'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 = "C:\Documents and Settings\Administrator\Documenti\ScriptsVBS\PatchControl\PatchControl.mdb"
ReportFile = "SecPatchReport"          '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 = ""              '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
		   wscript.echo strComputer
               strFilterData = GetFilterInfo(strServerTable, strServerNameColumn, strComputer, strServerFilterColumn)
               If CStr(strFilterData) = strServerFilterValue Then
                If PingServer(strComputer) Then
                 Call ListHotfixes()                     'list all hotfixes installed
				  wscript.echo strComputer
                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

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 "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
		   wscript.echo = "ping false"
		   PingServer = False
       else
           wscript.echo = "ping ok"
		   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