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

This topic is archived. No further replies will be accepted.

Other recent topics Other recent topics