PatchAholic...The WSUS Blog!

A question was asked in the WSUS Mailing List (hosted by Shavlik Technologies on www.patchmanagement.org) -

I am using WSUS 2.0 and I was wondering if there was a way to extract the computer hardware information it collects?

Oh yes, this is possible.

You can extract computer hardware data in table 'dbo.tbComputerTarget' in SUS database (SUSDB). You can query for the following information;

For instance, you can query it directly using SQL Query Analyzer or OSQL;

USE SUSDB

SELECT     FullDomainName, IPAddress, ComputerMake, ComputerModel, BiosName, BiosVersion, OSMajorVersion, OSServicePackMajorNumber

FROM         tbComputerTarget

Hope that helps! Happy patching.

Many a times folks in WSUS newsgroup want to know if -

Is there a way to disable the SSL warning in the To-Do list in WSUSAdmin Console?

To Do List

Use Secure Sockets Layer (SSL)

WSUS has detected that you are not using Secure Sockets Layer (SSL). Microsoft recommends using SSL to secure administration and client to server communications for better security. For more information, see Using Secure Sockets Layer (SSL).

 

I used to answer that as - "That is not documented anywhere!!. We will have to live with that". But, thanks to Josh (poster in NG) for this cheeky workaround.

 

WORKAROUND

Make a backup of "C:\program files\Update Services\administration\home\welcome.aspx" file.

Then open the file in notepad and find the last section at the bottom that starts like this:

<td id="tskNotUsingSSL" class="Tasks" style="display: none;">

Now you can't delete that line, but delete everything between the <div> and </div> right below that line - Which means you have to delete the following text between <div> and </div>;

Save the file and Voila! Happy Patching :-).

lf the logged in user is part of Local Administrators group, then he can use the custom install option to unselect the updates which will be eventually hidden. These updates will not be offered by the WUA at the next detection/scheduled installation time.

Scripting Guru Torgeir Bakken has posted an excellent .vbs script to unhide those hidden updates.

According to Torgeir Bakken (MVP)

If you are afraid that some users will hide some updates using the custom install option, here is a counter-measure you can use if the computers are in an Active Directory domain.

Use a script that unhides all hidden updates every time the computer starts up.

You could put the vbscript below in a computer startup script (with a GPO) that runs as part of the boot up process (before the user logs in).  It runs under the system context and has admin rights.

--------------------8<----------------------

On Error Resume Next
Dim oSearcher, oSearchResult, i, oUpdate

Set oSearcher = CreateObject("Microsoft.Update.Searcher")

' use locally cached information
oSearcher.Online = False

' find updates that are hidden
Set oSearchResult = oSearcher.Search("IsHidden=1")

If Err.Number = 0 Then
   If oSearchResult.Updates.Count > 0 Then
     For i = 0 to oSearchResult.Updates.Count - 1
       Set oUpdate = oSearchResult.Updates(i)
       ' unhide the update
       oUpdate.IsHidden = False
     Next
   End If
End If

'--------------------8<----------------------

Tip:

IF you configure the deadline whilst approving an update then it will restrict local Administrator from being able to unselect or hide updates.

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

PRE-REQUISITES:

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.

STEP 1:

Let’s start by adding the following code as a stored procedure (spSRMCountComputersNeedingUpdates.sql);

spSRMCountComputersNeedingUpdates.sql:-

CREATE PROCEDURE [dbo].[spSRMCountComputersNeedingUpdates]  AS

declare @computersNeedingUpdates int
declare @updatesNeededByComputers int
  SELECT @computersNeedingUpdates = COUNT(DISTINCT(C.TargetID)),
         @updatesNeededByComputers = COUNT(DISTINCT(U.LocalUpdateID))
      FROM tbUpdate AS U
    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
                                   D.ActionID IN (0,2) AND
                                   tg.Name <> 'All Computers'
                   )

select @computersNeedingUpdates as computersNeedingUpdates,@updatesNeededByComputers as updatesNeededByComputers

IF @computersNeedingUpdates > 0
  BEGIN

    SELECT U.LocalUpdateID,
      C.FullDomainName as FullDomainName
      FROM tbUpdate AS U
      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.tbComputerTarget AS C ON C.TargetID = S.TargetID
      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
                             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
                                   D.ActionID IN (0,2) AND
                                   tg.Name <> 'All Computers'
                    )

    SELECT U.LocalUpdateID,
      UT.Name as UpdateTypeName,
      KB.KBArticleID,
      case when SB.SecurityBulletinID IS NULL Then 'None' Else convert(varchar(15),SB.SecurityBulletinID) End as SecurityBulletinID,
      MI.MoreInfoURL as MoreInfoURL,
      PCLP.Title as UpdateTitle,
      PCLP.Description as UpdateDescription
      FROM tbUpdate AS U
      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.tbComputerTarget AS C ON C.TargetID = S.TargetID
      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
                             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
                                   D.ActionID IN (0,2) AND
                                   tg.Name <> 'All Computers'
                    )
    GROUP BY U.LocalUpdateID,UT.Name,KB.KBArticleID,SB.SecurityBulletinID,MI.MoreInfoURL,PCLP.Title,PCLP.Description

  END
--ENDIF
RETURN 1
GO

STEP 2:

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.

WSUSReport.vbs:-

'On Error Resume Next
Const adCmdStoredProc = 4
Const adUseClient = 3

'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"
smtp_port = "25"

db = "SUSDB"
appname = "SUSDB Mailer"
db_server = "YOUR-DB-SERVER"

Set Conn = CreateObject("ADODB.Connection")
if Err.Number <> 0 Then
  WScript.Echo "Failed creating ADODB.Connection object -> " & Err.Description
  WScript.Quit(0)
End If

