I compiled a script from learning how to do things from reading tech-tips vbs forums and I wanted to share my work as a way of thanks and giving back. I don't know if my work utilizes the best practices, but it gets the job done and perhaps it could be improved with expert suggestions.
To summarize what the script does: it reads computer names from an excel file, checks if the system is online, checks a couple services status, checks system admins, and checks system shares and reports everything to the excel spread sheet.
Dim objFSO, objExcel, strXLReadRow, FSO, objWorkbook, strComputer, strService, strServStatus
Dim objAdminGroup, strAdmins, objShares, strShares, objShell, boolCode, objWMIService, colServiceList
' SCRIPT NOTES:
' FUNCTIONS / SUB ROUTINES ARE PLACED AT EOF
' THIS SCRIPT USES AN EXCEL WORK FILE - IT IS EXPECTED THE EXCEL FILE IS
' NAMED THE SAME NAME AS THIS SCRIPT FILE NAME AND IS IN THE SAME DIRECTORY
' CHECK IF THE EXCEL FILE EXISTS, IF NOT DISPLAY USAGE SUMMARY AND ABORT SCRIPT
Set objFSO = CreateObject("Scripting.FileSystemObject")
If objFSO.FileExists(Left(WScript.ScriptName, Len(WScript.ScriptName)- 4) & ".xls") Then
' INTENTIONALLY DO NOTHING HERE - SCRIPT WILL CONTINUE PROCESSING IF THE FILE EXISTS
Else
Call UsageInstructions
End If
' SETUP SCRIPT TO USE THE EXCEL FILE
Set objExcel = CreateObject("Excel.Application")
objExcel.Visible = True
strXLReadRow = 3 ' STARTING ROW COUNT FOR READING THE PC NAMES
Set FSO = CreateObject("Scripting.FileSystemObject")
Set objWorkbook = objExcel.Workbooks.Open(Left(WScript.ScriptName, Len(WScript.ScriptName)- 4) & ".xls")
' THE EXCEL FILE COLUMN A SHOULD CONTAIN COMPUTER NAMES TO RUN THE SYSTEM CHECKS AGAINST
' CHECK IF THE EXCEL FILE COLUMN A CONTAINS DATA, IF NOT DISPLAY USAGE SUMMARY AND ABORT SCRIPT
If objExcel.Cells(1, 1).Value = "" And objExcel.Cells(2, 1).Value = "" Then
Call UsageInstructions
End If
' CHECK TO SEE IF THE COMPUTER LIST STARTS AT ROW 1, IF SO INSERT A NEW ROW FOR THE COLUMN TITLES
If objExcel.Cells(1, 1).Value <> "" Then
objExcel.Range("A1").EntireRow.Insert
End If
' PREPARE EXCEL SPREADSHEET
objExcel.Cells(1, 1).Value = "System Name"
objExcel.Cells(1, 2).Value = "Online Status"
objExcel.Cells(1, 3).Value = "CSA Service"
objExcel.Cells(1, 4).Value = "Symantec AV Service"
objExcel.Cells(1, 5).Value = "System Administrators"
objExcel.Cells(1, 6).Value = "System Shares"
objExcel.Range("A1:F1").Select
' objExcel.Selection.Font.size = 11
objExcel.Selection.Font.Bold = True
objExcel.Cells.EntireColumn.AutoFit
objExcel.Range("A1:G1").HorizontalAlignment = -4108 ' -4108 centers
objExcel.Range("A1").EntireRow.Insert
objExcel.Cells(1, 1).Value = "SCRIPT IS PROCESSING . . ."
objexcel.Cells(1, 1).interior.colorindex = 3
' COLUMN A IN THE EXCEL WORK FILE IS THE COMPUTER LIST TO PROCESS
' WE WILL LOOP READING EACH CELL 1 BY 1 TILL AN EMPTY CELL IS FOUND
Do Until objExcel.Cells(strXLReadRow,1).Value = ""
strComputer = objExcel.Cells(strXLReadRow, 1).Value
' CHECK PING REPLY TO DETERMINE IF THE SYSTEM IS ONLINE, IF NOT, NOTHING ELSE IS DONE FOR CURRENT SYSTEM NAME
If Ping(strComputer) = True Then
On Error Resume Next ' NEEDED TO PREVENT SCRIPT STOP ERRORS IF ACCESS IS DENIED OR ANY ERRORS ON SYSTEM QUERY
objExcel.Cells(strXLReadRow, 2).value = "ONLINE"
' START OF SYSTEM CHECK COMMANDS, PROCESSES ONLY IF SYSTEM WAS DETERMINED TO BE ONLINE
' CHECK SERVICE CSAGENT
strServStatus = ""
strService = "CSAgent"
Call CheckService(strComputer, strService)
If strServStatus = "" Then
strServStatus = "!~ ERROR ~!"
End If
objExcel.Cells(strXLReadRow, 3).Value = strServStatus
' CHECK SERVICE SYMANTEC ANTIVIRUS
strServStatus = ""
strService = "Symantec AntiVirus"
Call CheckService(strComputer, strService)
If strServStatus = "" Then
strServStatus = "!~ ERROR ~!"
End If
objExcel.Cells(strXLReadRow, 4).Value = strServStatus
' GET LIST OF SYSTEM ADMINS
strAdmins = ""
Call GetAdmins
If strAdmins = "" Then
strAdmins = "!~ ERROR ~!"
End If
objExcel.Cells(strXLReadRow, 5).Value = strAdmins
' GET SYSTEM SHARES
strShares = ""
Call GetShares
If strShares = "" Then
strShares = "!~ ERROR ~!"
End If
objExcel.Cells(strXLReadRow, 6).Value = strShares
' END OF SYSTEM CHECK COMMANDS, THE "ELSE" COMMANDS BELOW PROCESS FOR OFFLINE SYSTEMS
Else
objExcel.Cells(strXLReadRow, 2).value = "OFFLINE"
End If
' ADD 1 TO THE ROW COUNT AND AUTOFIT THE SPREADSHEET AS WE LOOP
strXLReadRow = strXLReadRow + 1
objExcel.Cells.EntireColumn.AutoFit
Loop
' LOOP ABOVE COMPLETES WHEN NO MORE PC NAMES ARE FOUND IN COLUMN A
objExcel.Cells.EntireColumn.AutoFit
objExcel.Cells(1, 1).Value = "SCRIPT IS FINISHED!"
objexcel.Cells(1, 1).interior.colorindex = 4
Set objWorkbook = Nothing
MsgBox "Script " & Left(WScript.ScriptName, Len(WScript.ScriptName)- 4) & " is Finished . . ."
WScript.Quit
' :::::::::::::::::::::::::::::::::::::::
' FUNCTIONS AND SUB ROUTINES LISTED BELOW
' :::::::::::::::::::::::::::::::::::::::
' SUB ROUTINE TO DISPLAY USAGE INSTRUCTIONS
Sub UsageInstructions
MsgBox "SCRIPT USAGE INSTRUCTIONS: This script requires an Excel work file that meets these requirements: (1) Excel file is saved in the same directory as this script. (2) Excel file is named the same name as this script file. (3) Excel file contains a list of computer names in column A. Note, this script reads from the Excel file column A and writes to columns B through F. This script will abort when you select ok."
WScript.Quit
End Sub
' SUB ROUTINE TO GET THE SYSTEM ADMINS
Sub GetAdmins
Set objAdminGroup = GetObject("WinNT://" & strComputer & "/Administrators, group")
For Each GroupMember In objAdminGroup.members
strAdmins = strAdmins & GroupMember.name & ", "
Next
If strAdmins = "" Then
strAdmins = "!~ ERROR ~!"
Else
strAdmins = Left(strAdmins,Len(strAdmins) -2)
End If
End Sub
' SUB ROUTINE TO GET SYSTEM SHARES
Sub GetShares
Set objShares = GetObject("winmgmts:{impersonationLevel=impersonate}\\" & strComputer & "\root\cimv2").ExecQuery("SELECT * FROM Win32_Share")
For Each share In objShares
strShares = strShares & share.name & ", "
Next
If strShares = "" Then
strShares = "!~ ERROR ~!"
Else
strShares = Left(strShares,Len(strShares) -2)
End If
End Sub
' FUNCTION TO GET PING STATUS - USED TO DETERMINE IF ONLINE OR OFFLINE
Function Ping(strComputer)
Set objShell = CreateObject("WScript.Shell")
boolCode = objShell.Run("Ping -n 1 -w 300 " & strComputer, 0, True)
If boolCode = 0 Then
Ping = True
Else
Ping = False
End If
End Function
' SUB ROUTINE TO GET SERVICE STATUS
Sub CheckService(strComputer,strService)
Set objWMIService = GetObject("winmgmts:\\" & strComputer & "\root\cimv2")
Set colServiceList = objWMIService.ExecQuery("Select * from Win32_Service where Name = '" & strService & "'")
If colServiceList.Count = 0 Then
strServStatus = "SERVICE DOESN'T EXIST"
Exit Sub
End If
For Each objService In colServiceList
If objService.State = "" Then
strServStatus = "!~ ERROR ~!"
Else
strServStatus = UCase(objService.State)
End If
Next
End Sub