Server Status Report VBScript

JenH2
Published:
Updated:
Browse All Articles > Server Status Report VBScript
Recently I finished a vbscript that I thought I'd share.  It uses a text file with a list of server names to loop through and get various status reports, then writes them all into an Excel file.  Originally it was put together for our Altiris server environment, but it is easily adaptable to whatever purpose you'd have.  Really, only the last column in the report "Number of Clients" is Altiris specific, the rest is totally open ended.

Note:  The text file with the server names needs a line feed between the server names.

Server1
Server2
Server3

Not:  Server1, Server2, Server3

The things I have the script report are:

Server Name
Location
Operating System + Service Pack
SQL Version
Manufacturer
Model
IP Address
Logical Disk
Number of Clients

Note:  The Number of Clients is purely for Altiris.
  
'I use the file extension XLSX because I have Office 2007, 
                      'for older versions change it to XLS
                      'Unless you specify a folder path (ie: C:\Serverdata\ServerStats.xlsx) 
                      'the file will default to your My Documents folder.
                      strExcelPath = "ServerStats.xlsx" 
                      'Access FSO to handle text
                      Set ObjFSO = CreateObject("Scripting.FileSystemObject") 
                      'This text file holds the list of servers to get status from.
                      Set objServerList = objFSO.OpenTextFile("ServerList.txt") 
                      'Create an Excel instance to write data too
                      'The script could be easily modified to write the data sets to 
                      'a SQL database, text file, csv or whatever.
                      Set objExcel = CreateObject("Excel.Application") 
                      'Excel commands to open a new workbook and add a worksheet to it
                      objExcel.Workbooks.Add
                      objExcel.ActiveWorkbook.Worksheets.Add 
                      'Sets the worksheet as the active sheet to use.  
                      'If using more than one sheet, 
                      'change the active sheet by changing the number in the ()
                      Set objSheet = objExcel.ActiveWorkbook.Worksheets(1) 
                      'This section writes the Header information to the worksheet 
                      'and sets the font for them to bold
                      objSheet.Range("A1:M1").Font.Bold = True
                      objSheet.Cells(1, 1).Value = "Server Name"
                      objSheet.Cells(1, 2).Value = "Location"
                      objSheet.Cells(1, 3).Value = "Operating System"
                      objSheet.Cells(1, 4).Value = "SQL Version"
                      objSheet.Cells(1, 5).Value = "Serial Number"
                      objSheet.Cells(1, 6).Value = "Manufacturer"
                      objSheet.Cells(1, 7).Value = "Model"
                      objSheet.Cells(1, 8).Value = "Memory"
                      objSheet.Cells(1, 9).Value = "# of Processors"
                      objSheet.Cells(1, 10).Value = "Processor Type"
                      objSheet.Cells(1, 11).Value = "IP Address"
                      objSheet.Cells(1, 12).Value = "Logical Disk"
                      objSheet.Cells(1, 13).Value = "Number of Clients" 
                      'This integer will be the row that data is written to in Excel.  
                      'If you didn't have a header row or more than one header row, 
                      'you would modify the number to whichever row to start writing to.
                      Row = 2 
                      'Starts the Loop of reading through the text file containing the server names.
                      Do While Not objServerList.AtEndOfStream
                      'In case one of the servers is offline, I added a Resume here.
                      	On Error Resume Next
                      'Sets the computer variable for the server name read out of the text file
                      strComputer = objServerList.ReadLine 
                      'Connects to remote computer and creates a WMI instance
                      Set objWMIService = GetObject("winmgmts:" & "{impersonationLevel=impersonate}!\\"_
                      	& strComputer & "\root\cimv2")
                          
                      'Log failed connections
                      If Err.Number <> 0 Then
                      	Set objLogOut = objFSO.CreateTextFile("ServerList.log",2, True)
                      objLogOut.Write("Connection failed on:  " & strComputer & " on " & Now & vbCrLf)
                      'Continue with script if no error encounters
                      	ElseIf Err.Number = 0 Then
                      	
                      'Writes the server name to the first column
                      objSheet.Cells(Row,1).Value = strComputer 
                      'Uses a Case function to parse through the strComputer and write the location specified
                      strSite = ""
                      Select Case strComputer
                      	Case "AltirisNS1" strSite = "Notification Server"
                      	Case "Package1" strSite = "Package Server USA"
                      	Case "Package2" strSite = "Package Server Asia"
                      	Case "Deployment1" strSite = "Deployment Server USA"
                      	Case "Deployment2" strSite = "Deployment Server Asia"
                      	Case Else strSite = "Unknown Site"
                      End Select	
                      objSheet.Cells(Row,2).Value = strSite 
                      	
                      'Gets OS   Service Pack
                      Set colOSes = objWMIService.ExecQuery("Select * from Win32_OperatingSystem")
                      	For Each objOS in colOSes
                      objSheet.Cells(Row,3).Value = objOS.Caption & " SP "_
                      		& objOS.ServicePackMajorVersion & "." & objOS.ServicePackMinorVersion
                      	Next
                      		
                      'Get SQL Server Version
                      'If you are checking a server with an offloaded database,
                      'you will need to add it into the If statement so you pull data 
                      'from the correct SQL Server 
                      If strComputer = "AltirisNS1" Then
                      	strDBServerName = "AltirisSQL"
                      Else
                      	strDBServerName = strComputer
                      End If 
                      'Connects to the SQLDMO Object to pull the version data
                      Set objSQLServer = CreateObject("SQLDMO.SQLServer")
                      objSQLServer.LoginSecure = True
                      objSQLServer.Connect strDBServerName
                      strVersionInfo = objSQLServer.VersionString 
                      'The VersionString data that is returned is very long
                      'and not well suited to write into Excel.
                      'Here is what the VersionString looks like normally: 
                      'Microsoft SQL Server 2005 - 9.00.3054.00 (Intel X86) 
                      '	Mar 23 2007 16:28:52 
                      '	Copyright (c) 1988-2005 Microsoft Corporation
                      '	Standard Edition on Windows NT 5.2 (Build 3790: Service Pack 2)
                       
                      'In order to cut the string down to something more concise (Version & Edition)
                      'a Split/Trim loop is used to gather the relevant data.
                      '(Thank you to RobSampson at ExchangeExperts for his help with this part) 
                      For Each strLine In Split(strVersionInfo, VbLf)
                      If InStr(strLine, "SQL Server") > 0 Then
                      	strSQLVer = Trim(Left(strLine, InStr(strLine, " -") - 1))
                      ElseIf InStr(strLine, "on Windows") > 0 Then
                      	strSQLEd = Trim(Left(strLine, InStr(strLine, "on Windows") - 1))
                      End If
                      Next 
                      objSheet.Cells(Row,4).Value = strSQLVer & "-" & strSQLEd 
                      'Get Serial Number
                      Set oWMIService = GetObject("winmgmts:\\" & strComputer & "\root\cimv2")
                      Set colItems1 = objWMIService.ExecQuery("SELECT * FROM Win32_BIOS",,48)
                      	For Each oItem In colItems1
                      objSheet.Cells(Row,5).Value = oItem.SerialNumber
                      	Next
                      		
                      'Gather several Computer System data sets.
                      'For this script, I grab Manufacturer, Model, 
                      'RAM (rounded down to # of gigs), and Processor count.
                      Set colSettings = objWMIService.ExecQuery("Select * from Win32_ComputerSystem")
                      	For Each objComputer in colSettings
                      objSheet.Cells(Row,6).Value = objComputer.Manufacturer
                      objSheet.Cells(Row,7).Value = objComputer.Model
                      objSheet.Cells(Row,8).Value = Round((objComputer.TotalPhysicalMemory/1000000000),4)
                      objSheet.Cells(Row,9).Value = objComputer.NumberOfProcessors
                      	Next
                      		
                      'Get Processor Information
                      Set colItems = objWMIService.ExecQuery("Select * from Win32_Processor",,48)
                      	For Each objItem in colItems
                      objSheet.Cells(Row,10).Value = objItem.Name
                      	Next
                      		
                      'Get the IP Address
                      Set colIPItems = objWMIService.ExecQuery_
                      		("SELECT * FROM Win32_NetworkAdapterConfiguration WHERE IPEnabled = True")
                      	For Each objIPItem In colIPItems 
                      		strIPAddress = Join(objIPItem.IPAddress, ",")
                      objSheet.Cells(Row,11).Value = strIPAddress
                      	Next
                      		
                      'Get Logical Disk Size and Partition Information
                      'In order to deal with multiple disks and partitions,
                      'the list of HDD information is pulled into an array
                      'which is looped through to get the information from each HDD
                      'and then a Join function writes them all as a single string 
                      Dim arrDisks()
                      intArr = 1
                      Set colDisks = objWMIService.ExecQuery_
                      	("Select * from Win32_LogicalDisk Where DriveType = 3")
                      For Each objDisk in colDisks
                      ReDim Preserve arrDisks(intArr) 
                      arrDisks(intArr) = "DISK " & objDisk.DeviceID & " (" & objDisk.FileSystem & ") _
                      	" & Round((objDisk.Size/1000000000),4) & _
                      	" GB ("& Round((objDisk.FreeSpace/1000000000)*1.024,4) & " GB Free Space)" & " "
                      intArr = intArr   1
                      Next
                      'I used a " | " to seperate each disk
                      objSheet.Cells(Row,12).Value = Join(arrDisks," | ") 
                      'Get Number of Altiris Clients
                      'This is purely something for use with Altiris servers,
                      'but again, the function can be modified for other purposes 
                      'Creates an ADODB object to access SQL servers
                      Set CnnSQL=CreateObject("ADODB.Connection") 
                      'Again, just like with getting the version information,
                      'if you have an offloaded database, you need to use this
                      'If...Then...Else... loop to point to the correct server.
                      'For the query itself I just took the query to list clients
                      'and added a COUNT SQL function to the query. 
                      If strComputer = "AltirisNS1" Then
                      CnnSQL.Open "Provider=SQLOLEDB;DATA SOURCE=IRVSQL51;_
                      		INITIAL CATALOG=Altiris2;Integrated Security=SSPI"
                      	Set RS = CreateObject("ADODB.Recordset") 
                      	Set RS = CnnSQL.Execute_
                      		("SELECT COUNT (guid) FROM vComputerResource WHERE IsManaged=1")
                      Else
                      CnnSQL.Open "Provider=SQLOLEDB;DATA SOURCE=" & strComputer & _
                      		";INITIAL CATALOG=eXpress;Integrated Security=SSPI"
                      	Set RS = CreateObject("ADODB.Recordset") 
                      	Set RS = CnnSQL.Execute("SELECT COUNT(name) FROM computer")
                      End If
                      objSheet.Cells(Row,13).Value = RS(0)
                      RS.Close
                      Set RS = Nothing
                      Set CnnSQL = Nothing  
                      'This ends the Loop where the server name was read from the text file
                      'and adds a count to the Row integer so data will now write on the next row in Excel 
                      End if
                      Row = Row   1
                      Loop 
                      'This whole section is a series of Excel functions to make the report look better
                      'I parse through each column and Autofit the column width, 
                      'then at the end I set the active cell back to A1. 
                      Set objRange = objExcel.Range("A1")
                      objRange.Activate
                      Set objRange = objExcel.ActiveCell.EntireColumn
                      objRange.Autofit() 
                      Set objRange = objExcel.Range("B1")
                      objRange.Activate
                      Set objRange = objExcel.ActiveCell.EntireColumn
                      objRange.Autofit() 
                      Set objRange = objExcel.Range("C1")
                      objRange.Activate
                      Set objRange = objExcel.ActiveCell.EntireColumn
                      objRange.Autofit() 
                      Set objRange = objExcel.Range("D1")
                      objRange.Activate
                      Set objRange = objExcel.ActiveCell.EntireColumn
                      objRange.Autofit() 
                      Set objRange = objExcel.Range("E1")
                      objRange.Activate
                      Set objRange = objExcel.ActiveCell.EntireColumn
                      objRange.Autofit() 
                      Set objRange = objExcel.Range("F1")
                      objRange.Activate
                      Set objRange = objExcel.ActiveCell.EntireColumn
                      objRange.Autofit() 
                      Set objRange = objExcel.Range("G1")
                      objRange.Activate
                      Set objRange = objExcel.ActiveCell.EntireColumn
                      objRange.Autofit() 
                      Set objRange = objExcel.Range("H1")
                      objRange.Activate
                      Set objRange = objExcel.ActiveCell.EntireColumn
                      objRange.Autofit() 
                      Set objRange = objExcel.Range("I1")
                      objRange.Activate
                      Set objRange = objExcel.ActiveCell.EntireColumn
                      objRange.Autofit() 
                      Set objRange = objExcel.Range("J1")
                      objRange.Activate
                      Set objRange = objExcel.ActiveCell.EntireColumn
                      objRange.Autofit() 
                      Set objRange = objExcel.Range("K1")
                      objRange.Activate
                      Set objRange = objExcel.ActiveCell.EntireColumn
                      objRange.Autofit() 
                      Set objRange = objExcel.Range("L1")
                      objRange.Activate
                      Set objRange = objExcel.ActiveCell.EntireColumn
                      objRange.Autofit() 
                      Set objRange = objExcel.Range("M1")
                      objRange.Activate
                      Set objRange = objExcel.ActiveCell.EntireColumn
                      objRange.Autofit() 
                      Set objRange = objExcel.Range("A1")
                      objRange.Activate 
                      'Saves the Excel workbook and closes everything up
                      objExcel.ActiveWorkbook.SaveAs strExcelPath
                      objExcel.ActiveWorkbook.Close
                      objExcel.Application.Quit 
                      'Added in a Done echo because the script can take several minutes 
                      'depending on how many servers that are being queried.
                      WScript.Echo "Done"