Conn.ConnectionTimeout = 15
Conn.CursorLocation = adUseClient
Conn.Open = "DRIVER={SQL Server};SERVER=" & db_server & ";APP=" & appname & ";DATABASE=" & db & ";Trusted_Connection=yes;"

if Err.Number <> 0 Then
  WScript.Echo "Failed opening ADODB.Connection object with DB info-> " & Err.Description
  WScript.Quit(0)
End If

Set Cmd = CreateObject("ADODB.Command")

if Err.Number <> 0 Then
  WScript.Echo "Failed creating ADODB.Command object -> " & Err.Description
  WScript.Quit(0)
End If
Cmd.CommandText = "spSRMCountComputersNeedingUpdates"
Cmd.CommandType = adCmdStoredProc
Cmd.ActiveConnection = Conn

Cmd.Prepared = 1
Cmd.CommandTimeout = 15

Set RS = Cmd.Execute

if Err.Number <> 0 Then
  WScript.Echo "Failed opening ADODB.Recordset object for Command -> " & Err.Description
  WScript.Quit(0)
End If

rs_count = RS.RecordCount

Dim string

string = "<HTML><BODY>" & vbCrlf

if RS.Fields(0) > 0 Then
  WScript.Echo "Count = " & RS.Fields(0).Value
  Set RSUpdates = RS.NextRecordSet
  Set RSData = RS.NextRecordSet
Else
  WScript.Echo "No updates.  Quitting successfully"
  WScript.Quit(1)
End If

'Loop through all the computers that need updates

  Dim Updates
  Dim Computers
 
  Dim vContainer
  ' Create the dictionary instances.
  Set Updates = CreateObject ("Scripting.Dictionary")
  Updates.CompareMode = StringCompare

x = 0
while (RSUpdates.EOF <> True)
  if Not Updates.Exists(RSUpdates.Fields("LocalUpdateID").Value) Then
    Updates.Add RSUpdates.Fields("LocalUpdateID").Value, RSUpdates.Fields("FullDomainName").Value
  Else
    Updates.Item(RSUpdates.Fields("LocalUpdateID").Value) = Updates.Item(RSUpdates.Fields("LocalUpdateID").Value) & "," & RSUpdates.Fields("FullDomainName").Value
  End If
 
  RSUpdates.MoveNext
Wend

while (RSData.EOF <> True)
  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
  string = string & "<TABLE border = 1>" & vbCrlf & _
           "<TR><TD><b>Type:</B> " & strUpdateType & "</TD><TD><B>KB Article:</B> " & strKBID & "</TD><TD><B>Bulletin:</B> " & strBulletinID & "</TD></TR>" & vbCrlf & _
           "<TR><TD colspan = 3><B>Title:</B> " & strUpdateTitle & "</TD></TR>" & vbCrlf & _
           "<TR><TD colspan = 3><B>Description:</B> " & strUpdateDesc & "</TD></TR>" & vbCrlf & _
           "<TR><TD colspan = 3><B>More Information:</B> <A href=" & strInfoURL & ">" & strInfoURL & "</A></TD></TR>" & vbCrlf & _
           "<TR><TD colspan = 3><B>Server Name(s):</B> " & strSrv & "</TD></TR></TABLE>" & vbCrlf
  RSData.MoveNext
Wend
string = string & "</BODY></HTML>"

Set cdoMessage = CreateObject("CDO.Message")
cdoMessage.Subject = "WSUS: There are computers needing updates"
cdoMessage.From = smtp_mail_from
cdoMessage.To = smtp_mail_to
cdoMessage.HTMLBody = string

cdoMessage.Configuration.Fields.Item("http://schemas.microsoft.com/cdo/configuration/sendusing") = 2
cdoMessage.Configuration.Fields.Item("http://schemas.microsoft.com/cdo/configuration/smtpserver") = smtp_server
cdoMessage.Configuration.Fields.Item("http://schemas.microsoft.com/cdo/configuration/smtpserverport") = smtp_port
cdoMessage.Configuration.Fields.Update

cdoMessage.Send
If Err.Number = 0 Then
  WScript.Echo "Success"
  WScript.Quit(1)
Else
  WScript.Echo "Error sending CDO Message: " & Err.Description
  WScript.Quit(0)
End If

MORE INFORMATION

Kudos to Steven - http://www.manross.net/links.html

Brian McCann wants to know a better way to Check the Version of Windows Installer.

According to Windows Installer Team blog, "If you want to check the version of the Windows Installer on your system, check the version of MSI.DLL in the Windows\System32 folder. If the version is 3.1.4000.2435, you have the latest version.

One common point of confusion is that even if you have the latest version of Windows Installer 3.1 on your system and you type in msiexec.exe /? from a command-window, you will still be told that you are on version 3.1.4000.1823 or 3.1.4000.1830. This is because msiexec.exe /? will only give you the version of msiexec on the system -- not the other Windows Installer-related dll's. (The version of msiexec was not updated to 3.1.4000.2435 with the (v2) redistributable, just msi.dll was updated.)"

Luckily, I found a neat .vbs browser hosted script by Michael Harris \(MVP Scripting\). Save the following code as .htm

<html>
<head>
<script language="vbscript">
sub document_onclick()
set installer = createobject("windowsinstaller.installer")
msgbox installer.version
end sub
</script>
</head>
<body>
Click me for Windows Installer version...
</body>
</html>

and you are done:-). Note that the "windowsinstaller.installer" object is not marked safe for scripting in IE browser hosted script... Double click the saved htm file and then you need to click the information bar to allow the blocked ActiveX control in IE

If the machine is configured for automatic updates using WSUS then it will update the installer automatically as mandatory WSUS update :-).

The VBScript code below from Scripting Guru Torgeir Bakken (MVP) checks if the new AU client is needed on the computer, and runs WindowsUpdateAgent20-x86.exe if required in silent mode. Thanks Torgeir.

NOTES:

