SQL Query for WSUS 3 Needed Updates

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

Disabling SSL v2 and enabling SSL v3 (and strong ciphers) from the commandline

@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”

New Year’s Resolutions for 2010

My New Year’s Resolutions are usually very similar each year – self improve with regard to fitness, both mental and physical, and work hard in my profession. This year I’m trying to set more explicit goals; rather than “read more”, I’ve set a target of reading a book a month. It may not sound like much, but when factoring in time spent on other things (not to mention reading technical literature) it’s a reasonable aim. Anyway, here they are:

Body:

  • After being ill, get cycling regularly again. Aiming for a consistant 100 miles/wk by May, then increase carefully.
  • Hit the cyclo-cross season in September feeling strong, not tired. Finish roughly 6-7 minutes down on the professionals.
  • Add running into the exercise routine.
  • Run my first 10K. Have a rough aim of beating 48 minutes.

Mind:

  • Listen to Radio 4 on the way to and from work (rather than music) – and hopefully absorb some of it.
  • Read a book a month.
  • Take the Microsoft 70-294 exam by the end of May.
  • Take the Microsoft 70-298 exam by the end of August, to get my MCSE.
  • Take the VMware VCP (Vsphere 4) exam by the end of October.
  • Learn to use my digital SLR properly in modes other than Automatic.

C-C-C-Combo:

  • Walk in the countryside more frequently.
  • Learn a new dish a month, and cook the well rated recipes more regularly!

Windows Update Multiple Fix Script

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 :)