Sql Broker Availability Monitor Type

Microsoft.ServiceManager.SqlBrokerAvailabilityMonitorType (UnitMonitorType)

A monitor type that looks at Sql Broker status for for Operations Manager functionality.

Element properties:

Member Modules:

Source Code:

<UnitMonitorType ID="Microsoft.ServiceManager.SqlBrokerAvailabilityMonitorType" Accessibility="Internal" RunAs="SM!Microsoft.SystemCenter.ServiceManager.DatabaseWriteActionAccount">

  <MonitorTypeStates>

    <MonitorTypeState ID="SqlBrokerEnabled" NoDetection="false"/>

    <MonitorTypeState ID="SqlBrokerDisabled" NoDetection="false"/>

  </MonitorTypeStates>

  <Configuration/>

  <MonitorImplementation>

    <MemberModules>

      <DataSource ID="Scheduler" TypeID="System!System.Scheduler">

        <Scheduler>

          <SimpleReccuringSchedule>

            <Interval Unit="Seconds">10</Interval>

          </SimpleReccuringSchedule>

          <ExcludeDates/>

        </Scheduler>

      </DataSource>

      <ProbeAction ID="DBQuery" TypeID="Windows!Microsoft.Windows.ScriptPropertyBagProbe">

        <ScriptName>SQLBrokerAvailibilityQuery.vbs</ScriptName>

        <Arguments>$Target/Host/Property[Type="Windows!Microsoft.Windows.Computer"]/PrincipalName$ $Target/Property[Type="SM!Microsoft.SystemCenter.ServiceManager.ManagementServer"]/Version$</Arguments>

        <ScriptBody><Script>

                'Copyright (c) Microsoft Corporation. All rights reserved.



'*************************************************************************

' $ScriptName:  "GetSqlDriverName" $

'

' Purpose:      This script is used to get SQL Driver name.

'

' $File:        GetSqlDriverName.vbs $

'*************************************************************************

Option Explicit



