Quick and Dirty: a handy SQL Query PS Rule

Very often, I get the request to monitor a remote SQL Server as a Synthetic Transaction and I normally end up creating something custom, even though SCOM has an OLE DB Template, which can come in handy, but it can create a bit of overhead from a class and objects perspective. The approach here is to create a disabled rule, target to a  common and existing class and enable it whenever necessary on the computers selected to be the watcher nodes. Doesn’t look as neat as an entry in the console, but, hey,do you really need to know how sausages are made to enjoy the hotdog?

Let’s get down to it. One thing that is often forgotten is authentication: when running the query, integrated auth is usually a good choice and having a Run as profile to assign an account for the query will sure be need.

1.For that, you will need a Secure Reference and its respective display string.

<TypeDefinitions>

<SecureReferences>
      <SecureReference ID=”ABC.Application.RunAsProfileSQLQueries” Accessibility=”Public” Context=”Windows!Microsoft.Windows.Computer” />
</SecureReferences>

</TypeDefinitions>

<DisplayStrings>

<DisplayString ElementID=”ABC.Application.RunAsProfileSQLQueries”>
  <Name>ABC Application RunAsProfile for SQL Queries</Name>
</DisplayString>
</DisplayStrings>

2.Since this is a custom rule, let’s start by creating a Scripting Probe:

  <TypeDefinitions>

<ModuleTypes>

      <ProbeActionModuleType ID=”ABC.Application.Probe.GenericSQLQueryPS” Accessibility=”Public” RunAs=”ABC.Application.RunAsProfileSQLQueries” Batching=”false” PassThrough=”false”>
        <Configuration>
          <xsd:element minOccurs=”1″ name=”SQLInstance” type=”xsd:string” />
          <xsd:element minOccurs=”1″ name=”Database” type=”xsd:string” />
          <xsd:element minOccurs=”1″ name=”strQuery” type=”xsd:string” />
        </Configuration>
        <ModuleImplementation Isolation=”Any”>
          <Composite>
            <MemberModules>
              <ProbeAction ID=”Probe” TypeID=”Windows!Microsoft.Windows.PowerShellPropertyBagTriggerOnlyProbe”>
                <ScriptName>PSSQLProbe.ps1</ScriptName>
                <ScriptBody><![CDATA[param([string]$SQLInstance,[string]$strQuery,[string]$Database)

$oAPI = New-Object -ComObject “MOM.ScriptAPI”
$oBag = $oAPI.CreatePropertyBag()

$strServer = “$SQLInstance”
$SQLQuery=”$strQuery”
$oAPI.LogScriptEvent(“PSSQLProbe.ps1″, 555,0,”Preparing query against $SQLInstance on Database $Database with query: $SQLQuery .”)
$ADOCon = New-Object -ComObject “ADODB.Connection”
$oResults = New-Object -ComObject “ADODB.Recordset”
$adOpenStatic = 3
$adLockOptimistic = 3
$ADOCon.Provider = “sqloledb”
$ADOCon.ConnectionTimeout = 60
$nowInUTC = (Get-Date).ToUniversalTime()
$conString = “Server=$strServer;Database=$Database;Integrated Security=SSPI”
try {
    $ADOCon.Open($conString)
}
catch {
    $oAPI.LogScriptEvent(“PSSQLProbe.ps1″, 555,1,”Error connecting. Constring: $conString Error: $error”)
}
if ($ADOCon.State -ne 0)
{
   
    $time=measure-command {
        try {    
           
            $oResults.Open($SQLQuery, $ADOCon, $adOpenStatic, $adLockOptimistic)
            $oAPI.LogScriptEvent(“PSSQLProbe.ps1″, 555,0,”Successfully executed query against $SQLInstance on Database $Database”)
            If (!$oResults.EOF)
            {
                $oBag.AddValue(‘RecordCount’,$oResults.RecordCount)
                $oBag.AddValue(‘TransactionTimeMS’, $time.Milliseconds)

            }
            else
            {
                $oBag.AddValue(‘RecordCount’,0)
                $oBag.AddValue(‘TransactionTimeMS’, $time.Milliseconds)
            }
            $oBag
        }
        catch
        {
           #write-host “Error running query”
           $oAPI.LogScriptEvent(“PSSQLProbe.ps1″, 555,1,”Error executing query against $SQLInstance on Database $Database with query $SQLQuery”)
        }
    }
    $oResults.Close()
    $ADOCon.Close()
}]]></ScriptBody>
                <Parameters>
                  <Parameter>
                    <Name>SQLInstance</Name>
                    <Value>$Config/SQLInstance$</Value>
                  </Parameter>
                  <Parameter>
                    <Name>strQuery</Name>
                    <Value>$Config/strQuery$</Value>
                  </Parameter>
                  <Parameter>
                    <Name>Database</Name>
                    <Value>$Config/Database$</Value>
                  </Parameter>
                </Parameters>
                <TimeoutSeconds>60</TimeoutSeconds>
              </ProbeAction>
            </MemberModules>
            <Composition>
              <Node ID=”Probe” />
            </Composition>
          </Composite>
        </ModuleImplementation>
        <OutputType>System!System.PropertyBagData</OutputType>
        <TriggerOnly>true</TriggerOnly>
      </ProbeActionModuleType>

  </ModuleTypes>
