Get status from server and update in database

I use this script to check the status of servers on the network. The script connects to a sql server database and gets the server names from a table called servers. After that the script pings the server to ensure there is a network connection possible. If the ping returns OK then a WMI query runs to ensure that the Operating System is healthy as well. This script is part of a series scripts that update a monitor database. This script is scheduled to run every 10 minutes. There is also a script that checks the hard disk spacwe which runs once every hour. The script uses a stored procedure to update the table in the database.See CreSP.sql on how to create the stored procedure.

This script uses a component from http://www.netal.com/ssr.htm to ping servers on the network.


On Error Resume next
'Get static systeminfo from servers on the network

'** Database Access
Const adOpenKeyset = 1
Const adOpenDynamic = 2
Const adLockPessimistic = 2
Const adCmdText = 1
Const adCmdStoredProc = 4


Dim objWMIObject, objServices, objNetwork, objRole
Dim objIP
  
'** create IPnetwork object
Set objIP = CreateObject("SScripting.IPNetwork")
'** Get some error messages printed if there are any.
Errorhandling 

'** sql 7 provider                                                                                              
'strSql7_0 = "Provider=SQLOLEDB.1;"							
'** sql 2000 provider
strSql2000 = "Provider=SQLOLEDB;"
'** Access 2000 provider
'strAccess2000 = "Provider=Microsoft.Jet.OLEDB.4.0;"	

'** Data Source = netbios name of the sql server
strDbServer = "Data Source=mysqlserver;"				
'** database	 to connect to
strDataBase = "Initial Catalog=Systeminfo;"	
'** substitute with a valid UserId		
strUser = "User ID=user;"							
'** substitute with a valid password		
strPassword = "Password=secret;"							

'** make connection string
strConn = strSql2000 & strDbServer _
          & strDatabase & strUser & strPassword
Dim objConn
'** create a connection to the datatbase
Set objConn = CreateObject("ADODB.Connection")

objConn.open strConn

'** create the objects to run a sored procedure
Set objCmd = CreateObject("ADODB.Command")
Set objCmd.ActiveConnection = objConn

objCmd.CommandText = "Update_ServerStatus"
objCmd.CommandType = adCmdStoredProc
        
'** get the servernames to check from the servers table
Set objRst = objConn.Execute("Select * From Servers",, adCmdText)

While not objRst.EOF
	'** set initial values for variables
   strServer = Trim(objRst.Fields(0))
   strPingStatus = "Error"
   strOSStatus = "Error"
   '** output the servername that is being checked
   wscript.echo strServer 
   '** ping the server
   Select Case objIP.Ping(strServer)
   	Case 0
   		strPingstatus = "OK"
   	Case 10
   		strPingstatus = "Unreachable"
   	Case 30
   		strPingstatus = "Time Out"
   	Case Else 
   		strPingstatus = "Error"
   End Select
   '** if server is reachable then check the OS
   If strPingstatus = "OK" Then
      Set objWMIService = GetObject("winmgmts:" _
          & "{impersonationLevel=impersonate}!\\" _ 
          & strserver & "\root\cimv2")
      
      Set colSettings = objWMIService.ExecQuery("Select" _
      & " * from Win32_OperatingSystem")    
      For Each objOperatingSystem in colSettings 
          strSrvStatus = objOperatingSystem.Status
      Next 
   Else
   	strSrvStatus = "Not Checked"  
   End If
	objCmd.Parameters.Refresh

	'** Set the param value for calling stored procedure.
	objCmd(1) = strServer
	objCmd(2) = strSrvStatus
	objCmd(3) = strPingStatus
	'** hard disk status is being checked in another script
	objcmd(4) = ""
	'** run stored procedure
	Set objRs = objCmd.Execute
  
	'** get the next record
	objRst.movenext
	Set objOperatingsystem = nothing
Wend

'** procedure for errorhandling
Sub ErrorHandling()
	Select Case err.number
		Case 0
			Exit sub
		Case 	-2147467259
			wscript.echo "Database is locked"
			wscript.quit(1)
		Case else
	   	wscript.echo err.number & " " & err.description
	   	err.clear
	  End select
End Sub