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. Go to "Apps" -> "Splunk Add-on for Microsoft SCOM" -> "Inputs"
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].

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 steps on how to Create an identity from 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, click Data Lab > Inputs and then New Input.
3. Select defined database connection in left-hand menu dropdown: "Choose Table" -> "Connection"
4. Select one of "Splunk Add-on for Microsoft SCOM" templates from "Settings" right-hand menu

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

1. Press 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!)
2. 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)
3. 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)
4. Timestamp (which column shall be used as an event timestamp): Click the "Choose Column" button, scom_timestamp column shall be selected from dropdown below
5. Query Timeout: consult your DBA to select optimal value, the default is 30 seconds if you leave it blank
6. 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.

1. 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)
2. 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 > ?
3. Press the "Execute SQL" button again. The query shall be executed successfully. In case of the issues contact your DBA
4. Once all the steps of "Follow these steps" procedure is marked green press green "Next" button at the top of the page
5. Select descriptive name, feel free to modify the description, we suggest to leave the application as it is
6. We suggest to leave "Max Rows to Retrieve" blank unless you are ready to drop some events
7. Fetch size: Feel free to consult this value with your DBA
8. Execution Frequency: it purely depends on your usage scenario but we suggest to 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
9. 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.