</TypeDefinitions>

<DisplayStrings>

<DisplayString ElementID=”ABC.Application.Probe.GenericSQLQueryPS”>
  <Name>ABC Application Probe Generic SQL Query PS</Name>
</DisplayString>

</DisplayStrings>

The probe has the 3 basic parameters: SQL Instance or server, database and SQL Query.

3. With the probe ready, you need a data source module:

<TypeDefinitions>

<ModuleTypes>

<DataSourceModuleType ID=”ABC.Application.DataSource.GenericSQLQueryPS” Accessibility=”Public” RunAs=”ABC.Application.RunAsProfileSQLQueries” Batching=”false”>
  <Configuration>
    <xsd:element minOccurs=”1″ name=”IntervalSeconds” type=”xsd:integer” />
    <xsd:element minOccurs=”0″ name=”SyncTime” type=”xsd:string” />
    <xsd:element minOccurs=”1″ name=”SQLInstance” type=”xsd:string” />
    <xsd:element minOccurs=”1″ name=”Database” type=”xsd:string” />
    <xsd:element minOccurs=”1″ name=”strQuery” type=”xsd:string” />
  </Configuration>
  <OverrideableParameters>
    <OverrideableParameter ID=”SQLInstance” Selector=”$Config/SQLInstance$” ParameterType=”string” />
    <OverrideableParameter ID=”Database” Selector=”$Config/Database$” ParameterType=”string” />
    <OverrideableParameter ID=”strQuery” Selector=”$Config/strQuery$” ParameterType=”string” />
    <OverrideableParameter ID=”IntervalSeconds” Selector=”$Config/IntervalSeconds$” ParameterType=”int” />
    <OverrideableParameter ID=”SyncTime” Selector=”$Config/SyncTime$” ParameterType=”string” />
  </OverrideableParameters>
  <ModuleImplementation Isolation=”Any”>
    <Composite>
      <MemberModules>
        <DataSource ID=”scheduler” TypeID=”System!System.SimpleScheduler”>
          <IntervalSeconds>$Config/IntervalSeconds$</IntervalSeconds>
          <SyncTime />
        </DataSource>
        <ProbeAction ID=”SQLProbe” TypeID=”ABC.Application.Probe.GenericSQLQueryPS”>
          <SQLInstance>$Config/SQLInstance$</SQLInstance>
          <Database>$Config/Database$</Database>
          <strQuery>$Config/strQuery$</strQuery>
        </ProbeAction>
      </MemberModules>
      <Composition>
        <Node ID=”SQLProbe”>
          <Node ID=”scheduler” />
        </Node>
      </Composition>
    </Composite>
  </ModuleImplementation>
  <OutputType>System!System.PropertyBagData</OutputType>
