Contact Info

Crumbtrail

ActiveXperts.com » Administration » VBScript » Network Monitor » SQL Server

MsSqlServer.vbs - Microsoft SQL Server monitoring using ActiveXperts Network Monitor

ActiveXperts Network Monitor ships with a powerful set of pre-defined checks. Each individual check has a static number of configuration items. To monitor other items, or to combine monitoring items, you can make use of custom VBScript checks.

Most of the built-in checks have a VBScript equivalent, implemented as a Function in a VBScript (.vbs) file. Out-of-the-box, each VBScript function monitors the same items as the built-in check. Feel free to modify a function. The VBScript check can be customized by editing the VBScript function.

To add a new VBScript-based Microsoft SQL Server monitoring check, do the following:

To customize the above monitoring check, click on the 'Edit button' next to the 'File selection box'. Notepad will be launched. You can now make changes to the VBScript function(s).

Screenshot of a VBScript MsSqlServer check

MsSqlServer.vbs script source code

' ///////////////////////////////////////////////////////////////////////////////
' // ActiveXperts Network Monitor  - VBScript based checks
' //  ActiveXperts Software B.V.
' //
' // For more information about ActiveXperts Network Monitor and VBScript, please
' // visit the online ActiveXperts Network Monitor VBScript Guidelines at:
' //    https://www.activexperts.com/support/network-monitor/online/vbscript/
' ///////////////////////////////////////////////////////////////////////////////

Option Explicit
Const  retvalUnknown = 1
Dim    SYSDATA, SYSEXPLANATION  ' Used by Network Monitor, don't change the names


' ///////////////////////////////////////////////////////////////////////////////
' // To test a function outside Network Monitor (e.g. using CSCRIPT from the
' // command line), remove the comment character (') in the following 5 lines:
' Dim bResult
' bResult =  CheckSqlServer( "localhost", "" )
' WScript.Echo "Return value: [" & bResult & "]"
' WScript.Echo "SYSDATA: [" & SYSDATA & "]"
' WScript.Echo "SYSEXPLANATION: [" & SYSEXPLANATION & "]"
' //////////////////////////////////////////////////////////////////////////////

Function CheckSqlServer( strComputer, strCredentials )

' Description: 
'     Checks if MS SQL Server is running; it checks the SQL services ans SQL processes
' Parameters:
'     1) strComputer As String - Hostname or IP address of the server you want to check
'     2) strCredentials 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)
' Usage:
'     CheckSqlServer( "", "" )
' Sample:
'     CheckSqlServer( "localhost", "" )

    Dim objWMIService, lstServices, numResult

    CheckSqlServer      = retvalUnknown  ' Default return value
    SYSDATA             = ""             ' Not used by this function
    SYSEXPLANATION      = ""             ' Set initial value

    ' Get thew WMI object
    If( Not getWMIObject( strComputer, strCredentials, objWMIService, SYSEXPLANATION ) ) Then
        Exit Function
    End If
    
    numResult = CheckSqlServices( objWMIService, strComputer, SYSEXPLANATION )
    If( numResult <> True ) Then
        CheckSqlServer  = numResult
        Exit Function      
    End If
    
    numResult = CheckSqlProcesses( objWMIService, strComputer, SYSEXPLANATION )
    If( numResult <> True ) Then
        CheckSqlServer  = numResult
        Exit Function      
    End If    
    
    CheckSqlServer = True
    SYSEXPLANATION      = "All conditions are met"    
    
End Function


' //////////////////////////////////////////////////////////////////////////////
' //
' // Private Functions
' //   NOTE: Private functions are used by the above functions, and will not
' //         be called directly by the ActiveXperts Network Monitor Service.
' //         Private function names start with a lower case character and will
' //         not be listed in the Network Monitor's function browser.
' //
' //////////////////////////////////////////////////////////////////////////////

Function CheckSqlServices( objWMIService, strComputer, BYREF strSysExplanation )

    Dim result, lstServices	
    
    CheckSqlServices  = retvalUnknown
    strSysExplanation = ""    
    
     ' Get the services list
    If( Not retrieveServicesList( objWMIService, strComputer, lstServices, SYSEXPLANATION ) ) Then
        Exit Function
    End If     
    
    result = isServiceRunning( lstServices, "MSSQLSERVER", "SQL Server (MSSQLSERVER)", strSysExplanation )					
    If( result = True ) Then
        result       = isServiceRunning( lstServices, "MSSQLServerOLAPService", "SQL Server Analysis Services (MSSQLSERVER)", strSysExplanation )		
    End If	
    If( result = True ) Then
        result       = isServiceRunning( lstServices, "ReportServer", "SQL Server Reporting Services (MSSQLSERVER)", strSysExplanation )		
    End If 
    If( result = True ) Then
        result       = isServiceRunning( lstServices, "SQLBrowser", "SQL Server Browser", strSysExplanation )		
    End If
    If( result = True ) Then
        result       = isServiceRunning( lstServices, "SQLSERVERAGENT", "SQL Server Agent (MSSQLSERVER)", strSysExplanation )		
    End If
    If( result = True ) Then
        result       = isServiceRunning( lstServices, "SQLWriter", "SQL Server VSS Writer", strSysExplanation )		
    End If   
	
    CheckSqlServices = result
    
End Function


' //////////////////////////////////////////////////////////////////////////////