1. Adjust path in the sExePath variable.
2. If the users are local administrators, you can run the script as part of a logon script.
3. Alternatively, if the computers are in an Active Directory domain, you can do it in a computer startup script (with a GPO) that runs as part of the boot up process (before the user logs in). It runs under the system context and has admin rights.

The script uses the IWindowsUpdateAgentInfo::GetInfo method instead of checking the version of the file wuaueng.dll. Also, if the interface does not exist because the AU client is to old, the script will install the client. The script below uses both methods to check for an already up to date version.

'--------------------8<----------------------
Option Explicit

So, if you are looking for a report of some/specific or all WSUS Clients, then you might want to check out Rob Dunn's script 'WSUS_Client_Report.vbs'. 

This is really great. For instance, If I want to view a report of all WSUS Client computers in my Active Directory domain, then I can just enter any common letter such as "A" or "." or "com" or "Domain-Name" in the input box and hit OK. Voila! You will see a report of all WSUS Client computers.

This is very sweet. Thanks Rob!

NOTES:

1. You must be in the WSUS administrator's group to see the report, and if you are running this against a remote SQL box, you must be an admin to get to the database. You will need to change the connection string if you only have access to the SUSDB instance on the SQL server.
2. Edit strWSUSDBServer = "Your-WSUS-Server" / "Your-WSUS-Server:8530"
3. Edit strWSUSServer = "Your-WSUS-Server" / "Your-WSUS-Server:8530"
   

' -------------------START SCRIPT------------------

'Input name of computer and link directly to that computers' WSUS report.
'
'You must be in the WSUS admins group on the server you are querying against
'You must have access to the database to perform queries
'
'An html page will be generated with a list of computers that matched the
' search criteria you specified.
Const ForWriting = 2

Dim strComputerID, t, strComputerIDArray

'Your WSUS server.  If you are running remote SQL, enter your SQL server name
' here instead.
strWSUSDBServer = "sadadsms-mn"

'Your WSUS server - i.e. where you run the administration from.  If you are
' not running remote SQL, then this variable should be the same as the variable
' above.
strWSUSServer = "sadadsms-mn:8530"

'Set some font settings for our HTML page.
fface = "arial"
fcolor = "black"
fsize = "2"

Set myconn = CreateObject("adodb.connection")

strComputer = InputBox("Enter the search criteria for the computer name you wish to " _
 & "retreive statitistics for:","Enter computer name search text")

'If nothing was entered, then quit.
If strComputer = "" Then wscript.quit

'set up the connection string.  This is using Windows NT authentication
' noted by Integrated Security=SSPI.
connection = "Provider=SQLOLEDB;" & _
        "Data Source=" & strWSUSDBServer & ";" & _
        "Initial Catalog=SUSDB;" & _
        "Integrated Security=SSPI"

'Open the connection to the database.
myconn.open (connection)

'Set some objects.
Set WshShell = WScript.CreateObject("WScript.Shell")
Set WshSysEnv = WshShell.Environment("PROCESS")
Set ws = wscript.CreateObject("Scripting.FileSystemObject")
Set result = CreateObject("adodb.recordset")

'Define our temporary report index file.
tempfile = WshSysEnv("TEMP") & "\" & "WSUS_Computer_Report.htm"

'Open the file for writing.
Set t = ws.OpenTextFile (tempfile, ForWriting, True)

'Generate the HTML
Call setupHTM("begin")

'Create a SQL string that would bring back the results where the fulldomainname field has any instance
' of the strcomputer text in it.
SQL = "SELECT computerID,FullDomainName from tbComputerTarget WHERE FullDomainName LIKE '%" & strComputer & "%'"

'Run the query.
Call GetComputerInfo

'Close the HTML
Call SetupHTM("end")

'If no search results were found, produce a msgbox and exit.
If strComputerID = "" Then
 msgbox "No computers with %" & strComputer & "% in the computer name field does not appear in the WSUS database specified on " _
  & "the server " & strWSUSServer & "."
 wscript.quit
Else
 'otherwise, open up the html page.
 Set oShell = CreateObject("WScript.Shell")
 oShell.Run tempfile,1,false

End If

Sub GetComputerInfo
  set result = myconn.execute(SQL)
   if not result.EOF then
  While not result.EOF
   'Put the field results for ComputerID into the strComputerID variable
   strComputerID = result("ComputerID")
   'Put the field results for FullDomainName into the strComputer variable
   strComputer = result("FullDomainName")
   'Write the hyperlink into a table row in the html file.
   t.writeline("<tr><td><font face='" & fface & "' color='" _
    & fcolor & "' size='" & fsize & "'><a href='http://" & strWSUSServer _
    & "/WsusAdmin/Reporting/ComputerDetailsReport.aspx?computerId=" & strComputerID & "'>" _
    & strComputer & "</a></font></td></tr>")
   'Move to the next record.
   result.movenext()
   
  Wend
 Else 
 End If
End Sub

Function SetupHTM(strVar)
 'If this is the beginning of the file, set up the head, title, and table for the page.
 If LCase(strVar) = "begin" Then
  t.writeline ("<html><head><title> WSUS Search Results </title></head><body>")
  t.writeline ("<font face='" & fface & "' color='" & fcolor & "'><strong>" _
   & " Click on a computername to view the WSUS client report.</strong></font><table>")
 ElseIf LCase(strVar) = "end" Then
  'if this is the end of the file, close out the table and html code.  Then close the
  ' file entirely.
  t.writeline ("</table></html>")
  t.close
 End If
End Function

' --------------------END SCRIPT-------------------

MORE INFORMATION

Shortcut to particular WSUS client report
http://www.vbshf.com/vbshf/forum/forums/thread-view.asp?tid=206&posts=1&start=1

So, how do you force the update detection? In this blog entry, let's take a look at some of the techniques.

