Monitor.TimedScript.PowerShell.SQLQuery.mpx

<ManagementPackFragment SchemaVersion="2.0" xmlns:xsd="http://www.w3.org/2001/XMLSchema">
<!--
%%
Description:
  A MONITOR which runs a timed PowerShell script as the DataSource and queries a SQL server for output to drive monitor state and ALERT.
  The script calls a SQL query against a database and the output submitted by the script in a propertybag.
  CompanyID - is a short abbreviation for your company with NO SPACES OR SPECIAL CHARACTERS ALLOWED
  AppName - is a short name for your app with NO SPACES OR SPECIAL CHARACTERS ALLOWED  
  ClassID - is the targeted class such as your custom class or Windows!Microsoft.Windows.Server.OperatingSystem  
  UniqueID - Is a unique short description of the monitor purpose (NO SPACES OR SPECIAL CHARACTERS ALLOWED) such as "SQLQueryServerName"
  IntervalSeconds - How often you wish to run the query
  SQLServer - is the SQL server name and any named instance.  Such as "servername.domain.com" or "servername.domain.com\INSTANCENAME"
  SQLDBName - is the SQL database name you wish to query in the above instance
  SQLQuery - is the SQL query you wish to run such as "SELECT COUNT(*) FROM Alerts"
  RowCountThreshold - the threshold to change monitor state.  By default any results great or equal to will be considered unhealthy
  
Version: 1.8
LastModified: 14-Jul-2022
%%

In this fragment you need to replace:
  ##CompanyID##
  ##AppName##
  ##ClassID##
  ##UniqueID## 
  ##IntervalSeconds##  
  ##SQLServer##
  ##SQLDBName##
  ##SQLQuery##
  ##RowCountThreshold##

This fragment depends on references:  
  RequiredReference: Alias="System", ID="System.Library"
  RequiredReference: Alias="Windows", ID="Microsoft.Windows.Library"
  RequiredReference: Alias="Health", ID="System.Health.Library"
  
@@Author=Kevin Holman@@    
--> 
  <TypeDefinitions>
    <ModuleTypes>
      <DataSourceModuleType ID="##CompanyID##.##AppName##.##UniqueID##.Monitor.DataSource" Accessibility="Internal" Batching="false">
        <Configuration>
		  <xsd:element minOccurs="1" type="xsd:integer" name="IntervalSeconds" xmlns:xsd="http://www.w3.org/2001/XMLSchema" />
		  <xsd:element minOccurs="0" type="xsd:string" name="SyncTime" xmlns:xsd="http://www.w3.org/2001/XMLSchema" />
		  <xsd:element minOccurs="1" type="xsd:string" name="SQLServer" xmlns:xsd="http://www.w3.org/2001/XMLSchema" />
		  <xsd:element minOccurs="1" type="xsd:string" name="SQLDBName" xmlns:xsd="http://www.w3.org/2001/XMLSchema" />
		  <xsd:element minOccurs="1" type="xsd:string" name="SQLQuery" xmlns:xsd="http://www.w3.org/2001/XMLSchema" />	
		  <xsd:element minOccurs="1" type="xsd:integer" name="TimeoutSeconds" xmlns:xsd="http://www.w3.org/2001/XMLSchema" />		  
        </Configuration>
        <OverrideableParameters>
		  <OverrideableParameter ID="IntervalSeconds" Selector="$Config/IntervalSeconds$" ParameterType="int" />
		  <OverrideableParameter ID="SyncTime" Selector="$Config/SyncTime$" ParameterType="string" />
		  <OverrideableParameter ID="TimeoutSeconds" Selector="$Config/TimeoutSeconds$" ParameterType="int" />
        </OverrideableParameters>
        <ModuleImplementation Isolation="Any">
          <Composite>
            <MemberModules>
              <DataSource ID="Scheduler" TypeID="System!System.Scheduler">
                <Scheduler>
                  <SimpleReccuringSchedule>
                    <Interval Unit="Seconds">$Config/IntervalSeconds$</Interval>
					<SyncTime>$Config/SyncTime$</SyncTime>
                  </SimpleReccuringSchedule>
                  <ExcludeDates />
                </Scheduler>
              </DataSource>
              <ProbeAction ID="PA" TypeID="Windows!Microsoft.Windows.PowerShellPropertyBagTriggerOnlyProbe">
                <ScriptName>##CompanyID##.##AppName##.##UniqueID##.Monitor.DataSource.ps1</ScriptName>
                <ScriptBody>
