Creating a monitor and rule from an SQL Query value, using oledbprobe module
Last week a client ask me for help with a monitor and a rule based on the result of an SQL Query, in the past I’ve done this sometimes using a powershell script, but this time I want it to use the OleDbProbe module to accomplish my goal.
After some research it was pretty straight forward to do it so I will post the sections of the code I used and explained the management pack part by part.
First I created a simple class base on Windows Server Computer Role:
<EntityTypes>
< ClassTypes>
< ClassType ID="test.Monitor.Class" Accessibility="Internal" Abstract="false" Base="Windows!Microsoft.Windows.ComputerRole" Hosted="true" Singleton="false" />
</ClassTypes>
< /EntityTypes>
Now that we have the class I need to create a discovery for it, and because I’m just going to monitor this on one specific server I go with a WMI query for the machine name, this will be my watcher node:
<Discoveries>
<Discovery ID="test.Monitor.Discovery" Enabled="true" Target="Windows!Microsoft.Windows.Server.Computer" ConfirmDelivery="false" Remotable="true" Priority="Normal">
<Category>Discovery</Category>
< DiscoveryTypes>
< DiscoveryClass TypeID="test.Monitor.Class" />
</DiscoveryTypes>
<DataSource ID="DS" TypeID="Windows!Microsoft.Windows.WmiProviderWithClassSnapshotDataMapper">
< NameSpace>root\cimv2</NameSpace>
<Query>Select * from Win32_ComputerSystem where name like "XXXXXXXXXXX"</Query>
<Frequency>60</Frequency>
< ClassId>$MPElement[Name="test.Monitor.Class"]$</ClassId>
< InstanceSettings>
<Settings>
<Setting>
<Name>$MPElement[Name="Windows!Microsoft.Windows.Computer"]/PrincipalName$</Name>
<Value>$Target/Property[Type="Windows!Microsoft.Windows.Computer"]/PrincipalName$</Value>
</Setting>
<Setting>
<Name>$MPElement[Name="System!System.Entity"]/DisplayName$</Name>
<Value>$Target/Property[Type="System!System.Entity"]/DisplayName$</Value>
</Setting>
</Settings>
</InstanceSettings>
</DataSource>
</Discovery>
< /Discoveries>
Now that we have a class and a way to discover it, let’s get to the fun part of this post, we need to first create a probe action that it’s going to be the one that obtains the information we need, this one is going to be based on OleDbProbe module and it will receive a value for ConnectionString and Query, I’ll do it this way because it’s easier to reuse it when you need to create multiple different monitors and rules using different queries, then we need a datasource to use our probe action, notice that we can use a runas account to avoid giving priviledges to localsystem:
<ModuleTypes>
< DataSourceModuleType ID="test.Monitor.DataSource" Accessibility="Internal" RunAs="test.Monitor.DBAccessAccount" Batching="false">
<Configuration>
< xsd:element minOccurs="1" name="ConnectionString" type="xsd:string" />
< xsd:element minOccurs="1" name="Query" type="xsd:string" />
< xsd:element minOccurs="1" name="IntervalSeconds" type="xsd:integer" />
< xsd:element minOccurs="0" name="SyncTime" type="xsd:string" />
</Configuration>
< OverrideableParameters>
< 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>$Config/SyncTime$</SyncTime>
</DataSource>
< ProbeAction ID="Probe" TypeID="test.monitor.OleDb.ProbeAction">
< ConnectionString>$Config/ConnectionString$</ConnectionString>
<Query>$Config/Query$</Query>
</ProbeAction>
</MemberModules>
< Composition>
<Node ID="Probe">
<Node ID="Scheduler" />
</Node>
</Composition>
</Composite>
</ModuleImplementation>
< OutputType>System!System.OleDbData</OutputType>
</DataSourceModuleType>
< ProbeActionModuleType ID="test.monitor.OleDb.ProbeAction" Accessibility="Public" RunAs="test.Monitor.DBAccessAccount" Batching="false" PassThrough="false">
<Configuration>
< xsd:element minOccurs="1" name="ConnectionString" type="xsd:string" />
< xsd:element minOccurs="1" name="Query" type="xsd:string" />
</Configuration>
<OverrideableParameters>
< OverrideableParameter ID="ConnectionString" Selector="$Config/ConnectionString$" ParameterType="string" />
< OverrideableParameter ID="Query" Selector="$Config/Query$" ParameterType="string" />
</OverrideableParameters>
< ModuleImplementation Isolation="Any">
<Composite>
< MemberModules>
< ProbeAction ID="OleDBPA" TypeID="System!System.OleDbProbe">
< ConnectionString>$Config/ConnectionString$</ConnectionString>
<Query>$Config/Query$</Query>
< GetValue>true</GetValue>
<OneRowPerItem>false</OneRowPerItem>
</ProbeAction>
</MemberModules>
<Composition>
<Node ID="OleDBPA" />
</Composition>
</Composite>
</ModuleImplementation>
< OutputType>System!System.OleDbData</OutputType>
< InputType>System!System.BaseData</InputType>
</ProbeActionModuleType>
< /ModuleTypes>
So we have the base we need to get the information, now we just have to create a custom monitor type, so we can accomplish our monitoring goal, this monitor will have 3 states and will receive the Connection String and Query to execute, so every time we create a monitor we can define this two things, also we have some overridable parameters IntervalSeconds, SyncTime and Thresholds, noticed that when we use the OleDbProbe module we need to create an xpath query so we can get the value out of the oledb data that is return from our datasource, this comes in a format like this “Columns[x]/Column[y]”, where x is the row we need and y the column:
<MonitorTypes>
< UnitMonitorType ID="test.Monitor.MonitorType.testdbValue" Accessibility="Internal" RunAs="test.Monitor.DBAccessAccount">
< MonitorTypeStates>
< MonitorTypeState ID="UnderWarning" NoDetection="false" />
< MonitorTypeState ID="OverWarning" NoDetection="false" />
< MonitorTypeState ID="OverError" NoDetection="false" />
</MonitorTypeStates>
<Configuration>
< xsd:element minOccurs="1" name="IntervalSeconds" type="xsd:integer" />
< xsd:element minOccurs="1" name="Query" type="xsd:string" />
< xsd:element minOccurs="1" name="ConnectionString" type="xsd:string" />
< xsd:element minOccurs="0" name="SyncTime" type="xsd:string" />
< xsd:element minOccurs="1" name="WarningThreshold" type="xsd:integer" />
< xsd:element minOccurs="1" name="ErrorThreshold" type="xsd:integer" />
< xsd:element minOccurs="1" name="ColumnsColumn" type="xsd:string" />
</Configuration>
< OverrideableParameters>
< OverrideableParameter ID="IntervalSeconds" Selector="$Config/IntervalSeconds$" ParameterType="int" />
<OverrideableParameter ID="SyncTime" Selector="$Config/SyncTime$" ParameterType="string" />
< OverrideableParameter ID="WarningThreshold" Selector="$Config/WarningThreshold$" ParameterType="int" />
< OverrideableParameter ID="ErrorThreshold" Selector="$Config/ErrorThreshold$" ParameterType="int" />
</OverrideableParameters>
< MonitorImplementation>
< MemberModules>
< DataSource ID="DataSource" TypeID="test.Monitor.DataSource">
< ConnectionString>$Config/ConnectionString$</ConnectionString>
<Query>$Config/Query$</Query>
<IntervalSeconds>$Config/IntervalSeconds$</IntervalSeconds>
< SyncTime>$Config/SyncTime$</SyncTime>
</DataSource>
< ProbeAction ID="Probe" TypeID="test.monitor.OleDb.ProbeAction">
< ConnectionString>$Config/ConnectionString$</ConnectionString>
<Query>$Config/Query$</Query>
</ProbeAction>
< ConditionDetection ID="FilterUnderWarning" TypeID="System!System.ExpressionFilter">
<Expression>
< SimpleExpression>
< ValueExpression>
< XPathQuery Type="Integer">Columns[1]/Column[1]</XPathQuery>
</ValueExpression>
<Operator>Less</Operator>
< ValueExpression>
<Value Type="Integer">$Config/WarningThreshold$</Value>
</ValueExpression>
</SimpleExpression>
</Expression>
</ConditionDetection>
< ConditionDetection ID="FilterOverWarning" TypeID="System!System.ExpressionFilter">
<Expression>
<And>
<Expression>
< SimpleExpression>
< ValueExpression>
<XPathQuery Type="Integer">Columns[1]/Column[1]</XPathQuery>
</ValueExpression>
<Operator>GreaterEqual</Operator>
< ValueExpression>
<Value Type="Integer">$Config/WarningThreshold$</Value>
</ValueExpression>
</SimpleExpression>
</Expression>
<Expression>
< SimpleExpression>
< ValueExpression>
< XPathQuery Type="Integer">Columns[1]/Column[1]</XPathQuery>
</ValueExpression>
<Operator>Less</Operator>
<ValueExpression>
<Value Type="Integer">$Config/ErrorThreshold$</Value>
</ValueExpression>
</SimpleExpression>
</Expression>
</And>
</Expression>
</ConditionDetection>
< ConditionDetection ID="FilterOverError" TypeID="System!System.ExpressionFilter">
<Expression>
<SimpleExpression>
< ValueExpression>
<XPathQuery Type="Integer">Columns[1]/Column[1]</XPathQuery>
</ValueExpression>
<Operator>GreaterEqual</Operator>
< ValueExpression>
<Value Type="Integer">$Config/ErrorThreshold$</Value>
</ValueExpression>
</SimpleExpression>
</Expression>
</ConditionDetection>
</MemberModules>
< RegularDetections>
< RegularDetection MonitorTypeStateID="UnderWarning">
<Node ID="FilterUnderWarning">
<Node ID="DataSource" />
</Node>
</RegularDetection>
< RegularDetection MonitorTypeStateID="OverWarning">
<Node ID="FilterOverWarning">
<Node ID="DataSource" />
</Node>
</RegularDetection>
< RegularDetection MonitorTypeStateID="OverError">
<Node ID="FilterOverError">
<Node ID="DataSource" />
</Node>
</RegularDetection>
</RegularDetections>
</MonitorImplementation>
</UnitMonitorType>
< /MonitorTypes>
Finally we will create a rule and monitor as the final step, I used a simple query to get a value from the OperationsManager DB, nothing complicated, use your own query for testing your management pack:
<Rules>
<Rule ID="test.Monitor.Performance.Rule" Enabled="true" Target="test.Monitor.Class" ConfirmDelivery="true" Remotable="true" Priority="Normal" DiscardLevel="100">
<Category>PerformanceCollection</Category>
<DataSources>
< DataSource ID="DataSource" RunAs="test.Monitor.DBAccessAccount" TypeID="test.Monitor.DataSource">
<ConnectionString>Provider=SQLOLEDB;Server=SQLSCOM;Database=OperationsManager;Integrated Security=SSPI</ConnectionString>
<Query>SELECT TOP 1 [ConsoleTaskAccessibility] FROM [OperationsManager].[dbo].[ConsoleTask]</Query>
< IntervalSeconds>60</IntervalSeconds>
</DataSource>
</DataSources>
< ConditionDetection ID="MapToPerf" RunAs="test.Monitor.DBAccessAccount" TypeID="Performance!System.Performance.DataGenericMapper">
< ObjectName>testdb</ObjectName>
< CounterName>CountofErrors</CounterName>
< InstanceName>testdb</InstanceName>
<Value>$Data/Columns[1]/Column[1]$</Value>
</ConditionDetection>
< WriteActions>
< WriteAction ID="WriteToDB" TypeID="SC!Microsoft.SystemCenter.CollectPerformanceData" />
< WriteAction ID="WritToDW" TypeID="MicrosoftSystemCenterDataWarehouseLibrary!Microsoft.SystemCenter.DataWarehouse.PublishPerformanceData" />
</WriteActions>
</Rule>
< /Rules>
< Monitors>
< UnitMonitor ID="test.Monitor.DBValue" Accessibility="Internal" Enabled="true" Target="test.Monitor.Class" ParentMonitorID="Health!System.Health.PerformanceState" Remotable="true" Priority="Normal" RunAs="test.Monitor.DBAccessAccount" TypeID="test.Monitor.MonitorType.testdbValue" ConfirmDelivery="true">
<Category>Custom</Category>
< AlertSettings AlertMessage="test.Monitor.DBValue_AlertMessageResourceID">
< AlertOnState>Warning</AlertOnState>
< AutoResolve>true</AutoResolve>
< AlertPriority>Normal</AlertPriority>
< AlertSeverity>Error</AlertSeverity>
</AlertSettings>
< OperationalStates>
< OperationalState ID="UIGeneratedOpStateId621f6fa1b92d4dc2bc9b2fc34d24af84" MonitorTypeStateID="UnderWarning" HealthState="Success" />
< OperationalState ID="UIGeneratedOpStateId4b3e701018e04481bc82598d8c4a9446" MonitorTypeStateID="OverWarning" HealthState="Warning" />
< OperationalState ID="UIGeneratedOpStateId005add145c944b9d9c29a4d4cbef8636" MonitorTypeStateID="OverError" HealthState="Error" />
</OperationalStates>
<Configuration>
< IntervalSeconds>60</IntervalSeconds>
<Query>SELECT TOP 1 [ConsoleTaskAccessibility] FROM [OperationsManager].[dbo].[ConsoleTask]</Query>
< ConnectionString>Provider=SQLOLEDB;Server=SQLSCOM;Database=OperationsManager;Integrated Security=SSPI</ConnectionString>
< WarningThreshold>2</WarningThreshold>
< ErrorThreshold>3</ErrorThreshold>
< ColumnsColumn>Valor</ColumnsColumn>
</Configuration>
</UnitMonitor>
< /Monitors>