• Rob Dunn [WSUS MVP] has created this simple script to force remote computer to perform wuauclt.exe /detectnow using object methods. That's cool Rob - Thanks again.

' ---------------START CODE---------------
strComputer = inputbox("Enter a computer name to run WUA detectnow","Invoke detectnow")
if strComputer = "" then wscript.quit
on error goto 0
Set autoUpdateClient = CreateObject("Microsoft.Update.AutoUpdate",strComputer)
AutoUpdateClient.detectnow()
wscript.echo "All done."
' ----------------END CODE-----------------

• For all GUI fans, take a look at WSUS - DETECTNOW 2.0 from WSUS.de. With this GUI Tool, you can trigger detection for approved updates and can reset SusClientId too.

MORE INFORMATION

Force remote computer to perform wuauclt.exe /detectnow (WSUS) - using object methods
http://www.vbshf.com/vbshf/forum/forums/thread-view.asp?tid=242 

Execute a Windows Update Detect Cycle on a Remote Computer
http://www.microsoft.com/technet/scriptcenter/csc/scripts/software/update/cscsw026.mspx

WSUS - DETECTNOW 2.0
http://downloads.wsus.de/wsus_detect_now/WSUS_detect_now_2.0_eng.exe 
http://downloads.wsus.de/wsus_detect_now/WSUS_detect_now_2.0_ger.exe 

Quick AU Client Detection & Installation with Windows Server Updates Services, WSUS
http://msmvps.com/blogs/athif/archive/2005/06/29/56200.aspx 

WSUS: Script to Force the Update Detection from Automatic Update Client (WUA) for updates on WSUS Server
http://msmvps.com/blogs/athif/articles/66375.aspx 

Windows Update Agent force script, email results version 2.15 by -- Rob Dunn[WSUS MVP]
http://msmvps.com/blogs/athif/archive/2006/05/09/94089.aspx 

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
' http://www.wsus.de/
' Version 1.05.04.1
' Translated quick and dirty into English Marco Biagini
' mbiagini@ehsd.cccounty.us
'--------------------------------------------
On Error Resume Next

Set objWshNet = CreateObject("Wscript.Network")

const HKCU = &H80000001
const HKLM = &H80000002

strDefComputer = lcase(objWshNet.ComputerName)

Set oArgs = WScript.Arguments
If oArgs.Count = 0 Then
 strComputer = InputBox("Please enter the name or IP address of the Computer that you want to check WSUS settings", "Automatic Updates", strDefComputer)
Else
 strComputer = oArgs(0)
End If

If strComputer = "" Then
 WScript.Quit
End if

strComputer = lcase(strComputer)
if left(strComputer,2)="\\" then
 strComputer=right(strComputer,(len(strComputer)-2))
end if

Set oReg=GetObject("winmgmts:{impersonationLevel=impersonate}!\\" & strComputer & "\root\default:StdRegProv")

If Err.Number <> 0 Then
 msgbox "Unable to connect to:" & VBCRLF & VBCRLF & "     " & strComputer & VBCRLF, vbCritical, "Communication Error"
 WScript.Quit
End If

Resultmsg = "**** Results of WUA Settings ****" & VBCRLF & VBCRLF

strMsg = "No Auto Update:  "
strKeyPath = "Software\Policies\Microsoft\Windows\WindowsUpdate\AU"
strValueName = "NoAutoUpdate"
If RegValueExists(strKeyPath, strValueName) Then
 oReg.GetDWORDValue HKLM,strKeyPath,strValueName,dwValue
 Resultmsg = Resultmsg & strMsg & GetNoAutoUpdate(dwValue) & VBCRLF & VBCRLF
Else
 Resultmsg = Resultmsg & strMsg & "Automatic Updates are not configured" & VBCRLF & VBCRLF
End If

strMsg = "Use WU Server:  "
strKeyPath = "Software\Policies\Microsoft\Windows\WindowsUpdate\AU"
strValueName = "UseWUServer"
If RegValueExists(strKeyPath, strValueName) Then
 oReg.GetDWORDValue HKLM,strKeyPath,strValueName,dwValue
 Resultmsg = Resultmsg & strMsg & GetUseWUServer(dwValue) & VBCRLF

 If dwValue = "1" Then
  strMsg = "  - WSUS Server:  "
  strKeyPath = "Software\Policies\Microsoft\Windows\WindowsUpdate"
  strValueName = "WUServer"
  If RegValueExists(strKeyPath, strValueName) Then
   oReg.GetStringValue HKLM,strKeyPath,strValueName,strValue
   Resultmsg = Resultmsg & strMsg & strValue & VBCRLF
  Else
   Resultmsg = Resultmsg & strMsg & "Automatic Updates are not configured" & VBCRLF
  End If
 
  strMsg = "  - WU Status Server:  "
  strKeyPath = "Software\Policies\Microsoft\Windows\WindowsUpdate"
  strValueName = "WUStatusServer"
  If RegValueExists(strKeyPath, strValueName) Then
   oReg.GetStringValue HKLM,strKeyPath,strValueName,strValue
   Resultmsg = Resultmsg & strMsg & strValue & VBCRLF
  Else
   Resultmsg = Resultmsg & strMsg & "Automatic Updates are not configured" & VBCRLF
  End If
 Else
  Resultmsg = Resultmsg & VBCRLF
 End If
Else
 Resultmsg = Resultmsg & strMsg & "Automatic Updates are not configured" & VBCRLF
 Resultmsg = Resultmsg & "  - Client configured to receive Updates from windowsupdate.microsoft.com" & VBCRLF
End If

strMsg = "  - TargetGroup:  "
strKeyPath = "Software\Policies\Microsoft\Windows\WindowsUpdate"
strValueName = "TargetGroup"
 If RegValueExists(strKeyPath, strValueName) Then
  oReg.GetStringValue HKLM,strKeyPath,strValueName,strValue
  Resultmsg = Resultmsg & strMsg & strValue & VBCRLF & VBCRLF
 Else
  Resultmsg = Resultmsg & strMsg & "Value not configured" & VBCRLF & VBCRLF
