Appending to Excel document showing service status
I found a vb script that helps me with checking my servers online/offline status since Config Manager cant/doesnt represent clearly. What I want to do with the script is modify it so that it also tells me the status of the WDS service since I do have a few
servers that fail to start WDS on reboot even though startup is automatic. I am not good with coding, I can understand the concept when it is in front of me but cannot execute it with much luck. I have modified it to create the Service Status but don't actually
know how to query it.
Set objExcel = CreateObject("Excel.Application")
objExcel.Visible = True
intRow = 2
Set Fso = CreateObject("Scripting.FileSystemObject")
Set objWorkbook = objExcel.Workbooks.Open("C:\SCCM\SCCM SERVER PING TEST\output.xls")
Set InputFile = objWorkbook
Do Until objExcel.Cells(intRow,1).Value = ""
strComputer = objExcel.Cells(intRow, 1).Value
objExcel.Cells(1, 1).Value = "Machine Name"
objExcel.Cells(1, 2).Value = "IP Address"
objExcel.Cells(1, 3).Value = "Status"
objExcel.Cells(1, 4).value = "Service Status"
On Error Resume Next
Set objWMIService = GetObject("winmgmts:\\" & strComputer & "\root\cimv2")
Set colItems = objWMIService.ExecQuery("Select * from Win32_NetworkAdapterConfiguration Where IPEnabled = True")
For Each objItem in colItems
If Err.Number <> 0 Then
objExcel.Cells(intRow, 2).Value = ""
objExcel.Cells(intRow, 3).Value = "Off Line"
objExcel.Cells(intRow, 3).Font.Bold = TRUE
objexcel.Cells(intRow, 3).Font.ColorIndex = 3
objexcel.cells(intRow, 3).Interior.ColorIndex = 1
Err.Clear
Else
objExcel.Cells(intRow, 2).Value = objItem.IPAddress
objExcel.Cells(intRow, 3).Value = "On Line"
objExcel.Cells(intRow, 3).Font.Bold = TRUE
objexcel.Cells(intRow, 3).Font.ColorIndex = 4
objexcel.cells(intRow, 3).Interior.ColorIndex = 1
End If
Next
intRow = intRow + 1
Loop
objExcel.Range("A1:D1").Select
objExcel.Selection.Interior.ColorIndex = 19
objExcel.Selection.Font.ColorIndex = 11
objExcel.Selection.Font.Bold = True
objExcel.Cells.EntireColumn.AutoFit
Set objWorkbook = Nothing
MsgBox "Done"
July 12th, 2011 1:58am
It would be easier to to create a vbscript that queried the server via WMI to check the status of the WDS service and report back. Or create an HTA that gives you a visual reference with a check or X if it is online or offline.
Otherwise you might want to contact the Excel forum to help you with your script.
http://www.sccm-tools.com http://sms-hints-tricks.blogspot.com
Free Windows Admin Tool Kit Click here and download it now
July 26th, 2011 10:25pm
I was finally able to create what i needed. Just copy/paste into a notepad and save as .vbs
'This sections sets the Excel environment
Set objExcel = CreateObject("Excel.Application")
objExcel.Visible = True
intRow = 2
Set Fso = CreateObject("Scripting.FileSystemObject")
'Change the ("PATH") to the location of the excel spreadsheet. Also change the name to what you need
Set objWorkbook = objExcel.Workbooks.Open("C:\SCCM\SCCM SERVER PING TEST\output.xls")
Set InputFile = objWorkbook
Do Until objExcel.Cells(intRow,1).Value = ""
strComputer = objExcel.Cells(intRow, 1).Value
'The data between "" can be changed to whatever is prefered. They are just enteries to identify each column
objExcel.Cells(1, 1).Value = "Machine Name"
objExcel.Cells(1, 2).Value = "IP Address"
objExcel.Cells(1, 3).Value = "Status"
objExcel.Cells(1, 4).value = "Local Disk (MB)"
ObjExcel.Cells(1, 5).value = "DATA (MB)"
ObjExcel.Cells(1, 6).value = "WDS Status"
'This section will check the computer IP address and write it to the Excel document. Will write Online/Offline aswell
On Error Resume Next
Set objWMIService = GetObject("winmgmts:\\" & strComputer & "\root\cimv2")
Set colItems = objWMIService.ExecQuery("Select * from Win32_NetworkAdapterConfiguration Where IPEnabled = True")
For Each objItem in colItems
If Err.Number <> 0 Then
objExcel.Cells(intRow, 2).Value = ""
objExcel.Cells(intRow, 3).Value = "Off Line"
objExcel.Cells(intRow, 3).Font.Bold = TRUE
objexcel.Cells(intRow, 3).Font.ColorIndex = 3
Err.Clear
Else
objExcel.Cells(intRow, 2).Value = objItem.IPAddress
objExcel.Cells(intRow, 3).Value = "On Line"
objExcel.Cells(intRow, 3).Font.Bold = TRUE
objexcel.Cells(intRow, 3).Font.ColorIndex = 1
end if
Next
'This section checks the freespace on the C:. /1048576 is to divide the bytes into MB
For Each objDisk In objwmiservice.InstancesOf("Win32_LogicalDisk")
if objDisk.Name = "C:" then
objExcel.Cells(intRow, 4).Value = objdisk.freespace /1048576
objExcel.Cells(intRow, 4).Font.Bold = ""
objexcel.Cells(intRow, 4).Font.ColorIndex = 1
end If
Next
'This section checks the freespace on "DATA" and "Repository". This can be modified to other enteries if needed. /1048576 is to divide the bytes into MB
For Each objDisk In objwmiservice.InstancesOf("Win32_LogicalDisk")
if objDisk.VolumeName = "DATA" or objDisk.VolumeName = "Repository" then
objExcel.Cells(intRow, 5).Value = objdisk.freespace /1048576
objExcel.Cells(intRow, 5).Font.Bold = ""
objexcel.Cells(intRow, 5).Font.ColorIndex = 1
end If
Next
'This section is checking the STATE of the "Windows Deployment Services (WDS) Server" for Microsoft Server 2003 and "Windows Deployment Services Server" for Microsoft Server 2008.
For Each objWMIService In objWMIService.InstancesOf("Win32_Service")
If objWMIService.Displayname = "Windows Deployment Services (WDS) Server" Or objWMIService.Displayname = "Windows Deployment Services Server" Then
objExcel.Cells(intRow, 6).Value = objWMIService.state
objExcel.Cells(intRow, 6).Font.Bold = ""
Select Case objWMIService.state
Case "Stopped"
objExcel.cells(intRow, 6).Font.ColorIndex = 3
Case "Running"
objexcel.Cells(intRow, 6).Font.ColorIndex = 1
Case Else
objexcel.Cells(intRow, 6).Font.ColorIndex = 1
End Select
End If
Next
intRow = intRow + 1
Loop
'Only need to change the "A1:Value1". Value being B1 through Z1. This is where the script will stop writing.
objExcel.Range("A1:F1").Select
objExcel.Selection.Interior.ColorIndex = 19
objExcel.Selection.Font.ColorIndex = 11
objExcel.Selection.Font.Bold = True
objExcel.Cells.EntireColumn.AutoFit
Set objWorkbook = Nothing
MsgBox "Done"
September 23rd, 2011 7:51pm