Function CheckSqlProcesses( objWMIService, strComputer, BYREF strSysExplanation )

    Dim result	
    
    CheckSqlProcesses = retvalUnknown
    strSysExplanation = ""    
	
    result = isProcessRunning( objWMIService, strComputer, "msmdsrv.exe", strSysExplanation )					
    If( result = True ) Then
        result = isProcessRunning( objWMIService, strComputer, "ReportingServicesService.exe", strSysExplanation )				
    End If	
    If( result = True ) Then
        result = isProcessRunning( objWMIService, strComputer, "sqlagent.exe", strSysExplanation )				
    End If	    
    If( result = True ) Then
        result = isProcessRunning( objWMIService, strComputer, "sqlbrowser.exe", strSysExplanation )				
    End If	    
    If( result = True ) Then
        result = isProcessRunning( objWMIService, strComputer, "sqlservr.exe", strSysExplanation )				
    End If	    
    If( result = True ) Then
        result = isProcessRunning( objWMIService, strComputer, "sqlwriter.exe", strSysExplanation )				
    End If	    
    	
    CheckSqlProcesses = result
    
End Function


' //////////////////////////////////////////////////////////////////////////////

Function retrieveServicesList( objWMIService, strComputer, BYREF lstServices, BYREF strSysExplanation )
' Retrieve the list of running services	

    retrieveServicesList  = False
    Set lstServices       = Nothing

    Set lstServices       = objWMIService.ExecQuery( "Select * from Win32_Service WHERE state = ""Running""" )
    If( Err.Number <> 0 ) Then
        strSysExplanation = "Unable to query WMI on computer [" & strComputer & "]"
        Exit Function
    End If
    If( lstServices.Count <= 0  ) Then
        strSysExplanation = "Win32_Service class does not exist on computer [" & strComputer & "]"
        Exit Function
    End If 

On Error Goto 0

    retrieveServicesList  = True    

End Function


' //////////////////////////////////////////////////////////////////////////////

Function isServiceRunning( BYREF lstServices, strServiceName, strServiceDescription, BYREF strSysExplanation )
' Check if a given service exists as running service in the services list
    
    Dim objService

    isServiceRunning = False  ' Default return value
				
    For Each objService in lstServices			
		
        If( Err.Number <> 0 ) Then
            isServiceRunning    = retvalUnknown
            strSysExplanation   = "Unable to list services" 
            Exit Function
        End If	 
	   
        ' 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
    strSysExplanation           = "'" & strServiceDescription & "' service is not running"    
    isServiceRunning            = False

End Function


' //////////////////////////////////////////////////////////////////////////////

Function isProcessRunning( objWMIService, strComputer, strProcess, BYREF strSysExplanation )

    Dim objProcess, collProcesses

    isProcessRunning            = retvalUnknown  ' Default return value

On Error Resume Next

    set collProcesses          = objWMIService.ExecQuery( "select * from Win32_Process" ) 
    If( Err.Number <> 0 ) Then
        strSysData         = ""
        strSysExplanation  = "Unable to query WMI on computer [" & strComputer & "]"
        Exit Function
    End If
    If( collProcesses.Count <= 0  ) Then
        strSysData         = ""
        strSysExplanation  = "Win32_Process class does not exist on computer [" & strComputer & "]"
        Exit Function
    End If

On Error Goto 0

    For Each objProcess in collProcesses
        If( Err.Number <> 0 ) Then
            isProcessRunning    = retvalUnknown
            strSysExplanation  = "Unable to list processes on computer [" & strComputer & "]"
            Exit Function 
        End If
	If UCase( objProcess.Name ) = UCase( strProcess ) Then
            isProcessRunning    = True
    	    strSysExplanation  = "Process [" & strProcess & "] is running on computer [" & strComputer & "]"
            Exit Function
        End If
    Next

    isProcessRunning            = False
    strSysExplanation          = "Process [" & strProcess & "] is not running on computer [" & strComputer & "]"

End Function


' //////////////////////////////////////////////////////////////////////////////

Function getWMIObject( strComputer, strCredentials, BYREF objWMIService, BYREF strSysExplanation )	

On Error Resume Next

    Dim objNMServerCredentials, objSWbemLocator, colItems
    Dim strUsername, strPassword

    getWMIObject              = False

    Set objWMIService         = Nothing
    
    If( strCredentials = "" ) Then	
        ' Connect to remote host on same domain using same security context
        Set objWMIService     = GetObject( "winmgmts:{impersonationLevel=Impersonate}!\\" & strComputer &"\root\cimv2" )
    Else
        Set objNMServerCredentials = CreateObject( "ActiveXperts.NMServerCredentials" )

        strUsername           = objNMServerCredentials.GetLogin( strCredentials )
        strPassword           = objNMServerCredentials.GetPassword( strCredentials )

        If( strUsername = "" ) Then
            getWMIObject      = False
            strSysExplanation = "No alternate credentials defined for [" & strCredentials & "]. In the Manager application, select 'Options' from the 'Tools' menu and select the 'Server Credentials' tab to enter alternate credentials"
            Exit Function
        End If
	
        ' Connect to remote host using different security context and/or different domain 
        Set objSWbemLocator   = CreateObject( "WbemScripting.SWbemLocator" )
        Set objWMIService     = objSWbemLocator.ConnectServer( strComputer, "root\cimv2", strUsername, strPassword )

        If( Err.Number <> 0 ) Then
            objWMIService     = Nothing
            getWMIObject      = False
            strSysExplanation = "Unable to access [" & strComputer & "]. Possible reasons: WMI not running on the remote server, Windows firewall is blocking WMI calls, insufficient rights, or remote server down"
            Exit Function
        End If

        objWMIService.Security_.ImpersonationLevel = 3

    End If
	
    If( Err.Number <> 0 ) Then
        objWMIService         = Nothing
        getWMIObject          = False
        strSysExplanation     = "Unable to access '" & strComputer & "'. Possible reasons: no WMI installed on the remote server, no rights to access remote WMI service, or remote server down"
        Exit Function
    End If    

    getWMIObject              = True 

End Function