End If

strMsg = "AU Options:  "
strKeyPath = "Software\Policies\Microsoft\Windows\WindowsUpdate\AU"
strValueName = "AUOptions"
If RegValueExists(strKeyPath, strValueName) Then
 oReg.GetDWORDValue HKLM,strKeyPath,strValueName,dwValue
 Resultmsg = Resultmsg & strMsg & GetAUOptions(dwValue) & VBCRLF

 If dwValue = "4" Then
  strMsg = "  - Scheduled Install Day:  "
  strKeyPath = "Software\Policies\Microsoft\Windows\WindowsUpdate\AU"
  strValueName = "ScheduledInstallDay"
  If RegValueExists(strKeyPath, strValueName) Then
   oReg.GetDWORDValue HKLM,strKeyPath,strValueName,dwValue
   Resultmsg = Resultmsg & strMsg & getday(dwValue) & VBCRLF
  Else
   Resultmsg = Resultmsg & strMsg & "Value not configured" & VBCRLF
  End If
 
  strMsg = "  - Planned Installation Time:  "
  strKeyPath = "Software\Policies\Microsoft\Windows\WindowsUpdate\AU"
  strValueName = "ScheduledInstallTime"
  If RegValueExists(strKeyPath, strValueName) Then
   oReg.GetDWORDValue HKLM,strKeyPath,strValueName,dwValue
   Resultmsg = Resultmsg & strMsg & dwValue &":00 - 24 hours 4:00 is 4 AM, 16:00 is 4 PM" & VBCRLF
  Else
   Resultmsg = Resultmsg & strMsg & "Value not configured" & VBCRLF
  End If
 Else
   Resultmsg = Resultmsg & VBCRLF
 End If

Else
 Resultmsg = Resultmsg & strMsg & "Value is not configured" & VBCRLF
 strMsg = "  - Benutzerdefinierte Einstellung:  "
 strKeyPath = "Software\Microsoft\Windows\CurrentVersion\WindowsUpdate\Auto Update"
 strValueName = "AUOptions"
 If RegValueExists(strKeyPath, strValueName) Then
  oReg.GetDWORDValue HKLM,strKeyPath,strValueName,dwValue
  Resultmsg = Resultmsg & strMsg & GetAUOptions(dwValue) & VBCRLF

  If dwValue = "4" Then
   strMsg = "    - ScheduledInstallDay:  "
   strKeyPath = "Software\Microsoft\Windows\CurrentVersion\WindowsUpdate\Auto Update"
   strValueName = "ScheduledInstallDay"
   If RegValueExists(strKeyPath, strValueName) Then
    oReg.GetDWORDValue HKLM,strKeyPath,strValueName,dwValue
    Resultmsg = Resultmsg & strMsg & getday(dwValue) & VBCRLF
   Else
    Resultmsg = Resultmsg & strMsg & "Automatic Updates are not configured" & VBCRLF
   End If
 
   strMsg = "    - ScheduledInstallTime:  "
   strKeyPath = "Software\Microsoft\Windows\CurrentVersion\WindowsUpdate\Auto Update"
   strValueName = "ScheduledInstallTime"
   If RegValueExists(strKeyPath, strValueName) Then
    oReg.GetDWORDValue HKLM,strKeyPath,strValueName,dwValue
    Resultmsg = Resultmsg & strMsg & dwValue &":00" & VBCRLF
   Else
    Resultmsg = Resultmsg & strMsg & "Automatic Updates are not configured" & VBCRLF
   End If
  Else
    Resultmsg = Resultmsg & VBCRLF
  End If

 Else
  Resultmsg = Resultmsg & strMsg & "Not configured" & VBCRLF
 End If
End If

strMsg = "  - NoAUShutdownOption:  "
strKeyPath = "Software\Policies\Microsoft\Windows\WindowsUpdate\AU"
strValueName = "NoAUShutdownOption"
If RegValueExists(strKeyPath, strValueName) Then
 oReg.GetDWORDValue HKLM,strKeyPath,strValueName,dwValue
 Resultmsg = Resultmsg & strMsg & GetNoAUShutdownOption(dwValue) & VBCRLF & VBCRLF
Else
 Resultmsg = Resultmsg & strMsg & "Value not configured" & VBCRLF & VBCRLF
End If

strMsg = "AutoInstallMinorUpdates:  "
strKeyPath = "Software\Policies\Microsoft\Windows\WindowsUpdate\AU"
strValueName = "AutoInstallMinorUpdates"
If RegValueExists(strKeyPath, strValueName) Then
 oReg.GetDWORDValue HKLM,strKeyPath,strValueName,dwValue
 Resultmsg = Resultmsg & strMsg & GetAutoInstallMinorUpdates(dwValue) & VBCRLF & VBCRLF
Else
 Resultmsg = Resultmsg & strMsg & "Value is not configured" & VBCRLF & VBCRLF
End If

strMsg = "DetectionFrequency:  "
strKeyPath = "Software\Policies\Microsoft\Windows\WindowsUpdate\AU"
strValueName = "DetectionFrequency"
 If RegValueExists(strKeyPath, strValueName) Then
  oReg.GetDWORDValue HKLM,strKeyPath,strValueName,dwValue
  Resultmsg = Resultmsg & strMsg &"Every " & dwValue &" Hours to search for updates"& VBCRLF
 Else
   Resultmsg = Resultmsg & strMsg & "Value is not configured"& VBCRLF
 End If

