Security Patch Scanning script

Forums

My Profile

Inbox

Address Book

My Subscription

My Forums

Security Patch Scanning script

Logged in as: Guest

arrSession:exec spGetSession 2,16,49883

	Active Users: There are 0 members and 0 guests.
	Users viewing this topic: none

Printable Version

All Forums >> [Scripting] >> Post a VBScript >> Security Patch Scanning script
Do you like VisualBasicScript.com? Link to us and help spread the word about our forum. Thanks!

Page: [1]

Message

<< Older Topic Newer Topic >>

Security Patch Scanning script - 7/24/2007 1:31:53 AM

CondoPC

Posts: 118
Score: 0
Joined: 7/23/2007
Status: offline

This script will scan a list of computers remotely for security patches installed. This can return a list of all patches installed or just scan for a defined list of patches and report whether they are installed or not.

Because the list of servers and patches can be large, the script assumes that this information would be kept in a database. Access works great for this. At a minmum, it expects a database with two tables, one for servers and on for patches.

The script is easily modified to reflect your database name, your table names, and your table columns. It also allows you to specify a filter so that you can refine your searches to smaller groups of servers or patches. The output is in csv format and is easy to manipulate into a pivot table for reporting.

'Security Patch Script - Scanning and Reporting
'*************************************************************************
'Script for scanning computers for security patch installation history
'This will scan a list of servers or computers from a batabase and report 
'on the security patches installed. The report can be on all patches 
'installed or against a list of know patches as defined in a database list. 
'The output is in CSV format.
'
'Author: Jason Condo
'Date: 7/20/2007
'www.condopc.com

On Error Resume Next
'Option Explicit
'--------------------------------------------------------------------
'       Script Declarations
'--------------------------------------------------------------------
'=======================================================================================================================
'=====          USER DEFINED VALUES    - you may make changes to these values                                    =======
'=======================================================================================================================
DIM ReportFile, strServerTable, strServerNameColumn, strServerFilterColumn, strServerFilterValue
DIM strPatchTable, strPatchNameColumn, strPatchFilterColumn, strPatchFilterValue, strExtendedInfo
'Path to database storing our server standards - Used to get list of information used by the script located at root of MIT share
Const strDB = "S:\Std.mdb"
ReportFile = "SecurityPatchReport"          'Name of the output report file - this will be saved as <your value>_date.csv in the same folder as the script s running from.
ScriptOutput = "File"                      'If value is "File", the script will output to a defined CSV file, otherwise output is to console
'values for the server table - replace with matching table and column names from database above
strServerTable = "tblServers"               'name of the table in the database that hold server names and information
strServerNameColumn = "ServerName"          'name of the column that hold the names of the servers to scan
strServerFilterColumn = "Environment"       'name of the column you want to filter servers to - in the MITStd.mdb, the Environment values
                                           'are as follows: "1" production, "2" qa , "3" test, "4" development, "5" dr, "6" training
strServerFilterValue = "4"                  'value you will filter results by 
'values for the patch table - replace with matching table and column names from database above
strPatchTable = "tblPatches"                'name of the table in the database that hold server names and information
strPatchNameColumn = "PatchNumber"          'name of the column that hold the names of the servers to scan
strPatchFilterColumn = "DeployDate"      'name of the column you want to filter servers to
strPatchFilterValue = "072007"              'value you will filter results by
strExtendedInfo = "MiniDescription"         'name of the column that hold extended information about the patch

'=======================================================================================================================
'=====          SCRIPT DEFINED VALUES    - Do Not make changes to these values                                   =======
'=======================================================================================================================
Dim strMessage                              'used to hold value for message to output
DIM objShell, objFSO, objLoggingFile
DIM strScriptFile, strScriptPath
DIM myDay, myTime, myDayName, myMonthName , myYear, mySecond, myHour, myColon, myMinute 'variables used in parsing date and time
DIM ProcessChoice, sProcess, strFilterData, strHF, strHotfixFound, strComputer
DIM objWMIService, colQuickFixes, strSQL, objConn, objRS, iLoop
                
Set objShell = CreateObject("WScript.Shell")
Set objFSO = CreateObject("Scripting.FileSystemObject")
strScriptFile = WScript.ScriptFullname
strScriptPath = Left(strScriptFile, Len(strScriptFile) - Len(WScript.Scriptname))   'used to define the path from where the script file is located
strLoggingFile = strScriptPath & ReportFile & "_" & rDateTime() & ".csv"            'this defines the path and filename for the report of patch info

