Microsoft System Center Operations Manager

Microsoft System Center Operations Manager (SCOM) provides infrastructure monitoring across various services, devices, and operations from a single console. The activities related to these systems are recorded in SCOM’s databases, and these databases can be queried using SQL. The resulting data can be collected and forwarded by NXLog Agent.

SCOM log types

Collected event logs: These events are collected by filtering rules in configured management packs.
Alert logs: Alerts are significant events generated by rules and monitors.
SCOM administrative event logs: Administrative actions executed in SCOM are either unsupported by Microsoft (requiring SQL triggers in the OM database and thus voiding the warranty) or too performance-heavy with little meaningful data to retrieve.

The default retention time for resolved alerts and collected events is seven days, after which the database entries are groomed. To configure database grooming settings, read the TechNet article How to configure grooming settings for the Operations Manager database.

Collecting SCOM logs

For NXLog Agent to collect logs, the following prerequisites must be completed.

Create a Windows/SQL account with read permissions for the Operations Manager database.
Configure an ODBC 32-bit System Data Source on the server running NXLog Agent. For more information, consult the Microsoft ODBC Data Source Administrator guide or the unixODBC Project.
Set an appropriate firewall rule on the database server that accepts connections from the server running NXLog Agent. Open TCP port 1433 or whichever port the SQL Server is configured to allow SQL Server access on. For further information, read the Configure Windows Firewall for Database Engine Access guide.

NXLog Agent can then be configured with one or more im_odbc input modules, each with an SQL query that produces the fields to be logged.

The configured SQL query must contain a way to serialize the result set, enabling NXLog Agent to resume reading logs where it left off after a restart. This is easily achieved by using an auto-increment-like solution or a timestamp field. See the example below.

Example 1. Collecting Operations Manager event logs and alert logs

This example queries the database for event logs and unresolved alert logs, then sends the results in JSON format to a plain text file. Note the Exec directive in the scom_alerts input instance. It is used to extract the content of the AlertParameters field that is itself a composite (XML) structure. You should define your regular expressions to extract data you are interested in from the alerts' AlertParameters and Context fields and the events' EventData and EventParameters fields.

This example uses the DATEDIFF SQL function to generate a timestamp from an SQL datetime field with millisecond precision. The timestamp is used to serialize the result set as required by NXLog Agent. Starting with SQL Server 2016, the DATEDIFF_BIG T-SQL function can be used instead (see DATEDIFF_BIG (Transact-SQL) at MSDN).

nxlog.conf

<Extension _json>
    Module              xm_json
</Extension>

<Input scom_events>
    Module              im_odbc
    ConnectionString    DSN=scom;uid=username@mydomain.local;pwd=mypassword;\
                        database=OperationsManager
    SQL                 SELECT CAST(DATEDIFF(minute, '19700101', CAST(EV.TimeGenerated AS DATE)) \
                               AS BIGINT) * 60000 + DATEDIFF(ms, '19000101', \
                               CAST(EV.TimeGenerated AS TIME)) AS 'id', \
                               EV.TimeGenerated AS 'EventTime', \
                               EV.TimeAdded AS 'EventAddedTime', \
                               EV.Number AS 'EventID', \
                               EV.MonitoringObjectDisplayName AS 'Source', \
                               R.DisplayName AS 'RuleName', \
                               EV.EventData, EV.EventParameters \
                        FROM   EventView EV JOIN RuleView R WITH (NOLOCK) ON \
                               EV.RuleId = R.id \
                        WHERE  CAST(DATEDIFF(minute, '19700101',    CAST(EV.TimeGenerated \
                               AS DATE)) AS BIGINT) * 60000 + DATEDIFF(ms, '19000101', \
                               CAST(EV.TimeGenerated AS TIME)) > ?
    PollInterval        30
    IdIsTimeStamp       FALSE
</Input>

<Input scom_alerts>
    Module              im_odbc
    ConnectionString    DSN=scom;uid=username@mydomain.local;pwd=mypassword;\
                        database=OperationsManager
    SQL                 SELECT CAST(DATEDIFF(minute, '19700101', CAST(AL.TimeRaised AS DATE)) AS \
                               BIGINT) * 60000 + DATEDIFF(ms, '19000101', \
                               CAST(AL.TimeRaised AS TIME)) AS 'id', \
                               AL.AlertStringName AS 'AlertName', \
                               AL.Category AS 'Category', \
                               AL.AlertStringDescription AS 'AlertDescription', \
                               AL.TimeRaised AS 'EventTime', \
                               AL.TimeAdded AS 'EventAddedTime', AL.Context, \
                               AL.AlertParams AS 'AlertParameters' \
                        FROM   AlertView AL \
                        WHERE  AL.resolutionstate <> 255 AND CAST(DATEDIFF(minute, \
                               '19700101', CAST(AL.TimeRaised AS DATE)) AS BIGINT) * \
                               60000 + DATEDIFF(ms, '19000101', CAST(AL.TimeRaised AS \
                               TIME)) > ?
    <Exec>
        if $AlertParameters =~ /(?x)\<AlertParameters\>\<AlertParameter\d\>(.*)
                                \<\/AlertParameter\d\>\<\/AlertParameters\>$/sm
           $AlertMessage = $1;
    </Exec>
    PollInterval        30
    IdIsTimeStamp       FALSE
