vbscript_服务器状态报告VBScript

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.

最近，我完成了一个我想分享的vbscript。 它使用带有服务器名称列表的文本文件来循环浏览并获取各种状态报告，然后将它们全部写入Excel文件。 最初，它是为我们的Altiris服务器环境组合而成的，但是它可以轻松地适应您的任何目的。 确实，只有“客户数量”报告中的最后一列是Altiris专用的，其余都是完全开放式的。

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

注意：带有服务器名称的文本文件需要在服务器名称之间换行。

Server1

服务器1

Server2

服务器2

Server3

服务器3

Not:  Server1, Server2, Server3

不：Server1，Server2，Server3

The things I have the script report are:

我的脚本报告包括：

Server Name

服务器名称

Location

位置

Operating System + Service Pack

操作系统+ Service Pack

SQL Version

SQL版本

Manufacturer

制造商

Model

模型

IP Address

IP地址

Logical Disk

逻辑磁盘

Number of Clients

客户数量

Note:  The Number of Clients is purely for Altiris.

注意：“客户数”仅适用于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"

ServerStatsScript.txt ServerStatsScript.txt ServerListExample.txt ServerListExample.txt

翻译自: https://www.experts-exchange.com/articles/808/Server-Status-Report-VBScript.html

vbscript