'Build a list of servers from the database
public arrServerList()                      'array for storing server names
Call GetListofServers(strServerTable,strServerNameColumn) 'get list of servers from the tlbServers table from the "ServerName" column
'Build a list of hotfixes from the database
public arrHF()
Call GetListofPatches(strPatchTable,strPatchNameColumn,strPatchFilterColumn,strPatchFilterValue) 'get a list of hotfixes based on filter data

'ask user if they want to scan against the list of hotfixes or for all hotfixes
ProcessChoice = MsgBox("Would you like to scan for specific hotfixes?" & Chr(13) & "Pressing 'No' will cause the script to run a report of all hotfixes installed", 3)
Select Case ProcessChoice
Case 6                                                  'yes
 sProcess = "SCAN"                                   'scans for specific hotfixes
Case 7                                                  'no
 sProcess = "REPORT"                                 'runs comma delimited report of servers and their installed patches
Case Else                                               'cancel or error
    DisplayOutput "User cancelled. No changes made"     'no changes made, script exits
 wscript.quit 
End Select
Set objLoggingFile = objFSO.OpenTextFile(strLoggingFile, 8, True)
'=======================================================================================================================
'=====          MAIN SCRIPT                                                                                      =======
'=======================================================================================================================
Select Case sProcess
'--------------------------------
'    Reporting for all patches
'================================
   Case "REPORT"
       DisplayOutput "Computer,Description:,Hot Fix ID,Installation Date,Installed By"
       For each strComputer in arrServerList
           If strComputer <> "" Then
               strFilterData = GetFilterInfo(strServerTable, strServerNameColumn, strComputer, strServerFilterColumn)
               If CStr(strFilterData) = strServerFilterValue Then
                If PingServer(strComputer) Then
                 Call ListHotfixes()                     'list all hotfixes installed
                Else
                 DisplayOutput strComputer & ",Server could not be contacted"
                End If
               Else
                   DisplayOutput strComputer & ",Skipped"
               End If
               DisplayOutput ""
           End If
       Next
'--------------------------------
'    Scan for specific patches
'================================
   Case "SCAN"
       DisplayOutput "Computer,Patch,Result,Count,Patch Details"
       For each strComputer in arrServerList
           If strComputer <> "" Then
               strFilterData = GetFilterInfo(strServerTable, strServerNameColumn, strComputer, strServerFilterColumn)
               If CStr(strFilterData) = strServerFilterValue Then
                   If PingServer(strComputer) Then
                 For each strHF in arrHF
                     If strHF <> "" Then
                               strMessage = strComputer & "," & strHF
                      strHotfixFound = ScanForHotfix(strHF)
                      If strHotfixFound Then
                       strMessage = strMessage & ",installed,1"
                      Else
                       strMessage = strMessage & ",not installed,"
                   End If
                               'find extended information about patch
                   strMessage = strMessage & "," & GetExtendedInfo(strPatchTable, strPatchNameColumn, strHF, strExtendedInfo)
                      DisplayOutput strMessage
                  End If
                 Next
                Else
                 DisplayOutput strComputer & ",Server could not be contacted"
                End If
               Else
                   DisplayOutput strComputer & ",Skipped"
               End If
               DisplayOutput ""
           End If
       Next
End Select

'=======================================================================================================================
'    Security Scan Functions
'=======================================================================================================================
'-------------------------------------------------
Function ListHotfixes()
'scan for hotfixes
Set objWMIService = GetObject("winmgmts:" _
 & "{impersonationLevel=impersonate}!\\" & strComputer & "\root\cimv2")
Set colQuickFixes = objWMIService.ExecQuery _
 ("Select * from Win32_QuickFixEngineering")

For Each objQuickFix in colQuickFixes
       If Instr(objQuickFix.HotFixID,"File 1") = 0 Then                  'skips blank hotfixes that get listed
     DisplayOutput objQuickFix.CSName  & "," &  objQuickFix.Description  & "," &  objQuickFix.HotFixID & "," & objQuickFix.InstallDate & "," & objQuickFix.InstalledBy
 End If
Next
End Function

'-------------------------------------------------
Function ScanForHotfix(strHF)
'scan for hotfix based on kb number passed from call, return true if found
Set objWMIService = GetObject("winmgmts:" _
 & "{impersonationLevel=impersonate}!\\" & strComputer & "\root\cimv2")
Set colQuickFixes = objWMIService.ExecQuery _
    ("Select * from Win32_QuickFixEngineering WHERE HotFixID = 'Q" & strHF & "' OR HotFixID = 'KB" & strHF & "'")

ScanForHotfix = False
For Each objQuickFix in colQuickFixes
 If instr(objQuickFix.HotFixID, strHF) <> 0 Then
  ScanForHotfix = TRUE
 End If
