mssqlserver.vbs - vbscript script by ActiveXperts Software
mssqlserver.vbs checks whether MS SQL Server is running; it checks services, processes and performance counters.
Use mssqlserver.vbs directly from ActiveXperts Network Monitor; in the Manager's 'Monitor' menu, select 'New Check (Script)' and select mssqlserver.vbs. Configure the required parameter, or press 'Load a working sample'.
In ActiveXperts Network Monitor, Administrators can use three different scripting languages: Powershell, VBScript and SSH.
mssqlserver.vbs script code
' ///////////////////////////////////////////////////////////////////////////////
' // ActiveXperts Network Monitor - VBScript based checks
' // For more information about ActiveXperts Network Monitor and VBScript, visit
' // http://www.activexperts.com/support/network-monitor/online/vbscript/
' ///////////////////////////////////////////////////////////////////////////////
Option Explicit
' Declaration of global variables
Dim SYSDATA, SYSEXPLANATION ' SYSDATA is displayed in the 'Data' column in the Manager; SYSEXPLANATION in the 'LastResponse' column
' Constants - return values
Const retvalUnknown = 1 ' ActiveXperts Network Monitor functions should always return True (-1, Success), False (0, Error) or retvalUnknown (1, Uncertain)
' Constants - indexes used in arrays
Const idxServiceShortname = 0 '
Const idxServiceLongname = 1
Const idxPerfObject = 0
Const idxPerfContext = 1
Const idxPerfItem = 2
Const idxPerfCondition = 3
' // To test a function outside Network Monitor (e.g. using CSCRIPT from the
' // command line), remove the comment character (') in the following lines:
' Dim bResult
' bResult = CheckSqlServer( "localhost", "", "IIS7" )
' WScript.Echo "Return value: [" & bResult & "]"
' WScript.Echo "SYSDATA: [" & SYSDATA & "]"
' WScript.Echo "SYSEXPLANATION: [" & SYSEXPLANATION & "]"
Function CheckSqlServer( strHost, strAltCredentials, strVersion )
' Description:
' Checks if SQL Server is running; it checks the SQL services, SQL processes and SQL performance counters
' Parameters:
' 1) strHost As String - Hostname or IP address of the server you want to check
' 2) strAltCredentials As String - Specify an empty string to use Network Monitor service credentials.
' To use alternate credentials, enter a server that is defined in Server Credentials table.
' (To define Server Credentials, choose Tools->Options->Server Credentials)
' 3) strVersion As String - SQL Server version (either 2012, 2008, 2005, 2000 or Express)
' Usage:
' CheckSqlServer( "<Hostname | IP>", "<Empty String | Server>", "<SQL-version>" )
' Sample:
' CheckSqlServer( "localhost", "", "2008" )
Dim strAltLogin, strAltPassword
Dim objWMIService
Dim lstServices, lstProcesses, lstPerfCounters
Dim numResult
CheckSqlServer = retvalUnknown ' Default return value, and will be shown as a yellow (uncertain) icon in the Manager
SYSDATA = "" ' SYSDATA displayed in the 'Data' column in the Manager
SYSEXPLANATION = "" ' SYSEXPLANATION displayed in the 'LastResponse' column in the Manager
strAltLogin = ""
strAltPassword = ""
' Retrieve product processes/services/performance-counters list
If( Not getSoftwareInfo( strVersion, lstServices, lstProcesses, lstPerfCounters, SYSEXPLANATION ) ) Then
Exit Function
End If
' If alternate credentials are specified, retrieve the alternate login and password from the ActiveXperts global settings
If( strAltCredentials <> "" ) Then
If( Not getCredentials( strHost, strAltCredentials, strAltLogin, strAltPassword, SYSEXPLANATION )) Then
Exit Function
End If
End If
' WMI Connect
If( Not wmiConnect( strHost, strAltLogin, strAltPassword, objWMIService, SYSEXPLANATION ) ) Then
Exit Function
End If
' Check services
numResult = checkServices( objWMIService, strHost, lstServices, SYSEXPLANATION )
If( numResult <> True ) Then
CheckSqlServer = numResult
Exit Function
End If
' Check processes
numResult = checkProcesses( objWMIService, strHost, lstProcesses, SYSEXPLANATION )
If( numResult <> True ) Then
CheckSqlServer = numResult
Exit Function
End If
' Check performance counters
numResult = checkPerfCounters( strHost, strAltLogin, strAltPassword, lstPerfCounters, SYSEXPLANATION )
If( numResult <> True ) Then
CheckSqlServer = numResult
Exit Function
End If
CheckSqlServer = True
SYSEXPLANATION = "All services, processes and counters successfully checked"
End Function
' //////////////////////////////////////////////////////////////////////////////
' // --- Private Functions section ---
' // Private functions names should start with a lower case character, so they
' // will not be listed in the Network Monitor's function browser.
' //////////////////////////////////////////////////////////////////////////////
Function getSoftwareInfo( strVersion, BYREF lstServices, BYREF lstProcesses, BYREF lstPerfCounters, BYREF strSysExplanation )
' // Retrieve services, processes and counters associated to the specific version of the software. Entries that start
' // with '!' are optional services/processes/counters and are not checked by deault. Remove '!' mark to enable monitoring those items.
getSoftwareInfo = True
Select Case strVersion
Case "2012"
lstServices = array( array( "MSSQLSERVER", "SQL Server (MSSQLSERVER)" ), _
array( "!SQLSERVERAGENT", "SQL Server Agent (MSSQLSERVER)" ), _
array( "MSSQLServerOLAPService", "SQL Server Analysis Services (MSSQLSERVER)" ), _
array( "!SQLBrowser", "SQL Server Browser" ), _
array( "!SQL Server Distributed Replay Controller"), _
array( "SQL Server Integration Services 11.0" ), _
array( "ReportServer", "SQL Server Reporting Services (MSSQLSERVER)" ), _
array( "SQLWriter", "SQL Server VSS Writer" ) )
lstProcesses = array( "sqlservr.exe", "!sqlagent.exe", "msmdsrv.exe", "!sqlbrowser.exe", "!DReplayController.exe", "MsDtsSrvr.exe", "ReportingServicesService.exe", "sqlwriter.exe" )
lstPerfCounters = array( array( "Memory", "", "Available Bytes", ">104857600" ), array( "Processor", "_TOTAL", "% Processor Time", "<90" ) )
Case "2008"
lstServices = array( array( "MSSQLSERVER", "SQL Server (MSSQLSERVER)" ), _
array( "!SQLSERVERAGENT", "SQL Server Agent (MSSQLSERVER)" ), _
array( "MSSQLServerOLAPService", "SQL Server Analysis Services (MSSQLSERVER)" ), _
array( "!SQLBrowser", "SQL Server Browser" ), _
array( "ReportServer", "SQL Server Reporting Services (MSSQLSERVER)" ), _
array( "SQLWriter", "SQL Server VSS Writer" ) )
lstProcesses = array( "sqlservr.exe", "!sqlagent.exe", "msmdsrv.exe", "!sqlbrowser.exe", "ReportingServicesService.exe", "sqlwriter.exe" )
lstPerfCounters = array( array( "Memory", "", "Available Bytes", ">104857600" ), array( "Processor", "_TOTAL", "% Processor Time", "<90" ) )
Case "2005"
lstServices = array( array( "MSSQLSERVER", "SQL Server (MSSQLSERVER)" ), _
array( "!SQLSERVERAGENT", "SQL Server Agent (MSSQLSERVER)" ), _
array( "MSSQLServerOLAPService", "SQL Server Analysis Services (MSSQLSERVER)" ), _
array( "!SQLBrowser", "SQL Server Browser" ), _
array( "msftesql", "SQL Server FullText Search (MSSQLSERVER)" ), _
array( "ReportServer", "SQL Server Reporting Services (MSSQLSERVER)" ), _
array( "!SQLWriter", "SQL Server VSS Writer" ) )
lstProcesses = array( "sqlservr.exe", "!sqlagent.exe", "msmdsrv.exe", "!sqlbrowser.exe", "msftesql.exe", "ReportingServicesService.exe", "!sqlwriter.exe" )
lstPerfCounters = array( array( "Memory", "", "Available Bytes", ">104857600" ), array( "Processor", "_TOTAL", "% Processor Time", "<90" ) )
Case "2000"
lstServices = array( array( "MSSQLSERVER", "Microsoft SQL Server" ) )
lstProcesses = array( "sqlservr.exe", "sqlmangr.exe" )
lstPerfCounters = array( array( "Memory", "", "Available Bytes", ">104857600" ), array( "Processor", "_TOTAL", "% Processor Time", "<90" ) )
Case "Express"
lstServices = array( array( "MSSQL$SQLEXPRESS", "SQL Server (SQLEXPRESS)" ) )
lstProcesses = array( "sqlservr.exe", "sqlbrowser.exe", "sqlwriter.exe" )
lstPerfCounters = array( array( "Memory", "", "Available Bytes", ">104857600" ), array( "Processor", "_TOTAL", "% Processor Time", "<90" ) )
Case Else
SYSEXPLANATION = "Version [" + strVersion + "] is not supported."
lstServices = array( array( "", "" ) )
lstProcesses = array( "" )
lstPerfCounters = array( array( "", "", "", "" ) )
getSoftwareInfo = False
End Select
End Function
' //////////////////////////////////////////////////////////////////////////////
Function checkServices( objWMIService, strHost, lstCheckServices, BYREF strExplanation )
Dim lstAllServices, arrCheckService
checkServices = retvalUnknown ' Default return value
strExplanation = "Unable to check for services on this machine"
' Get the services list
If( Not retrieveServicesList( objWMIService, strHost, lstAllServices, strExplanation ) ) Then
Exit Function
End If
' Check services - only those that are not disabled by a leading '!'
For Each arrCheckService in lstCheckServices
If( arrCheckService( idxServiceShortname) <> "" And Left( arrCheckService( idxServiceShortname ), 1 ) <> "!" ) Then
If( Not isServiceRunning( lstAllServices, arrCheckService(idxServiceShortname), arrCheckService(idxServiceLongname), strExplanation ) ) Then
checkServices = False
Exit Function
End If
End If
Next
checkServices = True
strExplanation = "Services are running"
End Function
' //////////////////////////////////////////////////////////////////////////////
Function checkProcesses( objWMIService, strHost, lstCheckProcesses, BYREF strExplanation )
Dim lstAllProcesses, strCheckProcess
checkProcesses = retvalUnknown ' Default return value
strExplanation = "Unable to check for processes on this machine"
' Get the processes list
If( Not retrieveProcessesList( objWMIService, strHost, lstAllProcesses, strExplanation ) ) Then
Exit Function
End If
' Check processes - only those that are not disabled by a leading '!'
For Each strCheckProcess in lstCheckProcesses
If( strCheckProcess <> "" And Left( strCheckProcess, 1 ) <> "!" ) Then
If( Not isProcessRunning( lstAllProcesses, strCheckProcess, strExplanation ) ) Then
checkProcesses = False
Exit Function
End If
End If
Next
checkProcesses = True
strExplanation = "Processes are running"
End Function
' //////////////////////////////////////////////////////////////////////////////
Function checkPerfCounters( strHost, strAltUserName, strAltPassword, lstCheckPerfCounters, BYREF strExplanation )
Dim objPerf, strPerfPath, numPerfValue
Dim lstAllServices, arrCheckPerfCounter
Dim strEval, bCompareResult
checkPerfCounters = retvalUnknown ' Default return value
strExplanation = "Unable to check for performance counters on this machine"
' Load the ActiveXperts Network Monitor Perfomance object
Set objPerf = CreateObject( "ActiveXperts.NMPerf" )
' Initialze Performance object. Optional parameter: a log file, for debugging purposes
objPerf.Initialize( "" )
If( objPerf.LastError <> 0 ) Then
checkPerfCounters = retvalUnknown
SYSDATA = ""
SYSEXPLANATION = "Failed to initialize performance object."
Exit Function
End If
' Connect. If strAltUserName is empty, the service credentials will be used
objPerf.Connect strHost, strAltUserName, strAltPassword
If( objPerf.LastError <> 0 ) Then
checkPerfCounters = retvalUnknown
SYSDATA = ""
SYSEXPLANATION = "Failed to check performance counters (connect failed)."
objPerf.Shutdown()
Exit Function
End If
' Check performance counters - only those that are not disabled by a leading '!'
For Each arrCheckPerfCounter in lstCheckPerfCounters
If( arrCheckPerfCounter( idxPerfObject ) <> "" And Left( arrCheckPerfCounter( idxPerfObject ), 1 ) <> "!" ) Then
' Build the Peformance path
strPerfPath = objPerf.BuildPath( strHost, arrCheckPerfCounter(idxPerfObject), arrCheckPerfCounter(idxPerfContext), arrCheckPerfCounter(idxPerfItem) )
' Get integer value. If floating point is expected, use GetDoubleValue instead
numPerfValue = objPerf.GetIntegerValue( strPerfPath )
If( objPerf.LastError <> 0 ) Then
checkPerfCounters = retvalUnknown
SYSDATA = ""
SYSEXPLANATION = "Failed to retrieve value for counter [" & strPerfPath & "]"
Exit Function
End If
strEval = numPerfValue & " " & arrCheckPerfCounter(idxPerfCondition)
bCompareResult = Eval( strEval )
If( Not bCompareResult ) Then
SYSDATA = numPerfValue
SYSEXPLANATION = "Path [" & strPerfPath & "], Condition[" & arrCheckPerfCounter(idxPerfItem) & arrCheckPerfCounter(idxPerfCondition) & "] failed, Current Value=[" & numPerfValue & "]"
Exit Function
End If
End If
Next
objPerf.Shutdown()
checkPerfCounters = True
strExplanation = "Performance counters checked"
End Function
' //////////////////////////////////////////////////////////////////////////////
Function retrieveServicesList( objWMIService, strHost, BYREF lstServices, BYREF strSysExplanation )
' Retrieve the list of running services
retrieveServicesList = False
Set lstServices = Nothing
On Error Resume Next
Set lstServices = objWMIService.ExecQuery( "Select * from Win32_Service WHERE state = ""Running""" )
If( Err.Number <> 0 ) Then
strSysExplanation = "Unable to query WMI class on computer [" & strHost & "]"
Exit Function
End If
On Error Goto 0
If( lstServices.Count <= 0 ) Then
strSysExplanation = "Win32_Service class does not exist on computer [" & strHost & "]"
Exit Function
End If
retrieveServicesList = True
End Function
' //////////////////////////////////////////////////////////////////////////////
Function retrieveProcessesList( objWMIService, strHost, BYREF lstProcesses, BYREF strSysExplanation )
' Retrieve the list of running services
retrieveProcessesList = False
Set lstProcesses = Nothing
On Error Resume Next
Set lstProcesses = objWMIService.ExecQuery( "Select * from Win32_Process" )
If( Err.Number <> 0 ) Then
strSysExplanation = "Unable to query WMI class on computer [" & strHost & "]"
Exit Function
End If
On Error Goto 0
If( lstProcesses.Count <= 0 ) Then
strSysExplanation = "Win32_Process class does not exist on computer [" & strHost & "]"
Exit Function
End If
retrieveProcessesList = True
End Function
' //////////////////////////////////////////////////////////////////////////////
Function isServiceRunning( BYREF lstServices, strServiceName, strServiceDescription, BYREF strExplanation )
' Check if a given service exists as running service in the services list
Dim objService
For Each objService in lstServices
' Check If this is the service we are looking for
If( LCase( objService.Name ) = LCase( strServiceName ) ) Then
isServiceRunning = True
Exit Function
End If
Next
' The service was not found, show an error message
strExplanation = "'" & strServiceDescription & "' service is not running"
isServiceRunning = False
End Function
' //////////////////////////////////////////////////////////////////////////////
Function isProcessRunning( BYREF lstProcesses, strProcess, BYREF strExplanation )
' Check if a given process exists as running process in the processes list
Dim objProcess
For Each objProcess in lstProcesses
If( Err.Number <> 0 ) Then
isProcessRunning = retvalUnknown
strExplanation = "Unable to list processes"
Exit Function
End If
' Check If this is the service we are looking for
If( LCase( objProcess.Name ) = LCase( strProcess ) ) Then
isProcessRunning = True
Exit Function
End If
Next
' The process was not found, show an error message
strExplanation = "'" & strProcess & "' process is not running"
isProcessRunning = False
End Function
' //////////////////////////////////////////////////////////////////////////////
Function getCredentials( strHost, strAltCredentials, BYREF strAltLogin, BYREF strAltPassword, BYREF strSysExplanation )
Dim objNMServerCredentials
strAltLogin = ""
strAltPassword = ""
strSysExplanation = ""
getCredentials = False
If( strAltCredentials = "" ) Then
' No alternate credentials specified, so login and password are empty and service credentials will be used
getCredentials = True
Exit Function
End If
Set objNMServerCredentials = CreateObject( "ActiveXperts.NMServerCredentials" )
strAltLogin = objNMServerCredentials.GetLogin( strAltCredentials )
strAltPassword = objNMServerCredentials.GetPassword( strAltCredentials )
If( strAltLogin = "" ) Then
getCredentials = False
strSysExplanation = "No alternate credentials defined for [" & strAltCredentials & "]. In the Manager application, select 'Options' from the 'Tools' menu and select the 'Server Credentials' tab to enter alternate credentials"
Exit Function
End If
getCredentials = True
End Function
' //////////////////////////////////////////////////////////////////////////////
Function wmiConnect( strHost, strAltLogin, strAltPassword, BYREF objWMIService, BYREF strSysExplanation )
Dim objSWbemLocator, colItems
Dim bConnectResult
wmiConnect = False
Set objWMIService = Nothing
If( strAltLogin = "" ) Then
' Connect to remote host on same domain using same security context
On Error Resume Next
Set objWMIService = GetObject( "winmgmts:{impersonationLevel=Impersonate}!\\" & strHost &"\root\cimv2" )
If( Err.Number <> 0 ) Then
bConnectResult = False
Else
bConnectResult = True
End If
On Error Goto 0
Else
' Connect to remote host using different security context and/or different domain
On Error Resume Next
Set objSWbemLocator = CreateObject( "WbemScripting.SWbemLocator" )
Set objWMIService = objSWbemLocator.ConnectServer( strHost, "root\cimv2", strAltLogin, strAltPassword )
If( Err.Number <> 0 ) Then
bConnectResult = False
Else
bConnectResult = True
End If
objWMIService.Security_.ImpersonationLevel = 3
On Error Goto 0
End If
If( Not bConnectResult ) Then
Set objWMIService = Nothing
wmiConnect = False
strSysExplanation = "Failed to connect to '" & strHost & "'. Possible reasons: no WMI installed on the remote server, firewall blocking WMI calls, login failure, or remote server down"
Exit Function
End If
wmiConnect = True
End Function