strMsg = "RebootRelaunchTimeout:  "
strKeyPath = "Software\Policies\Microsoft\Windows\WindowsUpdate\AU"
strValueName = "RebootRelaunchTimeout"
 If RegValueExists(strKeyPath, strValueName) Then
  oReg.GetDWORDValue HKLM,strKeyPath,strValueName,dwValue
  Resultmsg = Resultmsg & strMsg & dwValue &" Minutes to wait until system restart"& VBCRLF
 Else
   Resultmsg = Resultmsg & strMsg & "Value is not configured" & VBCRLF
 End If

strMsg = "RebootWarningTimeout:  "
strKeyPath = "Software\Policies\Microsoft\Windows\WindowsUpdate\AU"
strValueName = "RebootWarningTimeout"
 If RegValueExists(strKeyPath, strValueName) Then
  oReg.GetDWORDValue HKLM,strKeyPath,strValueName,dwValue
  Resultmsg = Resultmsg & strMsg & dwValue &" Minutes wait until system restart"& VBCRLF
 Else
   Resultmsg = Resultmsg & strMsg & "Value not configured" & VBCRLF
End If

strMsg = "NoAutoRebootWithLoggedOnUsers:  "
strKeyPath = "Software\Policies\Microsoft\Windows\WindowsUpdate\AU"
strValueName = "NoAutoRebootWithLoggedOnUsers"
If RegValueExists(strKeyPath, strValueName) Then
 oReg.GetDWORDValue HKLM,strKeyPath,strValueName,dwValue
 Resultmsg = Resultmsg & strMsg & GetNoAutoReboot(dwValue) & VBCRLF
Else
 Resultmsg = Resultmsg & strMsg & "Value not configured" & VBCRLF
 Resultmsg = Resultmsg & "  - Default: User will be presented with a 5 minutes countdown" & VBCRLF
End If

strMsg = "RescheduleWaitTime:  "
strKeyPath = "Software\Policies\Microsoft\Windows\WindowsUpdate\AU"
strValueName = "RescheduleWaitTime"
If RegValueExists(strKeyPath, strValueName) Then
 oReg.GetDWORDValue HKLM,strKeyPath,strValueName,dwValue
 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
Else
 Resultmsg = Resultmsg & strMsg & "Not Configured" & VBCRLF & VBCRLF
End If

Resultmsg = Resultmsg & "http://www.wsus.de" & VBCRLF & "Die Infoseite zu Windows Server Updates Services"

MsgBox Resultmsg,,strComputer

set oReg = nothing

Function GetNoAutoUpdate(Index)
 Select Case 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"
 End select
End Function

Function GetUseWUServer(Index)
 Select Case Index
  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"
 End select
End Function

Function GetDay(Index)
 Select Case Index
  Case "0" GetDay = "Every Day"
  Case "1" GetDay = "Every Sunday"
  Case "2" GetDay = "Every Monday"
  Case "3" GetDay = "Every Tuesday"
  Case "4" GetDay = "Every Wednesday"
  Case "5" GetDay = "Every Thursday"
  Case "6" GetDay = "Every Friday"
  Case "7" GetDay = "Every Saturday"
  Case Else GetDay = "Invalid Entry"
 End select
End Function

Function GetAUOptions(Index)
 Select Case Index
  Case "0" GetAUOptions = "0"
  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"
 End select
End Function

Function GetNoAUShutdownOption(Index)
 Select Case 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"
 End select
End Function

Function GetAutoInstallMinorUpdates(Index)
 Select Case 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"
 End select
End Function

Function GetNoAutoReboot(Index)
 Select Case 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"
 End select
End Function

Function RegValueExists(sRegKey, sRegValue)
  sRegKey = Trim(sRegKey)
  sRegValue = LCase(Trim(sRegValue))
  ' init value
  RegValueExists = False
  If oReg.EnumValues(HKLM, sRegKey, aValueNames, aValueTypes) = 0 Then
    If Not IsNull(aValueNames) Then
      For i = 0 To UBound(aValueNames)
        If LCase(aValueNames(i)) = sRegValue Then
          RegValueExists = True
        End If
      Next
    End If
  End If
End Function

Function RegKeyExists(sRegKey)
  sRegKey = Trim(sRegKey)
  If oReg.EnumValues(HKLM, sRegKey, aValueNames, aValueTypes) = 0 Then
    RegKeyExists = True
  Else
    RegKeyExists = False
  End If
End Function

'---------------------END-----------------------

MORE INFORMATION

Translated version of http--www.wsus.de-
http://translate.google.com/translate?hl=en&sl=de&u=http://www.wsus.de/&prev=/search%3Fq%3Dwsus.de%26hl%3Den%26lr%3D%26safe%3Dactive 

View Original Web Page in german language
http://www.wsus.de/ 

Check WSUS (version 1.05.04.1)
http://downloads.wsus.de/check_wsus/Check_WSUS_eng.zip 
http://downloads.wsus.de/check_wsus/Check_WSUS_ger.zip 

How can I Approve multiple updates from WSUSAdmin?

You can select more than one update at a time and can simply select the newest one, scroll down, hit shift and selet the oldest one. You should then have them all selected and can approve them all at once.

Is there any tool that I can use ?

WSUSster is an update approval tool for Microsoft Windows Update Services (WSUS). Take a look at the screenshot on http://www.wsus.nl/images/wsuster_large.png

According to the authors of this tool, "A WSUS Server has a very bad interface for approving updates. That's why we decided to write our own approval tool which we called 'Wsuster'. We used the tool to migrate from SUS to WSUS. At this moment we use the tool also for approving the new security updates that are released every month. And it works like a charm. Just with a few mouse clicks, new updates are approved and old ones are removed!"

If you have any comments or feedback then, you can contact the authors here.

MORE INFORMATION

WSUSter Approval Tool
http://www.wsus.nl/site/index.php?option=com_content&task=view&id=23&Itemid=38