Next
End Function
'--------------------------------------------------
Function GetListofPatches(strTable,strPatch,strFilterColumn,strFilterValue)
strSQL = "SELECT * FROM " & strTable & " WHERE " & strFilterColumn & " = '" & strFilterValue & "'"

Set objConn = CreateObject("ADODB.Connection")
   objConn.Provider="Microsoft.Jet.OLEDB.4.0"
   objConn.Open strDB
Set objRS = CreateObject("ADODB.Recordset")
objRS.CursorLocation = 3 ' adUseClient
objRS.Open strSQL, objConn, 1, 3

If err.number <> 0 Then 
 DisplayOutput "GetListofPatches() - error occurred reading from database: " & err.number
Else 
 For iLoop = 0 to objRS.RecordCount
  
  redim preserve arrHF(iLoop)
  
  if objRS.EOF then 
   exit for
  else
   IF objRS(strPatch) <> "" or objRS(strPatch) <> NULL Then
    arrHF(iLoop) = objRS(strPatch)
   end if
   objRS.MoveNext
  end if
 next
 
End If

objRS.Close
objConn.Close
strSQL = Null
set objRS = Nothing
set objConn = Nothing
End Function

'--------------------------------------------------
Function GetExtendedInfo(strTable, strColumn, strValue, strColumn2)
strSQL = "SELECT * FROM " & strTable & " WHERE " & strColumn & " = '" & strValue & "'"
 
Set objConn = CreateObject("ADODB.Connection")
   objConn.Provider="Microsoft.Jet.OLEDB.4.0"
   objConn.Open strDB
   Set objRS = CreateObject("ADODB.Recordset")
objRS.CursorLocation = 3 ' adUseClient
objRS.Open strSQL, objConn, 1, 3

If err.number <> 0 Then 
 GetExtendedInfo = "Unknown"
ElseIf objRS.RecordCount = 0 Then
 GetExtendedInfo = "Unknown"
Else
    GetExtendedInfo = objRS(strColumn2)
End If

objRS.Close
objConn.Close
set objRS = Nothing
set objConn = Nothing
strSQL = Null
End Function

'=======================================================================================================================
'    Script Utility Functions
'=======================================================================================================================
'-----------------------------------------
Sub DisplayOutput(strText)
If UCase(ScriptOutput) = "FILE" Then
 objLoggingFile.WriteLine strText
Else
 wscript.echo strText
End If
End Sub
'------------------------------------------
Function GetListofServers(strTable, strColumn)

strSQL = "SELECT * FROM " & strTable

If err.number <> 0 Then 
 DisplayOutput "GetListofServers() - error occurred reading from database: " & err.number
Else 
 For iLoop = 0 to objRS.RecordCount
  
  redim preserve arrServerList(iLoop)
  
  if objRS.EOF then 
   exit for
  else
   IF objRS(strColumn) <> "" or objRS(strColumn) <> NULL Then
    arrServerList(iLoop) = objRS(strColumn)
   end if
   objRS.MoveNext
  end if
 next
 
End If

objRS.Close
objConn.Close
strSQL = Null
set objRS = Nothing
set objConn = Nothing

End Function

'------------------------------------------
Function GetFilterInfo(strTable, strColumn, strValue, strFilterColumn)

strSQL = "SELECT * FROM " & strTable & " WHERE " & strColumn & " = '" & strValue & "'"
 
Set objConn = CreateObject("ADODB.Connection")
   objConn.Provider="Microsoft.Jet.OLEDB.4.0"
   objConn.Open strDB
   Set objRS = CreateObject("ADODB.Recordset")
objRS.CursorLocation = 3 ' adUseClient
objRS.Open strSQL, objConn, 1, 3

If err.number <> 0 Then 
 DisplayOutput "GetFilterInfo() - error occurred reading from database: " & err.number
 GetFilterInfo = "Unknown"
ElseIf objRS.RecordCount = 0 Then
 DisplayOutput "error finding " & strValue & " in the database"
 GetFilterInfo = "Unknown"
Else
    GetFilterInfo = objRS(strFilterColumn)
End If

objRS.Close
objConn.Close
set objRS = Nothing
set objConn = Nothing
strSQL = Null
End Function

'-----------------------------------------
Function PingServer(strHost)
   dim objPing, objRetStatus
   set objPing = GetObject("winmgmts:{impersonationLevel=impersonate}").ExecQuery _
     ("select * from Win32_PingStatus where address = '" & strHost & "'")
   for each objRetStatus in objPing
       if IsNull(objRetStatus.StatusCode) or objRetStatus.StatusCode <> 0 then
  PingServer = False
       else
           PingServer = True
       end if
   next
   set objPing = Nothing
End Function

'------------------------------------------------
Function ErrorFound
If Err.number <> 0 Then
 DisplayError "error encountered"
 ErrorFound = True
 err.Clear
