Thursday, December 14, 2017

Excel VBA WMI script to capture the resent update patches into list of servers

'' Excel VBA script which pulls out the details of resent updated patches.

Option Explicit
Sub Check_Software_UpdateStatus()
Dim strComputer, objWMIService, colItems, objItem, mSht, oSht, I, rCount

Set mSht = ThisWorkbook.Sheets("Main")
Set oSht = ThisWorkbook.Sheets("Output")
oSht.Rows("2:65536").ClearContents

rCount = 2

For I = 2 To mSht.Cells(mSht.Rows.Count, 1).End(xlUp).Row
    strComputer = mSht.Cells(I, 1)
    Set objWMIService = GetObject("winmgmts:\\" & strComputer & "\root\CIMV2")
    Set colItems = objWMIService.ExecQuery("SELECT * FROM Win32_QuickFixEngineering", , 48)
        For Each objItem In colItems
            oSht.Cells(rCount, 1) = strComputer
            oSht.Cells(rCount, 2) = objItem.Caption
            oSht.Cells(rCount, 3) = objItem.CSName
            oSht.Cells(rCount, 4) = objItem.Description
            oSht.Cells(rCount, 5) = objItem.FixComments
            oSht.Cells(rCount, 6) = objItem.HotFixID
            oSht.Cells(rCount, 7) = objItem.InstallDate
            oSht.Cells(rCount, 8) = objItem.InstalledBy
            oSht.Cells(rCount, 9) = objItem.InstalledOn
            oSht.Cells(rCount, 10) = objItem.Name
            oSht.Cells(rCount, 11) = objItem.ServicePackInEffect
            oSht.Cells(rCount, 12) = objItem.Status
            rCount = rCount + 1
        Next
Next I
Set colItems = Nothing
Set objWMIService = Nothing
End Sub

No comments:

Post a Comment

Excel VBA to send email from excel with HTML table in a email body

'' Excel vba script to send email from outlook & email body in HTML table format. ''Write down email body in HTML tags...