This web page solution by Robert Collewijn will display (search/extract update) information about the update files and there location within the content directory structure. This enables to search for updates on WSUS Server and can be downloaded locally.

The updates are stored in a content folder on the WSUS server with unrecognizable 40 character file name (C6C8B597D4268C65AFFC94C34F46C9147B109329.exe). This web page will display filename and download link & location associated with that unrecognizable 40 character file name. All you have to do is use the search option at top right to with the KB number. Give it a try!

STEP-BY-STEP

Download HotfixInfo.zip (inclusive the sub folders) into the following folder [C:\Program Files\Update Services\HotfixInfo]. Robert recommends this directory because the other Windows Server Update Service pages are also in the [C:\Program Files\Update Services] folder.

Open the [index.asp] file and change the following two (bold and italic) lines recording your confirguration.

'*** Define default variables ***
Dim sWSUSContentDir, sWSUSContentSrv
sWSUSContentDir = "F:\WSUS\WSUSContent\"
sWSUSContentSrv = "http:\\WSUS\Content\"

More information on http://wsus.collewijn.info/main.php?page=hotfixinfo_enu.php

Note

If IIS is using anonymous access, then user IUSR_MachineName is used to access the SQL server database. This is a local machine account on the IIS machine. Make sure IUSR_MachineName is granted security access to SUSDB with public and db_owner database roles for SUSDB so that it can. Typically, you need to grant SELECT permission on object 'tbFile', database 'SUSDB', owner 'dbo.

This script by Alexey Biznya will Extract and sort by Categories latest or all updates from \WSUSContent folder. The script will find \WSUSContent folder and SQL Server name from the registry HKEY_LOCAL_MACHINE\SOFTWARE\Microsoft\Update Services\Server\Setup.

[WSUS stores updates in \WSUSContent folder using SHA1 hash for file names and this script will query WSUS SQL/MSDE database and then exports those updates and categories.]

Requirements

1. Windows 2000+
2. ActivePerl 5.8.7 build 815 or higher from www.ActiveState.com
3. osql.exe Utility (included in MSDE)

Tunable params

my $root = "C:\\WSUS\\FtpRoot"; Make sure \WSUSContent folder and the folder where it will extract the files should be on the same volume. For instance, I have WSUSContent folder in 'D:\WSUS\WSUSContent and the folder to extract updates in 'D:\WSUS\FtpRoot'

1. my $update_langs = " 0,1033,1049 "; use 1033 for English.
2. my $descr_lang = "ru"; use en for English.
3.  my $content_dir = ""; The script will search in registry so you don’t have to input the values here.
4. my $server_name = " ; The script will search in registry so you don’t have to input the values here.

Syntax: WSUSExtract.pl

# ======== start of script ========

#!/usr/bin/perl -w
#
# Name:           WSUSExtractor.pl
#
# Purpose:        Extract and sort by categories latest or all updates from WSUS .
#
# Syntax:         WSUSExtract.pl
#
# Version:        1.0
#
# Requirements:   Windows 2000+
#                 ActivePerl 5.8.7 buld 815 or higher from www.ActiveState.com
#                 osql.exe Utility (included in MSDE)
#                 WSUS on MSDE
#
# Author:         Alexey Biznya
#

use strict;
use locale;
use IO::File;
use File::Path;
use Win32::Registry;

$|=1;

  my $ppid;
  my $fpid=open_pid_file("WSUSExtractor.pid");
  print $fpid $$;
  close($fpid);
#  warn("Starting script (pid=$$)\n");

###########################################################################################
#  Tunable params
###########################################################################################

  my $root = "C:\\WSUS\\FtpRoot"; # your dir for files (volume must be the same at WSUS content folder for NTFS hardlinks)
  my $is_latest = 1; # Is all updates extract or latest only
  my $update_langs = " 0,1033,1049 "; # comma separated languageIDs of extracting updates (0-all,1033-en,1049-ru,..%lang) 
  my $descr_lang = "ru";      # short language of titles and descriptions of updates (en,ar ..)

  my $db_name = "SUSDB";
  my $content_dir = ""; # WSUS content folder
  my $server_name = ""; # WSUS SqlServerName
  my $tmpfile = "query.sql";

###########################################################################################

  my @rec = ();
  my $col;
  my $query;
  my $cmd;

###########################################################################################
#  Huge SQL query temporary save to file
###########################################################################################
my $latest_str = "";
if ($is_latest) { $latest_str  = " AND tbRevision.IsLatestRevision = 1 ";
          print "\nStart extracting latest updates\n"; }
else {          print "\nStart extracting all updates\n"; }

  open (SQL, ">$tmpfile");
  print SQL qq {
SELECT  C2.CategoryID, C3.CategoryID, RC4.CategoryID, tbRevisionLanguage.LanguageID,
tbFile.FileName, tbFile.FileDigest
FROM tbCategory AS C1
INNER JOIN tbCategory AS C2 ON C1.CategoryID = C2.ParentCategoryID
INNER JOIN tbCategory AS C3 ON C2.CategoryID = C3.ParentCategoryID
INNER JOIN tbCategory AS C4 ON (C4.ParentCategoryID IS NULL AND C4.CategoryID != 7 )
INNER JOIN tbRevisionInCategory AS RC3 ON ( C3.CategoryID = RC3.CategoryID )
INNER JOIN tbRevisionInCategory AS RC4 ON ( RC4.RevisionID = RC3.RevisionID AND RC4.CategoryID = C4.CategoryID )
INNER JOIN tbBundleDependency ON RC4.RevisionID = tbBundleDependency.BundledRevisionID
INNER JOIN tbPreComputedLocalizedProperty ON ( tbPreComputedLocalizedProperty.RevisionID = tbBundleDependency.RevisionID  )
INNER JOIN tbRevision ON ( tbRevision.RevisionID = RC4.RevisionID  $latest_str )
INNER JOIN tbFileForRevision ON ( tbFileForRevision.RevisionID = RC4.RevisionID  )
INNER JOIN tbRevisionLanguage ON ( tbRevisionLanguage.RevisionID = tbFileForRevision.RevisionID )
INNER JOIN tbFile ON ( tbFile.FileDigest = tbFileForRevision.FileDigest  )
WHERE ( tbRevisionLanguage.Expanded = 0 AND tbRevisionLanguage.LanguageID IN ( $update_langs )
AND tbPreComputedLocalizedProperty.ShortLanguage like '$descr_lang' )
ORDER BY tbFile.Modified };