else
 ErrorFound = False
End If

End Function

'------------------------------------------------
Function rDateTime()
'This will format the date and time to be used in a filename
'get 24-hour Hour and Minute and Second
myHour = Left(FormatDateTime(Time, vbShortTime),2)
myMinute = Right(FormatDateTime(Time, vbShortTime),2)
mySecond = DatePart("s" , FormatDateTime(Time, vbLongTime))
'concatenate the time 
myTime = myHour & myMinute & mySecond
'get Month and Day and Year 
   myDayName = DatePart("D",Date)
   myMonthName = DatePart("M",Date)
   myYear = DatePart("YYYY",Date)
'concatenate the date 
myDay = myYear & myMonthName & myDayName

'return formatted values
rDateTime = myDay & myTime

End Function

'------------------------------------------------
'End of Script

< Message edited by CondoPC -- 7/24/2007 1:35:29 AM >

Revisions: 1 | Post #: 1

RE: Security Patch Scanning script - 7/26/2007 11:53:04 PM

4scriptmoni

Posts: 190
Score: 0
Joined: 5/3/2007
Status: offline

COOL script! I am testing here in Pre. If you dont mind I may change few things...
Just notice:
1. The .mdb does not get updated (is it used only for filtering ?)
2. Date field is not showing in .csv

I had a similar but very very small thing, just outputs all hotfixes to a .txt

'This script, gather from a list of servers.txt all hotfix installed and creates a txt file for each server
'Authour: Felipe Ferreira
'Date: 06/04/2007
'version: 3.0
Dim IfSO, oFSO, Ifile, ofile, Inputfile, outputfile
Set iFSO = CreateObject("Scripting.FilesyStemObject")
Set oFSO = CreateObject("Scripting.FilesyStemObject")

Set GObjArgs = WScript.Arguments
'Get argument UPN txt file passed by user
if GObjArgs(0)="" then
   Wscript.Echo "Syntax erro! Syntax: cscript enableupn.vbs <listupn.txt>"
   wscript.quit
end if
if GObjArgs(0)<>"" then
inputfile = GObjArgs(0)   'Exemple: list.txt
end if
'Inputfile = "servers.txt"
Const ForAppending = 8
Set ifile = iFSO.OpenTextFile(Inputfile)

'Start to read the inputfile and goes thru each line untill list is done
Do until ifile.AtEndOfLine
   strComputer = ifile.ReadLine

'Outputfile = StrCoumputer & "_hotfix.txt"
'GET NAME OF SERVER FROM FILE
Set ofile = oFso.CreateTextFile(strComputer & "_" & year(DATE) & "_" & Month(DATE) & "_" & Day(DATE) & "_hotfixes.txt",true)

wscript.echo "Getting HotFixes from: " & strComputer
   
   Set objWMIService = GetObject("winmgmts:" _
   & "{impersonationLevel=impersonate}!\\" & strComputer & "\root\cimv2")

Set colQuickFixes = objWMIService.ExecQuery _
   ("Select * from Win32_QuickFixEngineering where HotFixID <> 'File 1'") 
'Gets hotfixid Also Cleans the dirty data

For Each objQuickFix in colQuickFixes
   Wscript.Echo objQuickFix.HotFixID
   ofile.writeline objQuickFix.HotFixID
Next
Loop 'goes thru servers.txt

_____________________________

Enterprise Microsoft Scripts
Exchange, Login/Logout Monitor,TS, Monitoring, Security, AD, etc...
http://www.xoroz.com

(in reply to CondoPC)

Post #: 2

RE: Security Patch Scanning script - 7/27/2007 12:37:22 AM

CondoPC

Posts: 118
Score: 0
Joined: 7/23/2007
Status: offline

Yes, I use the MDB for filtering because my list of servers is too large to build arrays or read from text. I already have a database of information about my servers, so adding a patch table was easy. This allows me to use this script as a framework and build all sorts of reporting and deployment scripts using the information in my database.

I found the date issue also, although it didn't bug me enuf to debug why it is available from WMI but doesn't contain anything.

(in reply to 4scriptmoni)

Post #: 3

If you found our site useful please link to us <a href="http://www.visualbasicscript.com">VisualBasicScript.com</a>.

All Forums >> [Scripting] >> Post a VBScript >> Security Patch Scanning script

Page: [1]

Jump to:

New Messages	No New Messages
Hot Topic w/ New Messages	Hot Topic w/o New Messages
Locked w/ New Messages	Locked w/o New Messages

Post New Thread

Reply to Message

Post New Poll

Submit Vote

Delete My Own Post

Delete My Own Thread

Rate Posts