</DataSourceModuleType>

  </ModuleTypes>
</TypeDefinitions>

<DisplayStrings>

<DisplayString ElementID=”ABC.Application.DataSource.GenericSQLQueryPS”>
  <Name>ABC Application DataSource Generic SQL Query PS</Name>
</DisplayString>
4. Now, the last part: the rule itself, which leverages the Datasource directly and adds a condition detection and an alert action:

<Monitoring>

<Rules>

<Rule ID=”ABC.Application.Rule.SQLQuery.Test” Enabled=”false” Target=”Windows!Microsoft.Windows.Server.OperatingSystem” ConfirmDelivery=”true” Remotable=”true” Priority=”Normal” DiscardLevel=”100″>
  <Category>Custom</Category>
  <DataSources>
    <DataSource ID=”DS” RunAs=”ABC.Application.RunAsProfileSQLQueries” TypeID=”ABC.Application.DataSource.GenericSQLQueryPS”>
      <IntervalSeconds>300</IntervalSeconds>
      <SQLInstance>your_SQL_Server_Instance</SQLInstance>
      <Database>Database</Database>
      <strQuery>SELECT ETC FROM ETC</strQuery>
    </DataSource>
  </DataSources>
  <ConditionDetection ID=”Filter” TypeID=”System!System.ExpressionFilter”>
    <Expression>
      <And>
        <Expression>
          <SimpleExpression>
            <ValueExpression>
              <XPathQuery Type=”String”>Property[@Name=’RecordCount’]</XPathQuery>
            </ValueExpression>
            <Operator>Greater</Operator>
            <ValueExpression>
              <Value Type=”String”>0</Value>
            </ValueExpression>
          </SimpleExpression>
        </Expression>
        <Expression>
          <SimpleExpression>
            <ValueExpression>
              <XPathQuery Type=”String”>Property[@Name=’TransactionTimeMS’]</XPathQuery>
            </ValueExpression>
            <Operator>Less</Operator>
            <ValueExpression>
              <Value Type=”String”>5000</Value>
            </ValueExpression>
          </SimpleExpression>
        </Expression>
      </And>
    </Expression>
  </ConditionDetection>
  <WriteActions>
    <WriteAction ID=”Alert” TypeID=”Health!System.Health.GenerateAlert”>
      <Priority>1</Priority>
      <Severity>2</Severity>
      <AlertMessageId>$MPElement[Name=”AlertMessageID50555aef48434eeea982400717e04b15″]$</AlertMessageId>
      <Suppression>
        <SuppressionValue>$Target/Host/Property[Type=”Windows!Microsoft.Windows.Computer”]/NetbiosComputerName$</SuppressionValue>
      </Suppression>
    </WriteAction>
  </WriteActions>
</Rule>

</Rules>

</Monitoring>

<Presentation>
  <StringResources>
    <StringResource ID=”AlertMessageID50555aef48434eeea982400717e04b15″ />

  </StringResources>

</Presentation>
<DisplayStrings>

<DisplayString ElementID=”AlertMessageID50555aef48434eeea982400717e04b15″>
          <Name>APP SQL Query Error – SQL server unavailable</Name>
          <Description>”No records were return when querying the Server or over time</Description>
        </DisplayString>

</DisplayStrings>

 

And there you go. All you have to do is put all that into Visual Studio Authoring Extensions, mix, stir and poof! You got your MP.

Make sure you read my previous VSAE related posts.

If you just want to get the MP, get it here! Don’t forget to create more rules, for different queries (or create overrides) and to override the rule to enable it to the computers you want to be the watches nodes. I also strongly recommend you to seal the management pack, just so you can update it freely and still keep your overrides.

Hope this helps!