Ying Li at myITforum.com

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

Powershell script to append a file and to create the file/folder if not exist

Thanks Jeffrey Snover for the comment and tip for my previous script. It is really short and sweet!

Here is a script to append a log file and to create the folder and file if they don't exist.

$a = "C:\Temp1"
$b = "\Rebootlog.txt"
$c = $a + $b
$d = get-date
If(Test-Path $a)
{
If (Test-Path $c)
{Add-content $c "SMS will Reboot Server 5 Minutes After $d"}
Else
{new-item $c -type file
Add-content $c "SMS will Reboot Server 5 Minutes After $d"
clear-host} 
}
Else
{New-Item $a -Type Directory
new-item $c -type file
Add-content $c "SMS will Reboot Server 5 Minutes After $d"
clear-host}

VB script to append wbem path and repair SMS client

In our environment, we noticed that SMS Host Agent Service stopped on some of the clients and their cache size is 0. Further investigation revealed that this is because wbem is missing from System Variable -Path. Since quite a number of clients have the same issue, I wrote a VB script to append wbem to the System Variable -Path and then repair the clients.

On Error Resume Next

'fetch clients list from a txt file to a dictionary

TxtFile = "C:\Myworkplace\Clientlist.txt"
Set objDictionary = CreateObject("Scripting.Dictionary")
Set objFSO = CreateObject("Scripting.FileSystemObject")
Set objTextFile = objFSO.OpenTextFile _
 (TxtFile, ForReading)
i = 0
Do Until objTextFile.AtEndofStream
    strNextLine = objTextFile.Readline
    objDictionary.Add i, strNextLine
    i = i + 1
Loop

For Each objItem in objDictionary
strComputer = objDictionary.Item(objItem)

'Check if SMS Host Agent Service is not running

Set objWMIService = GetObject("winmgmts:" & _
    "{impersonationLevel=Impersonate}!\\" & strComputer & "\root\cimv2")

Set colStoppedServices = objWMIService.ExecQuery _
    ("Select * From Win32_Service Where name = 'CcmExec'and state <> 'running'")
 
 For Each objService in colStoppedServices
 Wscript.Echo strComputer
     Wscript.Echo objService.DisplayName  & " = " & objService.State
 Wscript.Echo "Possible Wbem Missing from System Path - Checking on System Path..."
 NewVariable = "%SystemRoot%\System32\Wbem"
 Set Variables = objWMIService.ExecQuery("SELECT * FROM Win32_Environment WHERE Name='PATH' AND SystemVariable='True'")

  For each objVar in Variables
  Wscript.Echo "Description: " & objVar.Description _
         & VBNewLine & "Name: " & objVar.Name _
         & VBNewLine & "System Variable: " _
         & objVar.SystemVariable & VBNewLine _
         & "User Name: " & objVar.UserName & VBNewLine _
         & "Variable Value: " & objVar.VariableValue

  'Check if Wbem is in System Path

  Found = instr(objVar.VariableValue,NewVariable)
  If Found = 0 Then
  WSCript.Echo "Wbem is not in System Path and Updating System Path..."

  'Updating System Path

  ExistingPath = objVar.VariableValue
  ExistingPath = ExistingPath + NewVariable
  objVar.VariableValue = ExistingPath
  objVar.Put_()
  WSCript.Echo "System Path Updated"
  
  'Repair SMS Client

  Set SmsClient = GetObject("winmgmts://" & strComputer & "/Root/Ccm:SMS_Client")
  SmsClient.RepairClient
  WScript.Echo "Repair Is In Progress For " & UCase(strComputer)

  Else
   WScript.Echo "Good to go - Trying to Start SMS Agent"
   objService.StartService()
  End If

  Next
     
 Next

 WScript.Echo "=========================================================" 

Next
           

PowerShell script to get Computers Chassis Types

Here is a PowerShell script to get Computers Chassis Types:

$erroractionpreference = "SilentlyContinue"
$a = get-content "C:\temp\PClist.txt"
foreach ($i in $a)
{$b = get-wmiobject win32_SystemEnclosure -computername $i
foreach ($j in $b)
{write-host "$i Chassistype is:" $j.chassistypes}
}

Patch Management - SMS to schedule the server reboot after patches deployed by ITMU

We use ITMU to deploy the patches to all the servers at the same time. Normally we will suppress the reboot during the patching process. We will use SMS to schedule the reboot afterwards depending on the maintenance window we get from the server owners.

We create an advertisement to deploy the following VB script to the targeted collection:
First it will check c:\temp and if not exist - create c:\temp and then it will append or create Rebootlog in c\temp;
Per our Notes Admin's request, I add the function to check the Lotus Domino Server Service and stop the service before let SMS reboot the server.

Vbs Script:

Option Explicit
On Error Resume Next
Dim strDir
Dim strFile
Dim objFSO
Dim objFolder
Dim LogFile
Const ForWriting = 2
Const ForAppending = 8
Dim objFile
Dim objWMIService
Dim colRunningDataServices
Dim objDataService

strDir = "C:\Temp"
strFile = "\Rebootlog.txt"
Set objFSO = CreateObject("Scripting.FileSystemObject")
If objFSO.FolderExists (strDir) Then
checkLog
Else
Set objFolder = objFSO.CreateFolder(strDir)
checkLog
End if

Set objWMIService = GetObject("winmgmts:root\cimv2")
Set colrunningDataServices = objWMIService.ExecQuery _
    ("Select * From Win32_Service Where name = 'Lotus Domino Server' or name = 'Lotus Domino Server (Data)'and state = 'running'")
 

  For Each objDataService in colrunningDataServices
       objDataService.StopService()
      Next

WScript.Sleep 5*60*1000

Wscript.quit   
  
'****************************************************************************************
Sub checkLog

LogFile = strDir & strFile
Const ForWriting = 2
Const ForAppending = 8

If objFSO.FileExists(LogFile) Then
 Set objFile = objFSO.OpenTextFile(LogFile, ForAppending)
 objFile.Write vbCrLf & "SMS will Reboot Server 5 Minutes After " & Now
Else
 Set objFile = objFSO.CreateTextFile(LogFile)
 objFile.Close
 Set objFile = objFSO.OpenTextFile(LogFile, ForWriting) 
 objfile.write "SMS will Reboot Server 5 Minutes After " & now
End If
objFile.Close

End Sub

Powershell Script to find and replace a file on a computer

This script will find a file on C drive (or whatever drive we choose), rename it and replace it with another file. The new file should be in the same directory where you execute your powershell script.

#Because of CIM_DataFile, split File Name and Extension into two variables
$strFileName = "Foo"
$strFileExt = "doc"
$strNewFileName = "Foo2.doc"

$colFiles = get-wmiobject -query "Select * from CIM_Datafile where FileName = '$strFileName'and extension = '$strFileExt' and drive ='C:'"  
Foreach ($objfile in $colfiles)
{
Ren $objfile.name -newname ($objfile.filename +".bak")
copy-item $strNewFileName -destination ($objfile.drives + $objfile.path)
}

Powershell Script to check against a list of computers and see if the Automatic Update Service is running

This Powershell script will get a list of server or machine names from a text file named MachineList.txt and return the status of Automatic Updates service and start the service if it is not running.

$erroractionpreference = "SilentlyContinue"
$a = get-content "C:\Temp\Machinelist.txt"
Foreach ($i in $a)
{$b = get-wmiobject win32_service -computername $i -Filter "Name = 'wuauserv'"
If ($b.state -eq "stopped")
{$b.startservice()
write-host "Window update service is started for $i"}
else
{write-host "Window update service is already started for $i"}
}