close(SQL);

###########################################################################################
#  Find WSUS content folder and SQL server address
###########################################################################################

   my $reg;
   $::HKEY_LOCAL_MACHINE->Open("SOFTWARE\\Microsoft\\Update Services\\Server\\Setup", $reg)
       or die "Can't open registry: $^E";
   $reg->QueryValueEx("ContentDir", $col, $content_dir) or die "Cannot find WSUS content folder in registry: $^E";
   $content_dir = $content_dir."\\WsusContent";
#   print "Here's a dir: $content_dir\n";

   $reg->QueryValueEx("SqlServerName", $col, $server_name) or die "Cannot find WSUS content folder in registry: $^E";
   $server_name =~ s/%computername%/$ENV{computername}/;
#   print "Here's a server name: $server_name\n";

###########################################################################################
#  Put to hash Microsoft WSUS languages
###########################################################################################
  my %lang = ();
  $query= "SELECT LanguageID, ShortLanguage FROM tbLanguage";
  $cmd = "osql.exe -w 500 -h-1 -s# -n -E -d $db_name -S $server_name -Q \"$query\"  2>nul |";

  open (DAT, $cmd);
    while(<DAT>) {
        chomp;
#        print $_."\n";
            if ( @rec = split(/#/) )
            { foreach $col (@rec) { if( $col =~ /^\s*(.+?)\s*$/ ) {$col = $1;} } }
        else { last; }
        $lang{$rec[0]} = $rec[1];
#        print $rec[0]."\t".$rec[1]."\n";
    }
  close(DAT);

###########################################################################################
#  Put to hash Microsoft WSUS categories
###########################################################################################
  my %ctg = ();
  $query= "SELECT CategoryID, Title FROM tbPrecomputedCategoryLocalizedProperty  WHERE ( ShortLanguage like 'en') ";
  $cmd = "osql.exe -w 500 -h-1 -s# -n -E -d $db_name -S $server_name -Q \"$query\"  2>nul |";

  open (DAT, $cmd);
    while(<DAT>) {
        chomp;
            if ( @rec = split(/#/) )
            { foreach $col (@rec) {  if( $col =~ /^\s*(.+?)\s*$/ ) {$col = $1;} } }
        else { last; }
                $rec[1] =~ s/\s+|\//_/g;
        $rec[1] =~ s/\W//g;
        $ctg{$rec[0]} = $rec[1];
    }
  close(DAT);

###########################################################################################
#  Processing Microsoft WSUS database and save files
###########################################################################################

  my $srcfile;
  my $dstfile;
  my $path;
  my $counter = 0;

  $cmd = "osql.exe -w 5000 -h-1 -s# -n -E -d $db_name -S $server_name -i $tmpfile  2>nul |";
  print "\nProcessing..\n";
  open (DAT, $cmd);
    while(<DAT>) {
        chomp;
#        print $_."\n";
            if ( @rec = split(/#/) )
            { foreach $col (@rec) {  if( $col =~ /^\s*(.+?)\s*$/ ) {$col = $1;} } }
        else { last; }

        if ($rec[4] =~ m/.(\w+)$/ ) { $srcfile = $1; }
        if ($rec[5] =~ m/^0x(\w{38})(\w{2})$/){ $srcfile = "$content_dir\\$2\\$1$2.$srcfile"; }
        $path = $root."\\".$ctg{$rec[0]}."\\".$ctg{$rec[1]}."\\".$ctg{$rec[2]}."\\".$lang{$rec[3]};
        if (! -e $path) { mkpath($path, 0, 0777) or die "Can't create dir: $^E\n";}
        $dstfile = $path."\\".$rec[4];
        if (hardlink($srcfile, $dstfile, 1)) { $counter += 1; }
    }
  close(DAT);

  print "\nTotal matching updates in database: $counter\n";

  unlink($tmpfile) or die "Cannot unlink tmpfile: $^E";

  warn "\nSuccess\n";

###########################################################################

###########################################################################################
# POSIX NTFS hard link to file
###########################################################################################
sub hardlink {
  my $exist_file=shift;
  my $new_file=shift;
  my $is_rewrite = shift;

  if(-e $new_file && $is_rewrite)
    { unlink ($new_file) or return 0;  }

  link ( $exist_file, $new_file ) or return 0; 

  return 1;
}

###########################################################################################
#  pid processing
###########################################################################################

sub open_pid_file {
  my $file=shift;
 
  if(-e $file) {
    my $fh=IO::File->new($file) or die "Can't open PID file $file: $^E";
    $ppid=<$fh>;
    die "Invalid PID file" unless $ppid=~ /^(\d+)$/;
    die "Process already running with PID $ppid" if(kill 0 => $ppid);
    warn "Removing PID file for defunct process ($ppid).\n";
    die "Can't unlink PID file $file" unless -w $file && unlink $file;
  }
  $ppid=$$;
  return IO::File->new($file,O_WRONLY|O_CREAT|O_EXCL,0644)
          or die "Can't create $file: $^E\n";
}
###########################################################################################

END {
  unlink "WSUSExtractor.pid" or die "Can't unlink PID file: $^E";
#  warn("Stoping script (pid=$$)\n");
}

__END__

# ======== end of script ========