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.
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 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. For more information, consult the relevant ODBC documentation: 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. 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 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 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. |
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 own 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.
Starting with SQL Server 2016, the DATEDIFF_BIG T-SQL function can be used instead (see DATEDIFF_BIG (Transact-SQL) at MSDN).
<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>{
"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"
}{
"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"
}