<

[Okta Webinar] Learn how to a build a cloud-first strategyRegister Now

x

Server Status Report VBScript

Published on
9,837 Points
6,037 Views
3 Endorsements
Last Modified:
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
Comment
Author:JenH2
  • 7
  • 4
  • 3
  • +3
17 Comments
 
LVL 2

Author Comment

by:JenH2
Wow, the code snippet looked a lot cleaner in UltraEdit.  I attached the script as a text file, hopefully that will be easier to read through.
0
 
LVL 65

Expert Comment

by:RobSampson
Hi, that's a decent script. Nicely written...pretty easy to follow.

The only thing that might make it a bit shorter is the AutoFit section.

I think after the data is entered, you should be able to just use
objExcel.ActiveSheet.Columns.AutoFit

Regards,

Rob.
0
 
LVL 11

Expert Comment

by:bsharath
Hi Rob... I dont know if i can put this here. If i should not then please delete it...

I get this error message

---------------------------
Windows Script Host
---------------------------
Script:      C:\Stats.vbs
Line:      138
Char:      3
Error:      Expected statement
Code:      800A0400
Source:       Microsoft VBScript compilation error

---------------------------
OK  
---------------------------
0
VIDEO: THE CONCERTO CLOUD FOR HEALTHCARE

Modern healthcare requires a modern cloud. View this brief video to understand how the Concerto Cloud for Healthcare can help your organization.

 
LVL 65

Expert Comment

by:RobSampson
Try changing this:
Set colIPItems = objWMIService.ExecQuery_
            ("SELECT * FROM Win32_NetworkAdapterConfiguration WHERE IPEnabled = True")

to this
Set colIPItems = objWMIService.ExecQuery _
            ("SELECT * FROM Win32_NetworkAdapterConfiguration WHERE IPEnabled = True")

Rob.
0
 
LVL 11

Expert Comment

by:bsharath
Rob i get error on 152 now
0
 
LVL 65

Expert Comment

by:RobSampson
Hmmmm, there appears to be a few line continuation errors there...try this.
'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

0
 
LVL 11

Expert Comment

by:bsharath
Rob when i run i get no errors and the script completes without any output
0
 
LVL 11

Expert Comment

by:bsharath
Sorry it does work perfect now...
0
 
LVL 17

Expert Comment

by:Premkumar Yogeswaran
Nice Script....!

Hats off to Rob...!

Cheers,
Prem

Voted - Yes
0
 

Expert Comment

by:mehuljadeja
Its very useful script for Administrator...!!!

Q. - This script is stopped (no error as well) when the server is in hung state or not responding.

Any help would be highly appreciated ...

Thanks in Advance.
<-email address removed by admin->
0
 
LVL 65

Expert Comment

by:RobSampson
Hi, I have cleaned up the code a bit, and added some extra output.  You should at least be able to tell where it gets stuck with this, or see that it is actually doing something (because it takes a while).

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
'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: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
	'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
			'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, ",")
					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..."
				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,12).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,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
		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

Open in new window

0
 

Expert Comment

by:mehuljadeja
Hello Rob,

Here is the output.The script stuck when it try to connect server "PM1BSH4". I waited for 30 mins but still no luck.
FYI - Right now this server is in hung state but script could not skip the server.

Microsoft (R) Windows Script Host Version 5.6
Copyright (C) Microsoft Corporation 1996-2001. All rights reserved.

Starting Microsoft Excel...
Connecting to BL1ADCA
Connection successful. Retrieving information...
      ...OS Details...
      ...SQL Server Version...
      ...Serial Number...
      ...Memory Details...
      ...Processor Details...
      ...IP Addresses...
      ...Hard Disk Details...
      ...Altiris Client Details...
Connecting to BL1ADCB
Connection successful. Retrieving information...
      ...OS Details...
      ...SQL Server Version...
      ...Serial Number...
      ...Memory Details...
      ...Processor Details...
      ...IP Addresses...
      ...Hard Disk Details...
      ...Altiris Client Details...
Connecting to 1234
WMI Connection failed to 1234
Connecting to BL1AVHA
Connection successful. Retrieving information...
      ...OS Details...
      ...SQL Server Version...
      ...Serial Number...
      ...Memory Details...
      ...Processor Details...
      ...IP Addresses...
      ...Hard Disk Details...
      ...Altiris Client Details...
Connecting to PM1BSH4
0
 
LVL 65

Expert Comment

by:RobSampson
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.
0
 

Expert Comment

by:mehuljadeja
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!
0
 
LVL 65

Expert Comment

by:RobSampson
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.
0
 

Expert Comment

by:56MDG
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?
0
 
LVL 65

Expert Comment

by:RobSampson
Hi, I've added a WMI Time Out that mehuljadeja requested, using a function I wrote here:
http://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

0

Featured Post

VIDEO: THE CONCERTO CLOUD FOR HEALTHCARE

Modern healthcare requires a modern cloud. View this brief video to understand how the Concerto Cloud for Healthcare can help your organization.

Join & Write a Comment

Exchange organizations may use the Journaling Agent of the Transport Service to archive messages going through Exchange. However, if the Transport Service is integrated with some email content management application (such as an anti-spam), the admin…
This lesson discusses how to use a Mainform + Subforms in Microsoft Access to find and enter data for payments on orders. The sample data comes from a custom shop that builds and sells movable storage structures that are delivered to your property. …
Suggested Courses
Course of the Month19 days, 12 hours left to enroll

Keep in touch with Experts Exchange

Tech news and trends delivered to your inbox every month