Script to Alert on Active Number of MOM Operator Consoles

Article
11/22/2005

MOM 2005 has a support limit of 15 active MOM Operator Consoles, however there is no built in way to detect and/or alert on the number of consoles that are in use. I have written a little script that does just that. It is not the prettiest script in the world but it functions. :)

Check it out here:

Option Explicit

'*****************************************************************************************************************************************************************
'Script: Active Number of MOM Consoles
'This script will check the Active Number of MOM Consoles and alert if the active number goes beyond the Warning or Error Threshold levels
'Parameters:
'    WarningThreshold - The number of consoles that will generate a Warning Alert
'    ErrorThreshold - The number of consoles that will generate an Error Alert
'    CheckInstances - The SQL Server Instance that is running the Onepoint database
'    DatabaseName - The name of the database to run the query against. This should be set to master
'    Query - The SQL query that checks the active number of MOM consoles
'    The query should be:
'    SELECT program_name, count(*) FROM Master..sysprocesses WHERE ecid=0 and program_name='Microsoft Operations Manager - DAS Operations Console' GROUP BY program_name ORDER BY count(*) desc
'    InformationalAlert - Turns on debugging information (Have to uncomment lines below)
' Usage: Create a new MOM Script and apply this script to the Microsoft Operations Manager 2005 Databases Computer Group. Create a 10 minute provider. Create an Alert or Respond to Event
'   Rule and use the 10 minute provider. Under Event Rule responses, launch a script and choose this script
' The Action Account will need Select permissions on the sysprocesses table in the master database.
'*****************************************************************************************************************************************************************

' Alert Constants
CONST ALERT_INFORMATION = 20
CONST ALERT_WARNING = 30
CONST ALERT_ERRROR = 40
CONST ALERT_CRITICALERROR = 50
CONST ALERT_SERVICEUNAVAILABLE = 70

'Other constants
Const SCRIPT_NAME = "Active Number of MOM Consoles"
Const CONNECT_ERROR = -2

Sub Main()

Dim oParams, blnInformationAlert, sInstances, sDBName, sQuery,aInstances
Dim i, iSuccess, sMessage,inumberofConsoles, ierror, iwarning

If ScriptContext.IsEvent() Then
    Set oParams = ScriptContext.Parameters
    iWarning = Oparams.Get("WarningThreshold")
    iError = oParams.Get("ErrorThreshold")
    sInstances = oParams.Get("CheckInstances")
    sDBName = oParams.Get("DatabaseName")
    sQuery = oParams.Get("Query")
    blnInformationAlert = OParams.Get("InformationalAlert")
    Set oParams = Nothing
    iError = CINT(iError)
    iWarning = CINT(iWarning)

    If (sInstances = "") Or (sDBName = "") Or (sQuery = "") Then
      if blnInformationAlert Then AlertCreate SCRIPT_NAME,ALERT_WARNING,"The '" & SCRIPT_NAME & "' script ran but had incomplete parameter information. " &_
        "Processing cannot continue."
    Else
   aInstances = Split(sInstances,",")
   For i = 0 To UBound(aInstances)
   inumberofconsoles = ConnectToSQLInstance(Trim(aInstances(i)), sDBName, sQuery)
   If inumberofconsoles < 0 Then
            AlertCreate SCRIPT_NAME,ALERT_ERRROR,"Failed to connect to " & UCase(aInstances(i)) & " or failed to return a result from the query """ &_
        sQuery & """ against the database " & sDBName & "."
          Else
            If (inumberofconsoles >= iwarning) AND (inumberofconsoles < iError) Then AlertCreate SCRIPT_NAME, ALERT_WARNING,"The number of Of consoles exceeds or matches the warning theshold. " & inumberofconsoles & " active consoles detected"
     If inumberofconsoles >= iError Then AlertCreate SCRIPT_NAME,ALERT_ERRROR,"The number of Of consoles exceeds or matches the error theshold. " & inumberofconsoles & " active consoles detected"
   End if
   Next

    End If
Else
    sMessage = "The script '" & SCRIPT_NAME & "' can only be executed by an event rule."
    AlertCreate SCRIPT_NAME, ALERT_WARNING, sMessage
End If
' To Troubleshoot uncomment the lines below

' if blnInformationAlert Then
'
'   sMessage = "The number of Active Consoles is " & inumberofConsoles & "." &_
'     "The Error threshold is " & iError & "." &_
'     "The Warning threshold is " & iWarning & "."
'   AlertCreate Script_Name, ALERT_INFORMATION, sMessage
' End if

End Sub

'*****************************************************************************************
' Function ConnectToSQLIntance
' Paramaters:
'    sInstance - The SQL instance
'    sDatabase - The database name
'    sQuery - The SQL query
' Returns the Active Number of Consoles or CONNECT_ERROR if unable to connect to database
'*****************************************************************************************

Function ConnectToSQLInstance(sInstance, sDatabase, sQuery)

Dim oSQLServer, oDatabase, oResults, iconsoles

On Error Resume Next

Set oSQLServer = CreateObject("SQLDMO.SQLServer")
oSQLServer.LoginSecure = 1
oSQLServer.Connect sInstance
If Err.number = 0 Then
    Set oDatabase = oSQLServer.Databases.Item(sDatabase)
    If Err.number = 0 Then

      Set oResults = oDatabase.ExecuteWithResults(sQuery)

      If Err.number = 0 Then
        sCheck = OResults.GetColumnString(1,1)
        If Err.Number = 0 Then
         If oResults.GetColumnString(1,1) = "" Then
           ConnectToSQLInstance = 0

        Else
        iconsoles = Cint(oResults.GetColumnString(1,2))
          if iconsoles = "" then
     ConnectToSQLInstance = 0
          else
             ConnectToSQLInstance = iconsoles
          End if
        End If
       Else
         ConnectionToSQLInstance = 0
       End if
       Else
        ConnectToSQLInstance = CONNECT_ERROR

      End If
    Else
      ConnectToSQLInstance = CONNECT_ERROR
    End If
Else
    ConnectToSQLInstance = CONNECT_ERROR
End If

On Error Goto 0
End Function

'*******************************************************************************************
'Function AlertCreate
'Paramaters:
'    sName - The name of the Alert
'    iLevel - The Error level of the Alert
'    sDecription - Decription of the Alert
' Creates the alert and returns 0 if success and -1 if it fails to create the alert
'*******************************************************************************************

Function AlertCreate(sName,iLevel,sDescription)
Dim objCreatedAlert
Err.Clear
Set objCreatedAlert = ScriptContext.CreateAlert()
objCreatedAlert.Name = sName
objCreatedAlert.AlertLevel = iLevel
objCreatedAlert.Owner = "[unassigned]"
objCreatedAlert.ResolutionState = 0
objCreatedAlert.Description = sDescription
ScriptContext.Submit(objCreatedAlert)
If Err.number= 0 Then
AlertCreate = 0
Else
AlertCreate = -1
End if
End Function

Share via

Script to Alert on Active Number of MOM Operator Consoles

Additional resources