#=================================================================================
#  This script will query a SQL server and output an integer value for the query result
#
#  Author: Kevin Holman
#  v1.0
#=================================================================================
param([string]$SQLServer,[string]$SQLDBName,[string]$SQLQuery)


# Manual Testing section - put stuff here for manually testing script - typically parameters:
#=================================================================================
#$SQLServer = "sqlservername.domain.com"
#$SQLDBName =  "OperationsManager"
#$SQLQuery = "SELECT count(*) from AlertView"
#=================================================================================


#=================================================================================
# Constants section - modify stuff here:
# Assign script name variable for use in event logging
$ScriptName = "##CompanyID##.##AppName##.##UniqueID##.Monitor.DataSource.ps1"
$EventID = "5723"
#=================================================================================


# Starting Script section - All scripts get this
#=================================================================================
# Gather the start time of the script
$StartTime = Get-Date
#Set variable to be used in logging events
$whoami = whoami
# Load MOMScript API
$momapi = New-Object -comObject MOM.ScriptAPI
#Log script event that we are starting task
$momapi.LogScriptEvent($ScriptName,$EventID,0,"`nScript is starting. `nRunning as ($whoami). `nSQLServer: ($SQLServer). `nSQLDBName: ($SQLDBName). `nSQLQuery: ($SQLQuery).")
#=================================================================================


# PropertyBag Script section - Monitoring scripts get this
#=================================================================================
# Load SCOM PropertyBag function
$bag = $momapi.CreatePropertyBag()
#=================================================================================


# Begin MAIN script section
#=================================================================================
# Clear any previous errors
$Error.Clear()
# Connect to and then Query the database
$SqlConnection = New-Object System.Data.SqlClient.SqlConnection
$SqlConnection.ConnectionString = "Server=$SQLServer;Database=$SQLDBName;Integrated Security=True"
$SqlCmd = New-Object System.Data.SqlClient.SqlCommand
$SqlCmd.CommandText = $SQLQuery
$SqlCmd.Connection = $SqlConnection
$SqlAdapter = New-Object System.Data.SqlClient.SqlDataAdapter
$SqlAdapter.SelectCommand = $SqlCmd
$ds = New-Object System.Data.DataSet
$SqlAdapter.Fill($ds) | Out-Null
$SqlConnection.Close()

#Check for errors connecting to SQL
IF($Error)
{
  $Message = "`nFATAL ERROR connecting to SQL server. `nSQLServer: ($SQLServer). `nSQLDBName: ($SQLDBName). `nSQLQuery: ($SqlQuery). `nError is: ($Error)."
  $momapi.LogScriptEvent($ScriptName,$EventID,1,$Message)
  $bag.AddValue('SQLIntegerResult',"0")
  $bag.AddValue('SQLServer',$SQLServer)
  $bag.AddValue('SQLDBName',$SQLDBName)
  $bag.AddValue('SQLQuery',$SQLQuery)
  $bag.AddValue('Result',"Failed Connecting to SQL")
  $bag.AddValue('Message',$Message)  
  # Return all bags
  $bag  
  EXIT
}

# Set the output to an integer variable
[int]$SQLIntegerResult = $ds.Tables[0] | select -ExpandProperty Column1

#Check to ensure we got a value and that it is an integer
IF ($SQLIntegerResult -isnot [int])
{
  $Message = "`nFATAL ERROR getting integer value from SQL Query. `nSQLServer: ($SQLServer). `nSQLDBName: ($SQLDBName). `nSQLQuery: ($SqlQuery). `nError is: ($error)."
  $momapi.LogScriptEvent($ScriptName,$EventID,1,$Message)
  $bag.AddValue('SQLIntegerResult',"0")
  $bag.AddValue('SQLServer',$SQLServer)
  $bag.AddValue('SQLDBName',$SQLDBName)
  $bag.AddValue('SQLQuery',$SQLQuery)
  $bag.AddValue('Result',"Failed to get integer value from SQL Query")
  $bag.AddValue('Message',$Message)   
  # Return all bags
  $bag  
  EXIT
}