Open in new window

ServerStatsScript.txt
ServerListExample.txt
3
6,833 Views
Ask a related question

Comments (17)

RobSampson
CERTIFIED EXPERT
Most Valuable Expert 2012
Top Expert 2014

Commented:
Hi, an article isn't the place for this discussion.  Please raise a new question for further modifications to this code.

However, there's nothing that can be done about hung WMI connections.  The only thing you can do is remove that server from your list, and run it without that server, and investigate the WMI problems of that server.

Regards,

Rob.
mehuljadeja

Commented:
Hello Rob, Thanks a Million for your efforts.

Well, We have very large Datacenter (around 2500 Servers) and I have schedule this script for every week, so its difficult to identify that which server is hung or WMI is corrupt on it thus script is stopped.

Is it possible for you to script can try around 30 seconds and if the server is hung, script can write "Error" in excel and go to next for rest of servers ?

Thanks Again.
Mehu!
RobSampson
CERTIFIED EXPERT
Most Valuable Expert 2012
Top Expert 2014

Commented:
No, you can't monitor the WMI connection attempt and terminate it.  The GetObject call does not support that at all.  The only way to know what server has connection issues is to add this:
objExcel.Visible = True

under this line:
Set objWB = objExcel.Workbooks.Add

Then make sure when you run it, it is running interactively (ie, someone is logged in), and see what the value of the last server is.  Then, check the text file, and the problem computer will be the *next* computer.