</Input>

<Output outfile>
    Module              om_file
    File                'C:\logs\out.log'
    Exec                to_json();
</Output>

<Route r>
    Path                scom_events, scom_alerts => outfile
</Route>

Output sample (event log)

{
  "id": 1652118235036,
  "EventTime": "2022-05-09T17:43:55.037000-07:00",
  "EventAddedTime": "2022-05-09T17:43:56.167000-07:00",
  "EventID": 7036,
  "Source": "Data Access Service - win2019-test.nxlog.local",
  "RuleName": "Collect Event Log Events",
  "EventData": "<DataItem type=\"System.XmlData\" time=\"2022-05-09T10:43:55.0353299-07:00\" sourceHealthServiceId=\"0B30EB2E-007F-4C9E-21DC-639D4446B102\"><EventData xmlns=\"http://schemas.microsoft.com/win/2004/08/events/event\"><Data Name=\"param1\">System Center Data Access Service</Data><Data Name=\"param2\">running</Data><Binary>4F004D00530044004B002F0034000000</Binary></EventData></DataItem>",
  "EventParameters": "<Param>System Center Data Access Service</Param><Param>running</Param><Param>-</Param>",
  "EventReceivedTime": "2022-05-09T15:48:14.691026-07:00",
  "SourceModuleName": "scom_events",
  "SourceModuleType": "im_odbc"
}

Output sample (alert log)

{
  "id": 1652118203096,
  "AlertName": "Unable to Verify Run As Account",
  "Category": "AvailabilityHealth",
  "AlertDescription": "The System Center Management Health Service is unable to verify the Run As account.",
  "EventTime": "2022-05-09T17:43:23.097000-07:00",
  "EventAddedTime": "2022-05-09T17:43:23.253000-07:00",
  "Context": "<DataItem type=\"Microsoft.Windows.EventData\" time=\"2022-05-09T10:42:50.5332957-07:00\" sourceHealthServiceId=\"0B30EB2E-007F-4C9E-21DC-639D4446B102\"><EventOriginId>{41A8C7EC-6BAE-471E-AC2E-0EBDCD2D1237}</EventOriginId><PublisherId>{07662796-8DD4-4E1B-6676-B5CB4A8E4CAA}</PublisherId><PublisherName>HealthService</PublisherName><EventSourceName>HealthService</EventSourceName><Channel>Operations Manager</Channel><LoggingComputer>win2019-test.nxlog.local</LoggingComputer><EventNumber>3221232488</EventNumber><EventCategory>1</EventCategory><EventLevel>1</EventLevel><UserName>N/A</UserName><RawDescription><![CDATA[The Health Service cannot verify the future validity of the RunAs account %1\\%2 for management group %5 due to an error retrieving information from Active Directory (for Domain Accounts) or the local security authority (for Local Accounts).  The error is %3(%4).\r\n]]></RawDescription><LCID>1033</LCID><CollectDescription Type=\"Boolean\">true</CollectDescription><Params><Param>NXLOG</Param><Param>vicente.munoz</Param><Param>The specified domain either does not exist or could not be contacted.</Param><Param>0x8007054B</Param><Param>SCOM2019</Param></Params><EventData><DataItem type=\"System.XmlData\" time=\"2022-05-09T10:42:50.5332957-07:00\" sourceHealthServiceId=\"0B30EB2E-007F-4C9E-21DC-639D4446B102\"><EventData xmlns=\"http://schemas.microsoft.com/win/2004/08/events/event\"><Data>NXLOG</Data><Data>vicente.munoz</Data><Data>The specified domain either does not exist or could not be contacted.</Data><Data>0x8007054B</Data><Data>SCOM2019</Data></EventData></DataItem></EventData><EventDisplayNumber>7016</EventDisplayNumber><EventDescription><![CDATA[The Health Service cannot verify the future validity of the RunAs account NXLOG\\service-account for management group SCOM2019 due to an error retrieving information from Active Directory (for Domain Accounts) or the local security authority (for Local Accounts).  The error is The specified domain either does not exist or could not be contacted.(0x8007054B).\r\n]]></EventDescription><Keywords>36028797018963968</Keywords></DataItem>",
  "AlertParameters": "<AlertParameters></AlertParameters>",
  "EventReceivedTime": "2022-05-09T15:48:14.691026-07:00",
  "SourceModuleName": "scom_alerts",
  "SourceModuleType": "im_odbc"
}

Disclaimer

While we endeavor to keep the information in our guides up to date and correct, NXLog makes no representations or warranties of any kind, express or implied about the completeness, accuracy, reliability, suitability, or availability of the content represented here. We update our screenshots and instructions on a best-effort basis.

The accurateness of the content was tested and proved to be working in our lab environment at the time of the last revision with the following software versions:

NXLog Agent version 5.5.7535
Windows Server 2019
Microsoft System Center Operations Manager 2019
Microsoft SQL Server 2017

Last revision: 9 May 2022