NXLog User Guide

75. 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.

75.1. 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 currently either unsupported by Microsoft (requiring SQL triggers in the OM database and thus voiding the warranty) or are too performance heavy with little meaningful data to retrieve.

Note	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.

75.2. Collecting 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 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.

Note	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.

Example 344. Collecting Event 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 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).

nxlog.conf


  1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62

  <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": 1463035663720,
  "EventTime": "2016-05-12 06:47:43",
  "EventAddedTime": "2016-05-12 06:48:15",
  "WindowsID": 4776,
  "Source": "dc01.nxlog.local",
  "RuleName": "Windows log collection test",
  "EventData": "<DataItem type=\"System.XmlData\" time=\"2016-05-12T08:47:44.7224395+02:00\" sourceHealthServiceId=\"F767895D-A408-0F91-42A3-87565E1D9D85\"><EventData xmlns=\"http://schemas.microsoft.com/win/2004/08/events/event\"><Data Name=\"PackageName\">MICROSOFT_AUTHENTICATION_PACKAGE_V1_0</Data><Data Name=\"TargetUserName\">SCOM01$</Data><Data Name=\"Workstation\">SCOM01</Data><Data Name=\"Status\">0x0</Data></EventData></DataItem>",
  "EventParameters": "<Param>MICROSOFT_AUTHENTICATION_PACKAGE_V1_0</Param><Param>SCOM01$</Param><Param>SCOM01</Param><Param>0x0</Param>",
  "EventReceivedTime": "2016-05-12 10:28:50",
  "SourceModuleName": "in_scom_events",
  "SourceModuleType": "im_odbc"
}

Output Sample (Alert Log)

{
  "id": 1462887688220,
  "Alert Name": "Failed to Connect to Computer",
  "Category": "StateCollection",
  "Alert Description": "The computer {0} was not accessible.",
  "EventTime": "2016-05-10 13:41:28",
  "EventAddedTime": "2016-05-10 13:41:28",
  "Context": "<DataItem type=\"MonitorTaskDataType\" time=\"2016-05-10T15:41:28.1932994+02:00\" sourceHealthServiceId=\"00000000-0000-0000-0000-000000000000\"><StateChange><DataItem time=\"2016-05-10T15:41:25.5592943+02:00\" type=\"System.Health.MonitorStateChangeData\" sourceHealthServiceId=\"D53BAD42-4C93-6634-E610-BDC3E38ABD5B\" MonitorExists=\"true\" DependencyInstanceId=\"00000000-0000-0000-0000-000000000000\" DependencyMonitorId=\"00000000-0000-0000-0000-000000000000\"><ManagedEntityId>CC7109D1-9177-090D-AC3A-18781CFFF898</ManagedEntityId><EventOriginId>9B02AB65-FDB5-40AE-863F-6FAD232E06F9</EventOriginId><MonitorId>B59F78CE-C42A-8995-F099-E705DBB34FD4</MonitorId><ParentMonitorId>A6C69968-61AA-A6B9-DB6E-83A0DA6110EA</ParentMonitorId><HealthState>3</HealthState><OldHealthState>1</OldHealthState><TimeChanged>2016-05-10T15:41:25.5592943+02:00</TimeChanged><Context><DataItem type=\"System.Availability.StateData\" time=\"2016-05-10T15:41:25.5542835+02:00\" sourceHealthServiceId=\"D53BAD42-4C93-6634-E610-BDC3E38ABD5B\"><ManagementGroupId>{1457194C-D3B4-6685-5D3B-E4F7DAB158AD}</ManagementGroupId><HealthServiceId>72704AC7-4FDF-6006-1BB0-C74868E173D5</HealthServiceId><HostName>member2012r2-01.nxlog.local</HostName><Reachability ThruServer=\"false\"><State>0</State></Reachability></DataItem></Context></DataItem></StateChange><Diagnostic><DataItem type=\"System.PropertyBagData\" time=\"2016-05-10T15:41:25.6342865+02:00\" sourceHealthServiceId=\"D53BAD42-4C93-6634-E610-BDC3E38ABD5B\"><Property Name=\"StatusCode\" VariantType=\"8\">11003</Property><Property Name=\"ResponseTime\" VariantType=\"8\"></Property></DataItem></Diagnostic></DataItem>",
  "AlertParameters": "<AlertParameters><AlertParameter1>member2012r2-01.nxlog.local</AlertParameter1></AlertParameters>",
  "EventReceivedTime": "2016-05-12 10:33:38",
  "SourceModuleName": "in_scom_alerts",
  "SourceModuleType": "im_odbc",
  "AlertMessage": "member2012r2-01.nxlog.local"
}