The only other way to do it would be to run a multi-threaded process, which would be complicated.  If you post a separate question, and post the link to it here, I might be able to get something to work.

Regards,

Rob.
56MDG

Commented:
How would you modify the script to show number of physical disks and could you take out the Altiris information and replace it with SCCM client?
RobSampson
CERTIFIED EXPERT
Most Valuable Expert 2012
Top Expert 2014

Commented:
Hi, I've added a WMI Time Out that mehuljadeja requested, using a function I wrote here:
https://www.experts-exchange.com/A_4379.html

I have also added the number of physical disks to the output.

I don't know about the SCCM output. You probably can.  If you can find some code to give you SCCM output, we could plug that in.

Regards,

Rob.
If LCase(Right(Wscript.FullName, 11)) = "wscript.exe" Then
    strPath = Wscript.ScriptFullName
    strCommand = "%comspec% /c cscript  """ & strPath & """"
    Set objShell = CreateObject("Wscript.Shell")
    objShell.Run(strCommand), 1, True
    Wscript.Quit
End If
Const WMITimeOutInSeconds = 10
'I use the file extension XLSX because I have Office 2007, 
'for older versions change it to XLS
'Unless you specify a folder path (ie: C:\Serverdata\ServerStats.xlsx) 
'the file will default to your My Documents folder.
strExcelPath = "ServerStats.xlsx" 
'Access FSO to handle text
Set ObjFSO = CreateObject("Scripting.FileSystemObject") 
'This text file holds the list of servers to get status from.
Set objServerList = objFSO.OpenTextFile("ServerList.txt") 
'Create an Excel instance to write data too
'The script could be easily modified to write the data sets to 
'a SQL database, text file, csv or whatever.
WScript.Echo "Starting Microsoft Excel..."
Set objExcel = CreateObject("Excel.Application") 
'Excel commands to open a new workbook and add a worksheet to it
Set objWB = objExcel.Workbooks.Add
objExcel.DisplayAlerts = False
While objWB.Sheets.Count > 1
	objWB.Sheets(objWB.Sheets.Count).Delete
Wend
objExcel.DisplayAlerts = True
'Sets the worksheet as the active sheet to use.  
'If using more than one sheet, 
'change the active sheet by changing the number in the ()
Set objSheet = objWB.Worksheets(1) 
'This section writes the Header information to the worksheet 
'and sets the font for them to bold
objSheet.Range("A1:N1").Font.Bold = True
objSheet.Cells(1, 1).Value = "Server Name"
objSheet.Cells(1, 2).Value = "Location"
objSheet.Cells(1, 3).Value = "Operating System"
objSheet.Cells(1, 4).Value = "SQL Version"
objSheet.Cells(1, 5).Value = "Serial Number"
objSheet.Cells(1, 6).Value = "Manufacturer"
objSheet.Cells(1, 7).Value = "Model"
objSheet.Cells(1, 8).Value = "Memory"
objSheet.Cells(1, 9).Value = "# of Processors"
objSheet.Cells(1, 10).Value = "Processor Type"
objSheet.Cells(1, 11).Value = "IP Address"
objSheet.Cells(1, 12).Value = "No of Physical Disks"
objSheet.Cells(1, 13).Value = "Logical Disk"
objSheet.Cells(1, 14).Value = "Number of Clients" 
'This integer will be the row that data is written to in Excel.  
'If you didn't have a header row or more than one header row, 
'you would modify the number to whichever row to start writing to.
Row = 2 
'Starts the Loop of reading through the text file containing the server names.
Do While Not objServerList.AtEndOfStream
	'Sets the computer variable for the server name read out of the text file
	strComputer = objServerList.ReadLine 
	If Trim(strComputer) <> "" Then
		WScript.Echo "Connecting to " & strComputer
		If Ping(strComputer) = True Then
			strReturn = TestWMIConnection(strComputer, WMITimeOutInSeconds)
			If strReturn = "success" Then
				'In case one of the servers has WMI errors, I added a Resume here.
				On Error Resume Next
				'Connects to remote computer and creates a WMI instance
				Set objWMIService = GetObject("winmgmts:" & "{impersonationLevel=impersonate}!\\" & strComputer & "\root\cimv2")
				'Log failed connections
				If Err.Number <> 0 Then
					Set objLogOut = objFSO.OpenTextFile("ServerList.log",2, True)
					objLogOut.Write("Connection failed on:  " & strComputer & " on " & Now & vbCrLf)
					objLogOut.Close
					WScript.Echo "WMI Connection failed to " & strComputer
					Err.Clear
					'Continue with script if no error encounters
				ElseIf Err.Number = 0 Then
					WScript.Echo "Connection successful. Retrieving information..."
					'Writes the server name to the first column
					objSheet.Cells(Row,1).Value = strComputer 
					'Uses a Case function to parse through the strComputer and write the location specified
					strSite = ""
					Select Case strComputer
						Case "AltirisNS1" strSite = "Notification Server"
						Case "Package1" strSite = "Package Server USA"
						Case "Package2" strSite = "Package Server Asia"
						Case "Deployment1" strSite = "Deployment Server USA"
						Case "Deployment2" strSite = "Deployment Server Asia"
						Case Else strSite = "Unknown Site"
					End Select	
					objSheet.Cells(Row,2).Value = strSite 
					
					'Gets OS   Service Pack
					WScript.Echo vbTab & "...OS Details..."
					Set colOSes = objWMIService.ExecQuery("Select Caption,ServicePackMajorVersion,ServicePackMinorVersion from Win32_OperatingSystem")
					For Each objOS in colOSes
						objSheet.Cells(Row,3).Value = objOS.Caption & " SP " & objOS.ServicePackMajorVersion & "." & objOS.ServicePackMinorVersion
					Next
	
					'Get SQL Server Version
					'If you are checking a server with an offloaded database,
					'you will need to add it into the If statement so you pull data 
					'from the correct SQL Server 
					WScript.Echo vbTab & "...SQL Server Version..."
					If strComputer = "AltirisNS1" Then
						strDBServerName = "AltirisSQL"
					Else
						strDBServerName = strComputer
					End If 
					'Connects to the SQLDMO Object to pull the version data
					Set objSQLServer = CreateObject("SQLDMO.SQLServer")
					objSQLServer.LoginSecure = True
					objSQLServer.Connect strDBServerName
					strVersionInfo = objSQLServer.VersionString 
					'The VersionString data that is returned is very long
					'and not well suited to write into Excel.
					'Here is what the VersionString looks like normally: 
					'Microsoft SQL Server 2005 - 9.00.3054.00 (Intel X86) 
					'	Mar 23 2007 16:28:52 
					'	Copyright (c) 1988-2005 Microsoft Corporation
					'	Standard Edition on Windows NT 5.2 (Build 3790: Service Pack 2)
					 
					'In order to cut the string down to something more concise (Version & Edition)
					'a Split/Trim loop is used to gather the relevant data.
					'(Thank you to RobSampson at ExchangeExperts for his help with this part) 
					For Each strLine In Split(strVersionInfo, VbLf)
						If InStr(strLine, "SQL Server") > 0 Then
							strSQLVer = Trim(Left(strLine, InStr(strLine, " -") - 1))
						ElseIf InStr(strLine, "on Windows") > 0 Then
							strSQLEd = Trim(Left(strLine, InStr(strLine, "on Windows") - 1))
						End If
					Next
					objSheet.Cells(Row,4).Value = strSQLVer & "-" & strSQLEd 
					
					'Get Serial Number
					WScript.Echo vbTab & "...Serial Number..."
					Set colItems1 = objWMIService.ExecQuery("SELECT SerialNumber FROM Win32_BIOS",,48)
					For Each oItem In colItems1
						objSheet.Cells(Row,5).Value = oItem.SerialNumber
					Next
	
					'Gather several Computer System data sets.
					'For this script, I grab Manufacturer, Model, 
					'RAM (rounded down to # of gigs), and Processor count.
					WScript.Echo vbTab & "...Memory Details..."
					Set colSettings = objWMIService.ExecQuery("Select Manufacturer,Model,TotalPhysicalMemory,NumberOfProcessors from Win32_ComputerSystem")
					For Each objComputer in colSettings
						objSheet.Cells(Row,6).Value = objComputer.Manufacturer
						objSheet.Cells(Row,7).Value = objComputer.Model
						objSheet.Cells(Row,8).Value = Round((objComputer.TotalPhysicalMemory/1000000000),4)
						objSheet.Cells(Row,9).Value = objComputer.NumberOfProcessors
					Next
	
					'Get Processor Information
					WScript.Echo vbTab & "...Processor Details..."
					Set colItems = objWMIService.ExecQuery("Select Name from Win32_Processor",,48)
					For Each objItem in colItems
						objSheet.Cells(Row,10).Value = objItem.Name
					Next
	
					'Get the IP Address
					WScript.Echo vbTab & "...IP Addresses..."
					Set colIPItems = objWMIService.ExecQuery("SELECT IPAddress FROM Win32_NetworkAdapterConfiguration WHERE IPEnabled = True")
					For Each objIPItem In colIPItems 
						strIPAddress = Join(objIPItem.IPAddress, ",")
						If strIPAddress <> "0.0.0.0" Then objSheet.Cells(Row,11).Value = strIPAddress
					Next
	
					'Get Logical Disk Size and Partition Information
					'In order to deal with multiple disks and partitions,
					'the list of HDD information is pulled into an array
					'which is looped through to get the information from each HDD
					'and then a Join function writes them all as a single string 
					WScript.Echo vbTab & "...Hard Disk Details..."
	
					intPhysicalDisks = 0
					Set colDisks = objWMIService.ExecQuery("Select * From Win32_DiskDrive")
					For Each objDisk In colDisks
						intPhysicalDisks = intPhysicalDisks + 1
					Next
					objSheet.Cells(Row,12).Value = intPhysicalDisks
	
					Dim arrDisks()
					intArr = 1
					Set colDisks = objWMIService.ExecQuery("Select DeviceID,FileSystem,Size,FreeSpace from Win32_LogicalDisk Where DriveType = 3")
					For Each objDisk In colDisks
						ReDim Preserve arrDisks(intArr) 
						arrDisks(intArr) = "DISK " & objDisk.DeviceID & " (" & objDisk.FileSystem & ")" & Round((objDisk.Size/1000000000),4) & " GB (" & Round((objDisk.FreeSpace/1000000000)*1.024,4) & " GB Free Space)" & " "
						intArr = intArr + 1
					Next
					'I used a " | " to seperate each disk
					objSheet.Cells(Row,13).Value = Join(arrDisks," | ") 
	
					WScript.Echo vbTab & "...Altiris Client Details..."
					'Get Number of Altiris Clients
					'This is purely something for use with Altiris servers,
					'but again, the function can be modified for other purposes 
					'Creates an ADODB object to access SQL servers
					Set CnnSQL=CreateObject("ADODB.Connection") 
					'Again, just like with getting the version information,
					'if you have an offloaded database, you need to use this
					'If...Then...Else... loop to point to the correct server.
					'For the query itself I just took the query to list clients
					'and added a COUNT SQL function to the query. 
					If strComputer = "AltirisNS1" Then
						CnnSQL.Open "Provider=SQLOLEDB;DATA SOURCE=IRVSQL51;INITIAL CATALOG=Altiris2;Integrated Security=SSPI"
						Set RS = CreateObject("ADODB.Recordset") 
						Set RS = CnnSQL.Execute("SELECT COUNT (guid) FROM vComputerResource WHERE IsManaged=1")
					Else
						CnnSQL.Open "Provider=SQLOLEDB;DATA SOURCE=" & strComputer & ";INITIAL CATALOG=eXpress;Integrated Security=SSPI"
						Set RS = CreateObject("ADODB.Recordset") 
						Set RS = CnnSQL.Execute("SELECT COUNT(name) FROM computer")
					End If
					objSheet.Cells(Row,14).Value = RS(0)
					RS.Close
					Set RS = Nothing
					Set CnnSQL = Nothing  
				'This ends the Loop where the server name was read from the text file
				'and adds a count to the Row integer so data will now write on the next row in Excel 
				End If
				Row = Row + 1
			ElseIf strReturn = "failed" Then
				Set objLogOut = objFSO.OpenTextFile("ServerList.log",2, True)
				objLogOut.Write("Connection failed on:  " & strComputer & " on " & Now & vbCrLf)
				objLogOut.Close
				WScript.Echo "WMI Connection failed to " & strComputer
				Err.Clear
				'Continue with script if no error encounters
			Else
				Set objLogOut = objFSO.OpenTextFile("ServerList.log",2, True)
				objLogOut.Write("Connection time out on:  " & strComputer & " on " & Now & vbCrLf)
				objLogOut.Close
				WScript.Echo "WMI Connection time out on " & strComputer
				Err.Clear
				'Continue with script if no error encounters
			End If
		Else
			'Computer did not respond to Ping
			Set objLogOut = objFSO.OpenTextFile("ServerList.log",2, True)
			objLogOut.Write("Ping failed on:  " & strComputer & " on " & Now & vbCrLf)
			objLogOut.Close
			WScript.Echo "Ping failed to " & strComputer
		End If
	End If
Loop 

objSheet.Columns.AutoFit

'Saves the Excel workbook and closes everything up
objExcel.DisplayAlerts = False
objExcel.ActiveWorkbook.SaveAs strExcelPath
objExcel.ActiveWorkbook.Close
objExcel.DisplayAlerts = True
objExcel.Application.Quit 
'Added in a Done echo because the script can take several minutes 
'depending on how many servers that are being queried.
WScript.Echo "Done"

Function Ping(strComputer)
	Dim objShell, boolCode
	Set objShell = CreateObject("WScript.Shell")
	boolCode = objShell.Run("Ping -n 4 -w 300 " & strComputer, 0, True)
	If boolCode = 0 Then
		Ping = True
	Else
		Ping = False
	End If
End Function

Function TestWMIConnection(strComputer, intTimeOutInSeconds)
   ' Function written by Rob Sampson - 12 Jan 2011
   ' Experts-Exchange volunteer: http://www.experts-exchange.com/M_3820065.html
   ' Return strings from this function are in lower case, and consist of:
   ' "success": WMI Connection successful
   ' "failed": WMI Connection failed
   ' "time out": WMI Connection attempt timed out

   Set ObjFSO = CreateObject("Scripting.FileSystemObject")
   strTempScript = Replace(WScript.ScriptFullName, WScript.ScriptName, "") & "TempWMITestToBeDeleted.vbs"

   Set objTempFile = objFSO.CreateTextFile(strTempScript, True)
   objTempFile.WriteLine "On Error Resume Next"
   objTempFile.WriteLine "Set objWMIService = GetObject(""winmgmts:{impersonationLevel=impersonate}!\\" & strComputer & "\root\cimv2"")"
   objTempFile.WriteLine "If Err.Number = 0 Then"
   objTempFile.WriteLine vbTab & "WScript.StdOut.Write ""success"""
   objTempFile.WriteLine "Else"
   objTempFile.WriteLine vbTab & "WScript.StdOut.Write ""failed"""
   objTempFile.WriteLine "End If"
   objTempFile.Close

   Set objShell = CreateObject("WScript.Shell")
   Set objExec = objShell.Exec("wscript " & objFSO.GetFile(strTempScript).ShortPath)
   intSeconds = 0
   While objExec.Status = 0 And intSeconds <= intTimeOutInSeconds
      WScript.Sleep 1000
      intSeconds = intSeconds + 1
   Wend
   If objExec.Status = 1 Then
      strReturn = objExec.StdOut.ReadAll
   Else
      On Error Resume Next
      objExec.Terminate
      Err.Clear
      On Error GoTo 0
      strReturn = "time out"
   End If
   objFSO.DeleteFile strTempScript, True

   TestWMIConnection = LCase(strReturn)
End Function

Open in new window

View More

Have a question about something in this article? You can receive help directly from the article author. Sign up for a free trial to get started.