Author: Robin

I wanted an SQL query to retrieve the number of updates “required” by our clients / computers managed by WSUS. Most of the advice online seemed to be applicable only to WSUS 2. With a few tweaks to an existing script I managed to get a working SQL query (key: ComputerID now seems to be TargetID).

This is executed from within SQL Server Management Studio on the WSUS server itself (I migrated the database to the full version of SQL Server).
SQL version: 2008 R2
WSUS version: 3.2.7600.226
Windows version: Server 2008 x64 SP2

SELECT left(tbComputerTarget.FullDomainName,30) as [Machine Name]
,count(tbComputerTarget.FullDomainName) as [# of Missing patches]
,tbComputerTarget.LastSyncTime as [Last Sync Time]
FROM tbUpdateStatusPerComputer INNER JOIN tbComputerTarget ON tbUpdateStatusPerComputer.TargetID = tbComputerTarget.TargetID
WHERE (NOT (tbUpdateStatusPerComputer.SummarizationState IN ('1', '4'))) GROUP BY tbComputerTarget.FullDomainName, tbComputerTarget.LastSyncTime
ORDER BY COUNT(*) DESC

SummarizationState:
1 = Not Installed
2 = Needed
3 = Downloaded
4 = Installed
5 = Failed

@echo “http://forums.iis.net/t/1151822.aspx”
@echo “http://blog.techstacks.com/2008/10/iis-disabling-sslv2-and-weak-ciphers.html”
REG ADD “HKLM\System\CurrentControlSet\Control\SecurityProviders\SChannel\Protocols\SSL 2.0\Server” /v Enabled /t REG_DWORD /d 0 /f
REG ADD “HKLM\System\CurrentControlSet\Control\SecurityProviders\SChannel\Protocols\SSL 2.0\Client” /v Enabled /t REG_DWORD /d 0 /f
REG ADD “HKLM\System\CurrentControlSet\Control\SecurityProviders\SChannel\Protocols\SSL 3.0\Server” /v Enabled /t REG_DWORD /d 1 /f
REG ADD “HKLM\System\CurrentControlSet\Control\SecurityProviders\SChannel\Protocols\SSL 3.0\Client” /v Enabled /t REG_DWORD /d 1 /f
REG ADD “HKLM\System\CurrentControlSet\Control\SecurityProviders\SChannel\Protocols\TLS 1.0\Server” /v Enabled /t REG_DWORD /d 1 /f
REG ADD “HKLM\System\CurrentControlSet\Control\SecurityProviders\SChannel\Protocols\TLS 1.0\Client” /v Enabled /t REG_DWORD /d 1 /f
REG ADD “HKLM\System\CurrentControlSet\Control\SecurityProviders\SChannel\Ciphers\RC2 128/128”
REG ADD “HKLM\System\CurrentControlSet\Control\SecurityProviders\SChannel\Ciphers\RC4 128/128”
REG ADD “HKLM\System\CurrentControlSet\Control\SecurityProviders\SChannel\Ciphers\Triple DES 168/168”

I made a simple batch file to help fix Windows Update errors on our Windows Servers at work (both 2003 and 2008 / R2). It is based on some advice for various Windows Update errors that were on the internal knowledge base, which was no doubt gathered from information online somewhere. It performs a series of steps, saving a great deal of time over having to perform them manually.

Word of warning: The script will attempt to copy pending.xml before deleting it. If it cannot copy it it will delete it. Personally I have never been able to copy the file; most likely because I have not rebooted the server after taking ownership and granting myself full permissions to the file. I should add however that in all my use of this script I have not suffered any problems when deleting the file. Windows Update will recreate it the next time you/the computer downloads required updates.

:: Windows Update Multiple Fix Script
:: v1.00
@echo off
cd "C:\Windows\WinSxS"
echo Taking ownership of pending.xml
takeown /f C:\Windows\WinSxS\pending.xml
echo;
echo;
echo Granting you permissions on pending.xml
cacls C:\Windows\WinSxS\pending.xml /G %Username%:F
echo;
echo;
echo Attempting to copy pending.xml to user's home directory
copy "C:\Windows\WinSxS\pending.xml" "%HOMEPATH%\pending.xml"
echo;
echo;
echo Deleting pending.xml
del "C:\Windows\WinSxS\pending.xml"
echo;
echo;
echo Exporting HKEY_LOCAL_MACHINE\COMPONENTS to C:\HKLM_backup.reg
REG EXPORT HKLM\Components C:\HKLM_backup.reg
echo;
echo;
echo Removing noted problematic registry keys
REG DELETE HKLM\Components /v AdvancedInstallersNeedResolving
REG DELETE HKLM\Components /v PendingXmlIdentifier
REG DELETE HKLM\Components /v NextQueueEntryIndex
echo;
echo;
echo Restarting Windows Update related services...
net stop "Background Intelligent Transfer Service"
net start "Background Intelligent Transfer Service"
net stop "Cryptographic Services"
net start "Cryptographic Services"
net stop "Windows Update"
net start "Windows Update"
echo;
echo;
echo Please try running Windows Update / patching again.
echo;
echo;
PAUSE


Copy the code and save it as a batch file, running as administrator of course :)