Excel Operation

Excel Operation in VB Script

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