Skip to content

Configure direct events collection using the Splunk Add-on for Microsoft SCOM

Complete these steps to migrate rom scriptlet-based events collection to a direct, SQL-based approach. Splunk best practice is to first exercise the migration in a test environment.

  1. In Splunk platform, from the Apps menu select Splunk Add-on for Microsoft SCOM and go to Inputs tab.
  2. Note the Index name and (optionally) the Interval
  3. Change the status of the selected input to Disabled.
  4. Go to the search page, find last event, and note it’s “scom_timestamp” value

Prerequisites for direct events processing using the Splunk Add-on for Microsoft SCOM

You need Splunk DB Connect to utilize direct events processing for Microsoft SCOM.

Configure Microsoft SCOM Database to send data

Work with your local Database admin to create the least-privileged account for collecting data from the database. At a minimum, the account can open a database connection from the machine with the add-on installed to the machine with the database. It can be a local account for collocated services, or a domain user account if the instances are separated. This account needs read-only (“select”) access to the “OperationsManager” database, “OperationsManager.dbo.__MOMManagementGroupInfo__” table and Tables/Views (depending of data types to be collected).

Source type Table/View name
Alert dbo.AlertView
Alert History dbo.AlertHistory
Performance dbo.PerformanceDataAllView, dbo.PerformanceCounterSignatureView, dbo.RuleView
Event dbo.EventView, dbo.RuleView, dbo.LocalizedText

Configure DB Connect v3 inputs

This topic presents the instructions for DB Connect Version 3.6 and above. For previous versions follow the instructions that correspond to the version of DB Connect that you have installed.

To prepare your environment and configure your inputs, follow these steps.

Download Splunk DB Connect

Download Splunk DB Connect from Splunkbase or Splunk Web UI.

Set up the database connection

Download and install the Microsoft JDBC driver for SQL Server

To enable Microsoft SQL Server connections, download and install the Microsoft JDBC Driver for SQL Server as described in the Install database drivers section of the Deploy and Use Splunk DB Connect manual.

Create an identity in the Splunk platform

See how to Create an identity in the Splunk DB Connect manual.

Configure the inputs

Direct events processing SQL queries are available as templates in Splunk DB Connect.

  1. Select Splunk DB Connect from Apps menu.
  2. In Splunk DB Connect, on the Data Lab page select Inputs tab, and select New Input.
  3. In the Choose Table section on the left, from the Connection menu, select defined database connection.

  4. In the Settings section on the right, from the Query Template menu, select one of “Splunk Add-on for Microsoft SCOM” templates.

    Template name Template description
    msscom:alerts Collect alerts data from MS SCOM
    msscom:alertshistory Collect alerts history data from MS SCOM
    msscom:allperformance Collect all performance data from MS SCOM
    msscom:events Collect audit event data from MS SCOM

    Leave “Input Mode” and “Input Type” (“Event” / “Rising”) as is.

  5. Select the “Execute SQL” button next to “Settings” menu.

    Note

    This initial execution is used to validate the query and download metadata. If you examine the query you notice a query filter similar to where ah.TimeAdded > '2022-09-01 06:51:29.413'. You may replace the date with any other date from the past to get the results faster (remember about proper format!).

  6. After successful query execution make sure that “scom_timestamp” column is selected as a rising column (the column to differentiate between events from past and events that should be picked up).

  7. Checkpoint Value: depending on scenario - for fresh installations it can be any date (but usually SCOM Operational Database stores the information up to two weeks); for migration scenario adequate scriptlet input shall be disabled first and timestamp of the latest collected event shall be used (to avoid gaps and duplicated events).
  8. Timestamp (which column shall be used as an event timestamp): Click the Choose Column button, scom_timestamp column shall be selected from dropdown below.
  9. Query Timeout: consult your DBA to select optimal value, the default is 30 seconds if you leave it blank.

  10. Scroll back to the top of the page. Note that step 3 of “Follow these steps:” procedure shall be marked green. If you press “Execute SQL” button at this moment you shall see following error com.microsoft.sqlserver.jdbc.SQLServerException: The index 1 is out of range.. It means that the query is sent with a single variable (defined in “Checkpoint Value” field), but there is no place to inject this value.

  11. Navigate to the bottom of the query. Locate line similar to where ah.TimeAdded > '2022-09-01 06:51:29.413' (it may be ​​ah.TimeAdded, av.TimeAdded, pdav.TimeAdded or ev.TimeAdded depending on selected template).

  12. Replace quoted timestamp with single question mark. Line where ah.TimeAdded > '2022-09-01 06:51:29.413' shall now look like this: where ah.TimeAdded > ?.
  13. Press the “Execute SQL” button again. The query shall be executed successfully. In case of the issues contact your DBA.
  14. Once all the steps of “Follow these steps” procedure is marked green, select the green Next button at the top of the page.
  15. Select descriptive name. You can modify the description if you want, but you should leave the application as it is.
  16. Leave “Max Rows to Retrieve” blank unless you are ready to drop some events.
  17. Fetch size: Consult this value with your DBA.
  18. Execution Frequency: it purely depends on your usage scenario, but you can start with default values and tune it later. More frequent executions result in additional overload to database server while less frequent execution create bigger data packages to be processed at once and increased delay.
  19. Metadata: Predefined source type is required for valid Data model mapping. In case of the migration - Index value shall match “scriptlet” input.

Follow Create a database input procedure from Deploy and Use Splunk DB Connect manual in case of unexpected issues.