'*******************************************************************************
' Script: _AD Active Number of MOM Consoles
' Purpose: * Script will check the Active Number of MOM Consoles.
'          * 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. 
'          * Creates a MOM Performance Object.
'          * Other rules then can Alert on number of consoles open.
'          * Can MOM Reporter report on console useage over time.
'          * Script additioanlly directly creates MOM error Alerts
' Parameters: 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
' Usage:  Bind run of script to the Microsoft Operations Manager 2005 Databases
'         Computer Group with a 10 minute provider. The Action Account will need
'         Select permissions on the sysprocesses table in the master database.  
' Props: (March 17 2006 entry) http://fiberman.spaces.live.com/?_c11_Blog <wrap>
'            Part_BlogPart=blogview&_c=BlogPart&partqs=amonth%3d3%26ayear%3d2006
'        and http://blogs.technet.com/tmintner/
'
Option Explicit
Const ALERT_WARNING = 30
CONST ALERT_ERRROR = 40 
Const SCRIPT_NAME = "_AD Active Number of MOM Consoles"
Const CONNECT_ERROR = -2
Sub Main()
   Dim oParams, sInstances, sDBName, sQuery, aInstances
   Dim i, sMessage, inumberofConsoles, objCreatePerf
   If ScriptContext.IsEvent() Then
      Set oParams = ScriptContext.Parameters
      sInstances = oParams.Get("CheckInstances")
      sDBName = oParams.Get("DatabaseName")
'     sQuery is the SQL query that checks the active number of MOM consoles
      sQuery = "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"
      Set oParams = Nothing
      If (sInstances = "") Or (sDBName = "") Or (sQuery = "") 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
'              Create Perf Obj based on inumberofconsoles
               Set objCreatePerf = ScriptContext.CreatePerfData()
               objCreatePerf.ObjectName   = SCRIPT_NAME
               objCreatePerf.CounterName  = sInstances
               objCreatePerf.InstanceName = sDBName
               objCreatePerf.Value        = inumberofconsoles
               ScriptContext.Submit(objCreatePerf)
            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
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, sCheck ' Dim sCheck ?
   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
' 
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)
End Function
'*******************************************************************************