# If you had multiple rows and columns to evaluate you would do something like this
#
# foreach ($row in $ds.Tables[0].Rows)
#  {
#    $VAR1 = $row[0].ToString().Trim()
#    $VAR2 = $row[1].ToString().Trim()

# Log the output of the SQL query
$Message = "`nSQL Query successfully completed. `nThe number of rows returned is ($SQLIntegerResult)."
$momapi.LogScriptEvent($ScriptName,$EventID,0,$Message)
#=================================================================================
# End MAIN script section


# PropertyBag Script section - Monitoring scripts get this
#=================================================================================
#Output the PropertyBag
$bag.AddValue('SQLIntegerResult',$SQLIntegerResult)
$bag.AddValue('SQLServer',$SQLServer)
$bag.AddValue('SQLDBName',$SQLDBName)
$bag.AddValue('SQLQuery',$SQLQuery)
$bag.AddValue('Result',"Completed")
$bag.AddValue('Message',$Message) 
# Return all bags
$bag
#=================================================================================


# End of script section
#=================================================================================
#Log an event for script ending and total execution time.
$EndTime = Get-Date
$ScriptTime = ($EndTime - $StartTime).TotalSeconds
$momapi.LogScriptEvent($ScriptName,$EventID,0,"`nScript Completed. `nRuntime: ($ScriptTime) seconds.")
#=================================================================================
# End of script
                </ScriptBody>
                <Parameters>
                  <Parameter>
                    <Name>SQLServer</Name>
                    <Value>$Config/SQLServer$</Value>									
                  </Parameter>
                  <Parameter>
                    <Name>SQLDBName</Name>
                    <Value>$Config/SQLDBName$</Value>									
                  </Parameter>
                  <Parameter>
                    <Name>SQLQuery</Name>
                    <Value>$Config/SQLQuery$</Value>									
                  </Parameter>					  
                </Parameters>
                <TimeoutSeconds>$Config/TimeoutSeconds$</TimeoutSeconds>
              </ProbeAction>
            </MemberModules>
            <Composition>
              <Node ID="PA">
                <Node ID="Scheduler" />
              </Node>
            </Composition>
          </Composite>
        </ModuleImplementation>
        <OutputType>System!System.PropertyBagData</OutputType>
      </DataSourceModuleType>
    </ModuleTypes>
    <MonitorTypes>
      <UnitMonitorType ID="##CompanyID##.##AppName##.##UniqueID##.Monitor.MonitorType" Accessibility="Internal">
        <MonitorTypeStates>
          <MonitorTypeState ID="GoodCondition" NoDetection="false" />
          <MonitorTypeState ID="BadCondition" NoDetection="false" />
        </MonitorTypeStates>
        <Configuration>
		  <xsd:element minOccurs="1" type="xsd:integer" name="IntervalSeconds" xmlns:xsd="http://www.w3.org/2001/XMLSchema" />
		  <xsd:element minOccurs="0" type="xsd:string" name="SyncTime" xmlns:xsd="http://www.w3.org/2001/XMLSchema" />
		  <xsd:element minOccurs="1" type="xsd:string" name="SQLServer" xmlns:xsd="http://www.w3.org/2001/XMLSchema" />
		  <xsd:element minOccurs="1" type="xsd:string" name="SQLDBName" xmlns:xsd="http://www.w3.org/2001/XMLSchema" />
		  <xsd:element minOccurs="1" type="xsd:string" name="SQLQuery" xmlns:xsd="http://www.w3.org/2001/XMLSchema" />
		  <xsd:element minOccurs="1" type="xsd:integer" name="TimeoutSeconds" xmlns:xsd="http://www.w3.org/2001/XMLSchema" />		  
          <xsd:element minOccurs="1" type="xsd:integer" name="RowCountThreshold" xmlns:xsd="http://www.w3.org/2001/XMLSchema" />		  
        </Configuration>
        <OverrideableParameters>
		  <OverrideableParameter ID="IntervalSeconds" Selector="$Config/IntervalSeconds$" ParameterType="int" />
		  <OverrideableParameter ID="SyncTime" Selector="$Config/SyncTime$" ParameterType="string" />
		  <OverrideableParameter ID="TimeoutSeconds" Selector="$Config/TimeoutSeconds$" ParameterType="int" />
          <OverrideableParameter ID="RowCountThreshold" Selector="$Config/RowCountThreshold$" ParameterType="int" />		  
        </OverrideableParameters>
        <MonitorImplementation>
          <MemberModules>
            <DataSource ID="DS" TypeID="##CompanyID##.##AppName##.##UniqueID##.Monitor.DataSource">
			  <IntervalSeconds>$Config/IntervalSeconds$</IntervalSeconds>
			  <SyncTime>$Config/SyncTime$</SyncTime>	
		      <SQLServer>$Config/SQLServer$</SQLServer>
		      <SQLDBName>$Config/SQLDBName$</SQLDBName>
		      <SQLQuery>$Config/SQLQuery$</SQLQuery>
			  <TimeoutSeconds>$Config/TimeoutSeconds$</TimeoutSeconds>			  
            </DataSource>
            <ConditionDetection ID="GoodConditionFilter" TypeID="System!System.ExpressionFilter">
              <Expression>
			    <And>
                  <Expression>
                    <SimpleExpression>
                      <ValueExpression>
                        <XPathQuery Type="Double">Property[@Name='SQLIntegerResult']</XPathQuery>
                      </ValueExpression>
                      <Operator>Less</Operator>
                      <ValueExpression>
                        <Value Type="Double">$Config/RowCountThreshold$</Value>
                      </ValueExpression>
                    </SimpleExpression>
                  </Expression>
				  <Expression>
					<SimpleExpression>
					  <ValueExpression>
						<XPathQuery Type="String">Property[@Name='Result']</XPathQuery>
					  </ValueExpression>
					  <Operator>Equal</Operator>
					  <ValueExpression>
						<Value Type="String">Completed</Value>
					  </ValueExpression>
					</SimpleExpression>
				  </Expression>
			    </And>
			  </Expression>				  
            </ConditionDetection>
            <ConditionDetection ID="BadConditionFilter" TypeID="System!System.ExpressionFilter">
              <Expression>
			    <Or>
                  <Expression>
                    <SimpleExpression>
                      <ValueExpression>
                        <XPathQuery Type="Double">Property[@Name='SQLIntegerResult']</XPathQuery>
                      </ValueExpression>
                      <Operator>GreaterEqual</Operator>
                      <ValueExpression>
                        <Value Type="Double">$Config/RowCountThreshold$</Value>
                      </ValueExpression>
                    </SimpleExpression>
                  </Expression>
				  <Expression>
					<SimpleExpression>
					  <ValueExpression>
						<XPathQuery Type="String">Property[@Name='Result']</XPathQuery>
					  </ValueExpression>
					  <Operator>NotEqual</Operator>
					  <ValueExpression>
						<Value Type="String">Completed</Value>
					  </ValueExpression>
					</SimpleExpression>
				  </Expression>				  
			    </Or>
			  </Expression>				  
            </ConditionDetection>
          </MemberModules>
          <RegularDetections>
            <RegularDetection MonitorTypeStateID="GoodCondition">
              <Node ID="GoodConditionFilter">
                <Node ID="DS" />
              </Node>
            </RegularDetection>
            <RegularDetection MonitorTypeStateID="BadCondition">
              <Node ID="BadConditionFilter">
                <Node ID="DS" />
              </Node>
            </RegularDetection>
          </RegularDetections>
          <OnDemandDetections>
            <OnDemandDetection MonitorTypeStateID="GoodCondition">
              <Node ID="GoodConditionFilter">
                <Node ID="DS" />
              </Node>
            </OnDemandDetection>
            <OnDemandDetection MonitorTypeStateID="BadCondition">
              <Node ID="BadConditionFilter">
                <Node ID="DS" />
              </Node>
            </OnDemandDetection>
          </OnDemandDetections>
        </MonitorImplementation>
      </UnitMonitorType>
    </MonitorTypes>
  </TypeDefinitions>
  <Monitoring>
    <Monitors>
      <UnitMonitor ID="##CompanyID##.##AppName##.##UniqueID##.Monitor" Accessibility="Public" Enabled="true" Target="##ClassID##" ParentMonitorID="Health!System.Health.AvailabilityState" Remotable="true" Priority="Normal" TypeID="##CompanyID##.##AppName##.##UniqueID##.Monitor.MonitorType" ConfirmDelivery="false">
        <Category>AvailabilityHealth</Category>
        <AlertSettings AlertMessage="##CompanyID##.##AppName##.##UniqueID##.Monitor.AlertMessage">
          <AlertOnState>Warning</AlertOnState>
          <AutoResolve>true</AutoResolve>
          <AlertPriority>Normal</AlertPriority>
          <AlertSeverity>Warning</AlertSeverity>
          <AlertParameters>
            <AlertParameter1>$Data/Context/Property[@Name='SQLIntegerResult']$</AlertParameter1>
            <AlertParameter2>$Data/Context/Property[@Name='SQLServer']$</AlertParameter2>
            <AlertParameter3>$Data/Context/Property[@Name='SQLDBName']$</AlertParameter3>
            <AlertParameter4>$Data/Context/Property[@Name='SQLQuery']$</AlertParameter4>
            <AlertParameter5>$Data/Context/Property[@Name='Result']$</AlertParameter5>
            <AlertParameter6>$Data/Context/Property[@Name='Message']$</AlertParameter6>			
          </AlertParameters>		  
        </AlertSettings>
        <OperationalStates>
          <OperationalState ID="Success" MonitorTypeStateID="GoodCondition" HealthState="Success" />
          <OperationalState ID="Warning" MonitorTypeStateID="BadCondition" HealthState="Warning" />
        </OperationalStates>
        <Configuration>
          <IntervalSeconds>##IntervalSeconds##</IntervalSeconds>
		  <SyncTime></SyncTime>		  
		  <SQLServer>##SQLServer##</SQLServer>
		  <SQLDBName>##SQLDBName##</SQLDBName>
		  <SQLQuery>##SQLQuery##</SQLQuery>
		  <TimeoutSeconds>120</TimeoutSeconds>
		  <RowCountThreshold>##RowCountThreshold##</RowCountThreshold>		  
        </Configuration>
      </UnitMonitor>
    </Monitors>
  </Monitoring>
  <Presentation>
    <StringResources>
      <StringResource ID="##CompanyID##.##AppName##.##UniqueID##.Monitor.AlertMessage" />
    </StringResources>
  </Presentation>
  <LanguagePacks>
    <LanguagePack ID="ENU" IsDefault="true">
      <DisplayStrings>
        <DisplayString ElementID="##CompanyID##.##AppName##.##UniqueID##.Monitor">
          <Name>##CompanyID## ##AppName## ##UniqueID## Monitor</Name>
          <Description></Description>
        </DisplayString>
        <DisplayString ElementID="##CompanyID##.##AppName##.##UniqueID##.Monitor" SubElementID="Success">
          <Name>GoodCondition</Name>
        </DisplayString>
        <DisplayString ElementID="##CompanyID##.##AppName##.##UniqueID##.Monitor" SubElementID="Warning">
          <Name>BadCondition</Name>
        </DisplayString>
        <DisplayString ElementID="##CompanyID##.##AppName##.##UniqueID##.Monitor.AlertMessage">
          <Name>##CompanyID## ##AppName## ##UniqueID## Monitor: Failure</Name>
          <Description>##CompanyID## ##AppName## ##UniqueID## Monitor: Failure
Result: {4}
Message: {5}
SQLIntegerResult: {0}
SQLServer: {1}
SQLDBName: {2}
SQLQuery: {3}
</Description>
        </DisplayString>		
      </DisplayStrings>
    </LanguagePack>
  </LanguagePacks>
</ManagementPackFragment>