Function GetSqlDriverName

    Dim oAPI1, oReg 

    Const HKEY_LOCAL_MACHINE = &amp;H80000002

    Dim strComputer, strDriverPath, strKeyPath, intValue, strValueName,arrSubKeys,oSubkey,strNewKeyPath,intDotIndex 

    strComputer = "."

    strDriverPath="SQLOLEDB"

    Set oReg=GetObject("winmgmts:\\" &amp; _ 

        strComputer &amp; "\root\default:StdRegProv")

    strKeyPath = "SOFTWARE\ODBC\ODBCINST.INI"

    oReg.EnumKey HKEY_LOCAL_MACHINE, strKeyPath, arrSubKeys



    For Each oSubkey In arrSubKeys

        intValue = InStr(oSubkey, "SQL Server Native Client")

        if intValue &lt;&gt; 0 Then

            strValueName = "Driver" 

            strNewKeyPath = strKeyPath&amp;"\"&amp;oSubkey

            oReg.GetStringValue HKEY_LOCAL_MACHINE, strNewKeyPath, strValueName , strDriverPath

            strDriverPath = Mid (strDriverPath, InStrRev(strDriverPath, "\")+1)

            intDotIndex = InStr(strDriverPath, ".")

            strDriverPath = Left(strDriverPath , intDotIndex-1)

        end if

    Next

    GetSqlDriverName = strDriverPath

End Function

                '*************************************************************************

' ScriptName: SQLBrokerAvailibilityQuery.vbs

'

' Purpose - Populate properties of DW Management Server

'

' Parameters -  0 - discovery id

'               1 - Target managed entity id

'               2 - Host Computer Principal Name

'               3 - Version

'*************************************************************************  

SetLocale("en-us")

Dim sourceId,managedEntityId,oAPI,strKeyPath,principalName, version, oBag



Set oAPI = CreateObject("MOM.ScriptAPI")

principalName = WScript.Arguments(0)

version = WScript.Arguments(1)



'Call oAPI.LogScriptEvent("SQLBrokerAvailibilityQuery.vbs", 3000, 4, "Starting query of SQL Broker availibility")

Set oBag = oAPI.CreatePropertyBag()



Dim oReg, iResult

Set oReg = GetObject("winmgmts:\\" &amp; principalName &amp; "\root\default:StdRegProv")

If Err &lt;&gt; 0 Then

    Call oAPI.LogScriptEvent("SQLBrokerAvailibilityQuery.vbs", 3000, 4, "Unable to bind to WMI registry provider on " &amp; principalName &amp; ".  Terminating Script.")

        Call oAPI.Return(oBag)

    WScript.Quit

End if

Const HKLM = &amp;H80000002



If version = "2010" or version = "2012" then

    Dim strKeyRoot, strDatabaseServerName, strDatabaseName, ibrokerEnabled

    ibrokerEnabled = 0

    strKeyRoot = "SOFTWARE\Microsoft\System Center\2010\Common\Database"

    iResult = oReg.GetStringValue(HKLM, strKeyRoot, "DatabaseServerName", strDatabaseServerName)

    If iResult = 0 then

        iResult = oReg.GetStringValue(HKLM, strKeyRoot, "DatabaseName", strDatabaseName)		

        If iResult = 0 then

        'Call oAPI.LogScriptEvent("SQLBrokerAvailibilityQuery.vbs", 3000, 4, "Recovered Registry keys from " &amp; principalName )

            Dim  connectionString, cnADOConnection, oResults,oQuery, strDriverName

            strDriverName = GetSqlDriverName

            connectionString = "Provider=" &amp; strDriverName &amp; ";Server=" &amp; strDatabaseServerName &amp; ";Integrated Security=SSPI;Initial Catalog=" &amp; strDatabaseName

            Set cnADOConnection = CreateObject("ADODB.Connection")

            cnADOConnection.Open connectionString

            cnADOConnection.CommandTimeout = 300

            oQuery = "SELECT cast(is_broker_enabled as int) As BrokerEnabled FROM sys.databases WHERE name = DB_Name()"

            oResults = CreateObject("ADODB.Recordset")

            Set oResults = cnADOConnection.Execute(oQuery)

                    

            If oResults.State &lt;&gt; 0 Then

                Do Until oResults.EOF

                    iBrokerEnabled = oResults.Fields("BrokerEnabled")

                    oResults.MoveNext

                Loop

            End If

            If oResults.State &lt;&gt; 0 Then

                oResults.Close

            End If

            cnADOConnection.Close

                    

        End If

    End If

    If iBrokerEnabled = 1 then

        Call oBag.AddValue("BrokerEnabled", 1)

    Else

        Call oBag.AddValue("BrokerEnabled", 0)

    End if

End If



Call oAPI.Return(oBag)

'Call oAPI.LogScriptEvent("SQLBrokerAvailibilityQuery.vbs", 3000, 4, "Finishing SQLBroker Script.")

WScript.Quit





                </Script></ScriptBody>

        <TimeoutSeconds>500</TimeoutSeconds>

      </ProbeAction>

      <ConditionDetection ID="CDEnabled" TypeID="System!System.ExpressionFilter">

        <Expression>

          <SimpleExpression>

            <ValueExpression>

              <XPathQuery Type="Integer">Property[@Name='BrokerEnabled']</XPathQuery>

            </ValueExpression>

            <Operator>Equal</Operator>

            <ValueExpression>

              <Value Type="Integer">1</Value>

            </ValueExpression>

          </SimpleExpression>

        </Expression>

      </ConditionDetection>

      <ConditionDetection ID="CDDisabled" TypeID="System!System.ExpressionFilter">

        <Expression>

          <SimpleExpression>

            <ValueExpression>

              <XPathQuery Type="Integer">Property[@Name='BrokerEnabled']</XPathQuery>

            </ValueExpression>

            <Operator>Equal</Operator>

            <ValueExpression>

              <Value Type="Integer">0</Value>

            </ValueExpression>

          </SimpleExpression>

        </Expression>

      </ConditionDetection>

    </MemberModules>

    <RegularDetections>

      <RegularDetection MonitorTypeStateID="SqlBrokerEnabled">

        <Node ID="CDEnabled">

          <Node ID="DBQuery">

            <Node ID="Scheduler"/>

          </Node>

        </Node>

      </RegularDetection>

      <RegularDetection MonitorTypeStateID="SqlBrokerDisabled">

        <Node ID="CDDisabled">

          <Node ID="DBQuery">

            <Node ID="Scheduler"/>

          </Node>

        </Node>

      </RegularDetection>

    </RegularDetections>

  </MonitorImplementation>

</UnitMonitorType>