How to use an oledb module for discovery #sysctr #scom


I’ve been asked to post a sample on how to use an oledb module to perform discovery. The scenario applies everytime you have to be managed entities listed in a generic OLEDB table. Indeed the process is super standard and easy. The only attention points are aimed at instructing the OLEDB provider to return one data item per row, since we want to discover an entity for each returned row and to return not fetching time information:

          <GetValue>true</GetValue>

          <OneRowPerItem>true</OneRowPerItem>

          <GetFetchTime>false</GetFetchTime>

The following snippets searches for dtabases that match a specific pattern and creates one managed entity for each of them, the exact same technique can be used for any non system table. I choose this one to be generic enough.

The composition is straightforward:

          Discovery scheduler

          The OLEDB query

          A condition detection to check if the query is successful and if rows are returned

          A condition data mapper to actually discover the managed entities

<DataSourceModuleType ID=QND.OLEDB.DT Accessibility=Internal RunAs=SQL!Microsoft.SQLServer.SQLProbeAccount Batching=false>

  <Configuration>

    <IncludeSchemaTypes>

      <SchemaType>System!System.Discovery.MapperSchema</SchemaType>

    </IncludeSchemaTypes>

    <xsd:element name=IntervalSeconds type=xsd:integer />

    <xsd:element name=SyncTime type=xsd:string />

    <xsd:element name=ConnectionString type=xsd:string/>

    <xsd:element name=DBName type=xsd:string />

    <xsd:element name=QueryTimeout type=xsd:integer  />

    <xsd:element name=ClassId type=xsd:string />

    <xsd:element name=InstanceSettings type=SettingsType minOccurs=0 maxOccurs=1 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=QueryTimeout Selector=$Config/QueryTimeout$ ParameterType=int />

 

  </OverrideableParameters>

  <ModuleImplementation Isolation=Any>

    <Composite>

      <MemberModules>

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

          <Scheduler>

            <SimpleReccuringSchedule>

              <Interval>$Config/IntervalSeconds$</Interval>

              <SyncTime>$Config/SyncTime$</SyncTime>

            </SimpleReccuringSchedule>

            <ExcludeDates />

          </Scheduler>

        </DataSource>

        <ProbeAction ID=SQLProbe TypeID=System!System.OleDbProbe>

          <ConnectionString>$Config/ConnectionString$</ConnectionString>

          <Query>

            <![CDATA[SELECT name, state_desc FROM sys.databases WHERE source_database_id IS NULL and Name like ‘$Config/DBName$’]]>

          </Query>

          <GetValue>true</GetValue>

          <OneRowPerItem>true</OneRowPerItem>

          <QueryTimeout>$Config/QueryTimeout$</QueryTimeout>

          <GetFetchTime>false</GetFetchTime>

        </ProbeAction>

        <ConditionDetection ID=Filter TypeID=System!System.ExpressionFilter>

          <Expression>

            <And>

              <Expression>

                <SimpleExpression>

                  <ValueExpression>

                    <XPathQuery Type=String>Result</XPathQuery>

                  </ValueExpression>

                  <Operator>Equal</Operator>

                  <ValueExpression>

                    <Value Type=String>Success</Value>

                  </ValueExpression>

                </SimpleExpression>

              </Expression>

              <Expression>

                <SimpleExpression>

                  <ValueExpression>

                    <XPathQuery Type=Integer>RowLength</XPathQuery>

                  </ValueExpression>

                  <Operator>Greater</Operator>

                  <ValueExpression>

                    <Value Type=Integer>0</Value>

                  </ValueExpression>

                </SimpleExpression>

              </Expression>

            </And>

          </Expression>

        </ConditionDetection>

        <ConditionDetection ID=Mapper TypeID=System!System.Discovery.ClassSnapshotDataMapper>

          <ClassId>$Config/ClassId$</ClassId>

          <InstanceSettings>$Config/InstanceSettings$</InstanceSettings>

        </ConditionDetection>

      </MemberModules>

      <Composition>

        <Node ID=Mapper>

          <Node ID=Filter>

            <Node ID=SQLProbe>

              <Node ID=Scheduler />

            </Node>

          </Node>

        </Node>

      </Composition>

    </Composite>

  </ModuleImplementation>

  <OutputType>System!System.Discovery.Data</OutputType>

</DataSourceModuleType>

This generic data source can be used in a discovery rule as follows.

<Monitoring>

  <Discoveries>

    <Discovery ID=QND.OLEDB.Discovery Enabled=true Target=SQL2008!Microsoft.SQLServer.2008.DBEngine ConfirmDelivery=false Remotable=true Priority=Normal>

      <Category>Discovery</Category>

      <DiscoveryTypes>

        <DiscoveryClass TypeID=QND.OLEDB.Database>

        </DiscoveryClass>

      </DiscoveryTypes>

      <DataSource ID=SQLQuery TypeID=QND.OLEDB.DT>

        <IntervalSeconds>86400</IntervalSeconds>

        <SyncTime />

        <ConnectionString>Provider=SQLOLEDB;Server=$Target/Property[Type=”SQL!Microsoft.SQLServer.DBEngine”]/ConnectionString$;Database=master;Integrated Security=SSPI</ConnectionString>

        <DBName>MYDB%</DBName>

        <QueryTimeout>900</QueryTimeout>

        <ClassId>$MPElement[Name=’QND.OLEDB.Database’]$</ClassId>

        <InstanceSettings>

          <Settings>

            <Setting>

              <Name>$MPElement[Name=”Windows!Microsoft.Windows.Computer”]/PrincipalName$</Name>

              <Value>$Target/Host/Property[Type=”Windows!Microsoft.Windows.Computer”]/PrincipalName$</Value>

            </Setting>

            <Setting>

              <Name>$MPElement[Name=”SQL!Microsoft.SQLServer.ServerRole”]/InstanceName$</Name>

              <Value>$Target/Property[Type=”SQL!Microsoft.SQLServer.ServerRole”]/InstanceName$</Value>

            </Setting>

            <Setting>

              <Name>$MPElement[Name=’SQL!Microsoft.SQLServer.Database’]/DatabaseName$</Name>

              <Value>$Data/Columns/Column[1]$</Value>

            </Setting>

          </Settings>

        </InstanceSettings>

      </DataSource>

    </Discovery>

 

  </Discoveries>

</Monitoring>

 

Hope this helps and answers the questions.

  • Daniele This posting is provided “AS IS” with no warranties, and confers no rights.
Advertisements
  1. #1 by Raphael Burri on December 12, 2013 - 12:12 pm

    Hi Daniele
    Great write-up! This works quite well but may I suggest you look into using System.LogicalSet.ExpressionFilter instead of System.ExpressionFilter? Doing so you avoid an accidental “undiscovery” should the query fail due to a temporary issue (network connectivity, timeout or the like).

    System.LogicalSet.ExpressionFilter allows to completely block the discovery workflow in such an event. Hence it will not return an empty discovery data item. You might see a warning event in the Operations Manager event log (on an MS at least) but I prefer that over vanishing and reappearing objects.

    Cheers

    Raphael

    • #2 by Daniele Grandini on December 13, 2013 - 6:06 pm

      Hi Raphael, yes it seems a good option, especially if the SQL engine is remote.
      – Daniele

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

%d bloggers like this: