Wsus Patch Management Procedure
We will now see the steps for Installing WSUS for Configuration Manager 2012 R2. WSUS is Microsoft s separate, stand-alone server-based.
Steven Manross has created Windows Server Update Services add-ons in the form of an SQL stored procedure and. vbs / Perl scripts to determine if computers currently show as needing updates.
The SQL stored procedure spSRMCountComputersNeedingUpdates.sql is used in conjunction with the WSUSReport.vbs or WSUSReport.pl scripts to automatically notify an admin via email that there are computers needing Windows Security-related updates.
In step 1, let s add the sql stored procedure on WSUS Database Server and in step 2 we will run the. vbs script scripts to automatically notify WSUS Administrator via email that there are computers needing updates.
SAMPLE OUTPUT AS SEEN IN EMAIL:
Subject: WSUS: There are computers needing updates
Title: 816093: Security Update Microsoft Virtual Machine Microsoft VM
Description: This update helps resolve a vulnerability in the Microsoft virtual machine. After you install this item, you may have to restart your computer. Once you have installed this item, it cannot be removed.
Server Name s : computer1.domain.com,computer2.domain.com,computer3.domain.com
The. vbs code below requires Outlook CDO components to be installed or some other application that installs the CDO.Message object from the computer running WSUSReport.vbs.
Let s start by adding the following code as a stored procedure spSRMCountComputersNeedingUpdates.sql ;
In SQL Enterprise Manager under instancename Databases SUSDB Stored Procedures.
Right click on the Stored Procedure – click on New Stored Procedure.
Paste the code below – click on Check Syntax and make sure it is successful.
spSRMCountComputersNeedingUpdates.sql:-
CREATE PROCEDURE dbo. spSRMCountComputersNeedingUpdates AS
declare computersNeedingUpdates int
declare updatesNeededByComputers int
SELECT computersNeedingUpdates COUNT DISTINCT C.TargetID,
updatesNeededByComputers COUNT DISTINCT U.LocalUpdateID
INNER JOIN dbo.tbUpdateStatusPerComputer AS S WITH INDEX nc3UpdateStatusPerComputer ON U.UpdateID S.UpdateID
INNER JOIN dbo.tbComputerTarget AS C ON C.TargetID S.TargetID
WHERE S.SummarizationState IN 2,3,6
AND EXISTS SELECT FROM dbo.tbDeployment AS D
INNER JOIN dbo.tbRevision AS Re ON Re.RevisionID D.RevisionID
INNER JOIN dbo.tbTargetGroup AS tg ON tg.TargetGroupID D.TargetGroupID
WHERE Re.LocalUpdateID U.LocalUpdateID AND
select computersNeedingUpdates as computersNeedingUpdates, updatesNeededByComputers as updatesNeededByComputers
C.FullDomainName as FullDomainName
INNER JOIN dbo.tbPreComputedLocalizedProperty AS PCLP ON PCLP.UpdateID U.UpdateID
INNER JOIN dbo.tbLanguage as L on L.ShortLanguage PCLP.ShortLanguage
INNER JOIN dbo.tbLanguageInSubscription as LIS on LIS.LanguageID L.LanguageID
INNER JOIN dbo.tbUpdateType AS UT ON UT.UpdateTypeID U.UpdateTypeID
INNER JOIN dbo.tbUpdateStatusPerComputer AS S ON U.UpdateID S.UpdateID
INNER JOIN dbo.tbTargetInTargetGroup AS TITG ON TITG.TargetID C.TargetID
INNER JOIN dbo.tbTargetGroup AS TG ON TG.TargetGroupID TITG.TargetGroupID
INNER JOIN dbo.tbRevision AS Re ON Re.LocalUpdateID U.LocalUpdateID
LEFT JOIN dbo.tbKBArticleForRevision AS KB ON KB.RevisionID RE.RevisionID
LEFT JOIN dbo.tbSecurityBulletinForRevision AS SB ON SB.RevisionID RE.RevisionID
INNER JOIN dbo.tbMoreInfoURLForRevision AS MI ON MI.RevisionID RE.RevisionID and MI.ShortLanguage L.ShortLanguage
WHERE S.SummarizationState IN 2,3,6 AND
EXISTS SELECT FROM dbo.tbDeployment AS D
case when SB.SecurityBulletinID IS NULL Then None Else convert varchar 15, SB.SecurityBulletinID End as SecurityBulletinID,
MI.MoreInfoURL as MoreInfoURL,
PCLP.Description as UpdateDescription
GROUP BY U.LocalUpdateID,UT.Name,KB.KBArticleID,SB.SecurityBulletinID,MI.MoreInfoURL,PCLP.Title,PCLP.Description
Now save the following. vbs code as WSUSReport.vbs for computers needing updates using the stored procedure above. The following code requires Outlook CDO components to be installed or some other application that installs the CDO.Message object from the computer running WSUSReport.vbs.
Requires the Outlook CDO components to be installed or some other application that installs the CDO.Message object.
smtp_mail_from Some Friendly Name someaddress somesite.org
smtp_mail_to Recipient Name recipient somesite.org
smtp_server somesmtpserver.somesite.org
Set Conn CreateObject ADODB.Connection
WScript.Echo Failed creating ADODB.Connection object - Err.Description
Conn.CursorLocation adUseClient
Conn.Open DRIVER SQL Server ;SERVER db_server ;APP appname ;DATABASE db ;Trusted_Connection yes;
WScript.Echo Failed opening ADODB.Connection object with DB info- Err.Description
Set Cmd CreateObject ADODB.Command
WScript.Echo Failed creating ADODB.Command object - Err.Description
Cmd.CommandText spSRMCountComputersNeedingUpdates
Cmd.CommandType adCmdStoredProc
WScript.Echo Failed opening ADODB.Recordset object for Command - Err.Description
WScript.Echo Count RS.Fields 0. Value
Set RSUpdates RS.NextRecordSet
WScript.Echo No updates. Quitting successfully
Loop through all the computers that need updates
Create the dictionary instances.
Set Updates CreateObject Scripting.Dictionary
Updates.CompareMode StringCompare
if Not Updates.Exists RSUpdates.Fields LocalUpdateID. Value Then
Updates.Add RSUpdates.Fields LocalUpdateID. Value, RSUpdates.Fields FullDomainName. Value
Updates.Item RSUpdates.Fields LocalUpdateID. Value Updates.Item RSUpdates.Fields LocalUpdateID. Value , RSUpdates.Fields FullDomainName. Value
strUpdateID RSData.Fields LocalUpdateID. Value
strSrv Updates.Item strUpdateID
strUpdateType RSData.Fields UpdateTypeName. Value
strKBID RSData.Fields KBArticleID. Value
strBulletinID RSData.Fields SecurityBulletinID. Value
strInfoURL RSData.Fields MoreInfoURL. Value
strUpdateTitle RSData.Fields UpdateTitle. Value
strUpdateDesc RSData.Fields UpdateDescription. Value
Type: strUpdateType KB Article: strKBID Bulletin: strBulletinID vbCrlf _
Title: strUpdateTitle vbCrlf _
Description: strUpdateDesc vbCrlf _
More Information: strInfoURL vbCrlf _
Set cdoMessage CreateObject CDO.Message
cdoMessage.Subject WSUS: There are computers needing updates
cdoMessage.From smtp_mail_from
cdoMessage.Configuration.Fields.Item 2
cdoMessage.Configuration.Fields.Item smtp_server
cdoMessage.Configuration.Fields.Item smtp_port
cdoMessage.Configuration.Fields.Update
WScript.Echo Error sending CDO Message: Err.Description
Bobbie Harder MSFT has posted a list of Top known issues whilst upgrading WSUS to WSUS SP1 on microsoft.public.windows.server.update_services. These issues will be updated in a KB and in the online WSUS SP1 readme.
1. If you are using a proxy server, in some cases the SP1 upgrade may clear the proxy configuration username and password. This may cause synchronization of updates from Microsoft Servers to generate an invalid parameter error. To address this issue, reset the proxy configuration username and password and re-synchronize your server.
2. Remote SQL deployments: WSUS SP1 is not updating WSUS servers which are setup using remote SQL deployments.
The WSUS with SP1 setup Package must be run on both the front end and back end servers.
Run the setup package on the front end with no switches and choose to upgrade
Run the setup package on the back end with no switches and choose to upgrade.
3. Changed Machine Name after RTM install prior to SP1 upgrade can cause the WSUS SP1 upgrade to fail.
Use the following script to remove and re-add the ASPNET and WSUS Administrators groups. Then run the upgrade again.
osql.exe -S computername WSUS -E -Q USE SUSDB DECLARE asplogin
varchar 200 SELECT asplogin name from sysusers WHERE name like ASPNET
EXEC sp_revokedbaccess asplogin
osql.exe -S computername WSUS -E -Q USE SUSDB DECLARE wsusadminslogin
varchar 200 SELECT wsusadminslogin name from sysusers WHERE name like
WSUS Administrators EXEC sp_revokedbaccess wsusadminslogin
varchar 200 SELECT asplogin HOST_NAME ASPNET EXEC sp_grantlogin
asplogin EXEC sp_grantdbaccess asplogin EXEC sp_addrolemember
varchar 200 SELECT wsusadminslogin HOST_NAME WSUS Administrators EXEC
sp_grantlogin wsusadminslogin EXEC sp_grantdbaccess wsusadminslogin EXEC
sp_addrolemember webService, wsusadminslogin
osql.exe -S computername WSUS -E -Q backup database SUSDB to
Note you may have to replace in the last line with the
path to your actual content store.
a. WSUS SP1 upgrade can fail in some cases when the WMSDE database has been migrated to a local SQL 2000 server.
A registry key value must be changed in order for WSUS SP1 setup package to recognize there is no wmsde database to update.
If users have migrated WMSDE to a SQL server local or remote they must change the value of the following registry key:
1.HKLM Software Microsoft Update Services Server Setup WmsdeInstalled, from 1 to 0 before attempting to upgrade to WSUS SP1.
According to Bernd Teichert blog reader, In some cases, you might have to change the InstallType too on local SQL 2000 Server installation;
2.HKLM Software Microsoft Update Services Server Setup InstallType from 0x80 to 0x20.
b. WSUS SP1 upgrade can fail in some cases when the WMSDE database has been migrated to a remote SQL 2000 server.
Two registry key values must be changed in order for WSUS sp1 setup package to recognize there is no wmsde database to update and the update must be initiated on the backend, followed by the front end server.
If users have migrated WMSDE to a SQL server local or remote they must change the values of the following registry keys:
After updating these registry key values, initiate upgrade on backend and then on front end servers.
5. How to recover from a failed upgrade to restore your WSUS server to a consistent state and then retry the upgrade.
If the upgrade to WSUS SP1 fails it can leave your WSUS installation in an inconsistent and/or unusable state. In order to retry upgrading to WSUS SP1 you need to get your WSUS installation to a consistent state. To do this you can use the backup database created at the beginning of the upgrade process to restore your WSUS server to a pre-upgrade state.
If the upgrade operation to WSUS SP1 is unsuccessful, you can use the original WSUS backup database that was created at the start of the upgrade process to restore WSUS to a consistent state. In the event of a failed upgrade follow these steps to retry upgrading to WSUS SP1:
To retry upgrading to WSUS SP1;
1. Determine the location of the backup database by reviewing the contents of the WSUSSetup_ timestamp. log file. This file is located in the following folder – programfiles Update Services LogFiles.
2. Restore the backup database on the WSUS computer.
osql.exe -S -E -Q USE master ALTER DATABASE
SUSDB SET SINGLE_USER WITH ROLLBACK IMMEDIATE RESTORE DATABASE SUSDB FROM
DISK N WITH REPLACE ALTER DATABASE SUSDB SET
Remember to replace DatabaseInstance and PathToDatabaseBackup with values from your installation.
For DatabaseInstance use the value from the following registry key:
HKLM Software Microsoft Update Services Server Setup SqlServerName
For PathToDatabaseBackup use the value you identified in step 1.
3. Uninstall WSUS, but keep the WSUS database, log files and update files when you are prompted to remove them i.e. Ensure that all options in Remove Microsoft Windows Server Update Services are unchecked.
4. Reinstall WSUS RTM the original version not WSUS with SP1. Use the existing database when you are prompted to do this. This will return your WSUS system to a consistent state.
Note that you cannot use the backed up database from step 1 above directly in clean install of WSUS SP1 since the database schema has changed between WSUS RTM and WSUS SP1.
For any issues related to WSUS SP1 upgrade, you can post your queries directly on the following thread on microsoft.public.windows.server.update_services.
WSUS.de is yet another WSUS Community Web-Site. This site is hosted in German language and I have used Google Translation a free service to translate in English. So here we go WSUS.de in English.
My first pick is Check WSUS version 1.05.04.1. This. vbs script will Check WSUS Client / Windows Update Agent WUA Settings for any computer in the network. When you run this script, it will prompt you to enter Computer Name or IP Address of the machine that you want to check.
A copy of the script is as follows;
Einstellungen für die automatischen Updates
Translated quick and dirty into English Marco Biagini
Set objWshNet CreateObject Wscript.Network
strDefComputer lcase objWshNet.ComputerName
strComputer InputBox Please enter the name or IP address of the Computer that you want to check WSUS settings, Automatic Updates, strDefComputer
strComputer lcase strComputer
strComputer right strComputer, len strComputer -2
Set oReg GetObject winmgmts: impersonationLevel impersonate. strComputer root default:StdRegProv
msgbox Unable to connect to: VBCRLF VBCRLF strComputer VBCRLF, vbCritical, Communication Error
Resultmsg Results of WUA Settings VBCRLF VBCRLF
strKeyPath Software Policies Microsoft Windows WindowsUpdate AU
If RegValueExists strKeyPath, strValueName Then
oReg.GetDWORDValue HKLM,strKeyPath,strValueName,dwValue
Resultmsg Resultmsg strMsg GetNoAutoUpdate dwValue VBCRLF VBCRLF
Resultmsg Resultmsg strMsg Automatic Updates are not configured VBCRLF VBCRLF
Resultmsg Resultmsg strMsg GetUseWUServer dwValue VBCRLF
strKeyPath Software Policies Microsoft Windows WindowsUpdate
oReg.GetStringValue HKLM,strKeyPath,strValueName,strValue
Resultmsg Resultmsg strMsg strValue VBCRLF
Resultmsg Resultmsg strMsg Automatic Updates are not configured VBCRLF
Resultmsg Resultmsg – Client configured to receive Updates from windowsupdate.microsoft.com VBCRLF
strKeyPath Software Policies Microsoft Windows WindowsUpdate
Resultmsg Resultmsg strMsg strValue VBCRLF VBCRLF
Resultmsg Resultmsg strMsg Value not configured VBCRLF VBCRLF
Resultmsg Resultmsg strMsg GetAUOptions dwValue VBCRLF
strMsg – Scheduled Install Day:
strKeyPath Software Policies Microsoft Windows WindowsUpdate AU
strValueName ScheduledInstallDay
Resultmsg Resultmsg strMsg getday dwValue VBCRLF
Resultmsg Resultmsg strMsg Value not configured VBCRLF
strMsg – Planned Installation Time:
strValueName ScheduledInstallTime
Resultmsg Resultmsg strMsg dwValue :00 – 24 hours is 4 AM, is 4 PM VBCRLF
Resultmsg Resultmsg strMsg Value is not configured VBCRLF
strMsg – Benutzerdefinierte Einstellung:
strKeyPath Software Microsoft Windows CurrentVersion WindowsUpdate Auto Update
strMsg – ScheduledInstallDay:
strMsg – ScheduledInstallTime:
Resultmsg Resultmsg strMsg dwValue :00 VBCRLF
Resultmsg Resultmsg strMsg Not configured VBCRLF
strMsg – NoAUShutdownOption:
strValueName NoAUShutdownOption
Resultmsg Resultmsg strMsg GetNoAUShutdownOption dwValue VBCRLF VBCRLF
strMsg AutoInstallMinorUpdates:
strValueName AutoInstallMinorUpdates
Resultmsg Resultmsg strMsg GetAutoInstallMinorUpdates dwValue VBCRLF VBCRLF
Resultmsg Resultmsg strMsg Value is not configured VBCRLF VBCRLF
strValueName DetectionFrequency
Resultmsg Resultmsg strMsg Every dwValue Hours to search for updates VBCRLF
strMsg RebootRelaunchTimeout:
strValueName RebootRelaunchTimeout
Resultmsg Resultmsg strMsg dwValue Minutes to wait until system restart VBCRLF
strMsg RebootWarningTimeout:
strValueName RebootWarningTimeout
Resultmsg Resultmsg strMsg dwValue Minutes wait until system restart VBCRLF
strMsg NoAutoRebootWithLoggedOnUsers:
strValueName NoAutoRebootWithLoggedOnUsers
Resultmsg Resultmsg strMsg GetNoAutoReboot dwValue VBCRLF
Resultmsg Resultmsg – Default: User will be presented with a 5 minutes countdown VBCRLF
strValueName RescheduleWaitTime
If dwValue 0 Then Resultmsg Resultmsg strMsg Value not configured: dwValue VBCRLF VBCRLF End If
If dwValue 1 Then Resultmsg Resultmsg strMsg dwValue Minute VBCRLF VBCRLF End If
If dwValue 1 and dwValue 61 Then Resultmsg Resultmsg strMsg dwValue Minutes VBCRLF VBCRLF End If
If dwValue 60 Then Resultmsg Resultmsg strMsg Invalid Value dwValue VBCRLF VBCRLF End If
Resultmsg Resultmsg strMsg Not Configured VBCRLF VBCRLF
Resultmsg Resultmsg VBCRLF Die Infoseite zu Windows Server Updates Services
Function GetNoAutoUpdate Index
Case 0 GetNoAutoUpdate 0 – Auto Update applied by GPO
Case 1 GetNoAutoUpdate 1 – No Auto Update is applied by GPO
Case Else GetNoAutoUpdate Invalid Entry
Case 0 GetUseWUServer 0 – Client is configured to receive updates from windowsupdate.microsoft.com
Case 1 GetUseWUServer 1 – Client is configured to receive updates from your WSUS Server
Case Else GetUseWUServer Invalid Entry
Case Else GetDay Invalid Entry
Case 1 GetAUOptions 1 – Deaktiviert in den Benutzereinstellungen
Case 2 GetAUOptions 2 – Notify before download and Install.
Case 3 GetAUOptions 3 – Autom. Download, notify before installation.
Case 4 GetAUOptions 4 – Autom. Download, install according to GPO settings.
Case 5 GetAUOptions 5 – Allow Local Administator installation and manual configuration.
case Else GetAUOptions Invalid Entry
Function GetNoAUShutdownOption Index
Case 0 GetNoAUShutdownOption 0 – Updates are being installed and system will be restarted user ill be notified
Case 1 GetNoAUShutdownOption 1 – Updates are being installed and system will be restarted user will NOT be notified
Case Else GetNoAUShutdownOption Invalid Entry
Function GetAutoInstallMinorUpdates Index
Case 0 GetAutoInstallMinorUpdates 0 – Automatic updates are not immediately installed
Case 1 GetAutoInstallMinorUpdates 1 – Automatic updates are immediately installed
Case Else GetAutoInstallMinorUpdates Invalid Entry
Function GetNoAutoReboot Index
Case 0 GetNoAutoReboot 0 – User Countdown of 5 Minutes
Case 1 GetNoAutoReboot 1 – User will be notified before a system restart
case Else GetNoAutoReboot Invalid Entry
Function RegValueExists sRegKey, sRegValue
sRegValue LCase Trim sRegValue
If oReg.EnumValues HKLM, sRegKey, aValueNames, aValueTypes 0 Then
If Not IsNull aValueNames Then
If LCase aValueNames i sRegValue Then
Translated version of http–www.wsus.de-
en sl de u prev /search 3Fq 3Dwsus.de 26hl 3Den 26lr 3D 26safe 3Dactive
View Original Web Page in german language
PatchAholic…The WSUS Blog! | Just another Microsoft MVPs site
Read the step-by-step instructions written by an IT pro in the how to: WSUS GPO Settings for the real world.
I ve always heard that WSUS was something you set and forget, but I guess you are mostly referring to the database and not the WSUS Console so much.
TechNet Blogs Microsoft WSUS and Software Update Team Blog How to move WSUS from one server to another.
On Tuesday, November 28, 2006, Rights Management Services Client with Service Pack 2 KB917275 was downloaded by WSUS Server as classified under Service.
