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.