VB script to create server patch status report in excel
Here is a VB script to create server patch status report in excel and the report can also be used as server inventory. Please note that the script creates an instance of the Microsoft.Update.Session object, then uses the CreateUpdateSearcher method to create an instance of the Searcher object. We could actually create the Searcher object directly but by using the Session object we can get this script to run against remote computers!
On Error Resume Next
intRow = 2
'Create an Excel Work Sheet;
Set objExcel = CreateObject("Excel.Application")
objExcel.Visible = True
objExcel.Workbooks.Add
objExcel.Cells(1, 1).Value = "Organization"
objExcel.Cells(1, 2).Value = "Server Name"
objExcel.Cells(1, 3).Value = "IP Address"
objExcel.Cells(1, 4).Value = "Operating System"
objExcel.Cells(1, 5).Value = "Service Packs"
objExcel.Cells(1, 6).Value = "Manufacturer"
objExcel.Cells(1, 7).Value = "Model"
objExcel.Cells(1, 8).Value = "Serial Number"
objExcel.Cells(1, 9).Value = "Last Patch Installed"
objExcel.Cells(1, 10).Value = "Last Update Date"
objExcel.Cells(1, 11).Value = "Last Reboot Time"
objExcel.Cells(1, 12).Value = "Update Status"
objExcel.Cells(1, 13).Value = "Report Time Stamp"
objExcel.Range("A1:M1").Select
objExcel.Selection.Interior.ColorIndex = 19
objExcel.Selection.Font.ColorIndex = 11
objExcel.Selection.Font.Bold = True
objExcel.Cells.EntireColumn.AutoFit
Set Fso = CreateObject("Scripting.FileSystemObject")
Set InputFile = fso.OpenTextFile("MachineList.Txt")
Do While Not (InputFile.atEndOfStream)
strComputer = InputFile.ReadLine
'creating an instance of the Microsoft.Update.Session object;
Set objSession = CreateObject("Microsoft.Update.Session", strComputer)
'Add Error Handling here
If Err.Number = 0 Then
'Call the CreateUpdateSearcher method;
Set objSearcher = objSession.CreateUpdateSearcher
'Use the QueryHistory method to retrieve the desired updates;
'The first 1 tells the script to begin its search with record 1 in the update history;
'The second 1 tells the script to stop its search after record 1;
'Updates are stored in reverse chronological order, with the most recent update as record 1;
Set colHistory = objSearcher.QueryHistory(1, 1)
For Each objEntry in colHistory
objExcel.Cells(intRow, 2).Value = strComputer
objExcel.Cells(intRow, 9).Value = objEntry.Title
objExcel.Cells(intRow, 10).Value = objEntry.Date
GetWMI
'Get Update Status of the Servers;
' (default) -- talk to the server rather than using locally cached information;
objsearcher.Online = True
Set oSearchResult = objsearcher.Search("DeploymentAction='Installation'" _
& " and IsAssigned=1 and IsInstalled=0 or DeploymentAction=" _
& "'Uninstallation' and IsAssigned=1 and IsPresent=1 or" _
& " DeploymentAction='Installation' and IsAssigned=1 and" _
& " RebootRequired=1 or DeploymentAction='Uninstallation' and" _
& " IsAssigned=1 and RebootRequired=1")
If Err.Number <> 0 Then
objExcel.Cells(intRow, 12).Value = "Detection Failed"
End If
If oSearchResult.Updates.Count = 0 Then
objExcel.Cells(intRow, 12).Value = "Computer is Up-To-Date"
End If
For y = 0 to oSearchResult.Updates.Count - 1
Set oUpdate = oSearchResult.Updates(y)
If ((oUpdate.DeploymentAction = 1 And Not oUpdate.IsInstalled) Or _
(oUpdate.DeploymentAction = 2 And oUpdate.IsPresent)) Then
objExcel.Cells(intRow, 12).Value = "Some updates are still available to your computer."
End If
Next
For y = 0 to oSearchResult.Updates.Count - 1
Set oUpdate = oSearchResult.Updates(y)
If oUpdate.RebootRequired Then
objExcel.Cells(intRow, 12).Value ="Reboot Required"
End If
Next
Next
Else
GetWMI
objExcel.Cells(intRow, 2).Value = strComputer
objExcel.Cells(intRow, 12).Value = "Fail to Get the Update Status"
Err.Clear
End If
objExcel.Cells(intRow, 13).Value = Now()
Set objWMIService = Nothing
Set objSWbemDateTime = Nothing
Set objSession = Nothing
Set objSearcher = Nothing
Set colHistory = Nothing
Set colOperatingSystems = Nothing
Set oSearchResult = Nothing
Set oUpdate = Nothing
Set colComputerSystems = Nothing
Set colBios = Nothing
Set colAdapters = Nothing
intRow = intRow + 1
Err.Clear
Loop
Wscript.Echo "Done"
'*****************************************************************************************************************************
'Get Information from WMI
Sub GetWMI
Err.Clear
Set objWMIService = GetObject("winmgmts:" & "{impersonationLevel=impersonate}!\\" & strComputer & "\root\cimv2")
Set colOperatingSystems = objWMIService.ExecQuery ("Select * from Win32_OperatingSystem")
Set objSWbemDateTime = CreateObject("WbemScripting.SWbemDateTime")
For Each objOperatingSystem in colOperatingSystems
objExcel.Cells(intRow, 1) = objOperatingSystem.Organization
objExcel.Cells(intRow, 4) = objOperatingSystem.Caption
objExcel.Cells(intRow, 5) = objOperatingSystem.CSDVersion
objSWbemDateTime.Value = objOperatingSystem.LastBootUpTime
If objOperatingSystem.Version = "5.2.3790" Then
objExcel.Cells(intRow, 11).Value = objSWbemDateTime.GetVarDate(True)
Else
objExcel.Cells(intRow, 11).Value = objSWbemDateTime.GetVarDate(False)
End If
Next
Set colComputerSystems = objWMIService.ExecQuery ("Select * from Win32_computerSystem")
For Each objcomputer in colComputerSystems
objExcel.Cells(intRow, 7) = objComputer.Model
Next
Set colBios = objWMIService.ExecQuery ("Select * from win32_BIOS")
For Each objBios in colBios
objExcel.Cells(intRow, 6) = objBios.Manufacturer
objExcel.Cells(intRow, 8) = objBios.SerialNumber
Next
Set colAdapters = objWMIService.ExecQuery ("SELECT * FROM Win32_NetworkAdapterConfiguration WHERE IPEnabled = True and DHCPEnabled = False")
For Each objAdapter in colAdapters
If Not IsNull(objAdapter.IPAddress) Then
For z = 0 To UBound(objAdapter.IPAddress)
strIPAddress = objAdapter.IPAddress(z)
If Left(strIPAddress, 2) = 10 Then
objExcel.Cells(intRow, 3).Value = objAdapter.IPAddress(z)
End If
Next
End If
Next
End Sub