Compare the Event Xml data in SCOM database

Ratheesh Ayyamkulam 21 Reputation points
2020-09-09T12:10:03.967+00:00

Hi Team,

I'm having SCOM rule writing the events into DB, I need to filter the <data> value from the event data xml column.

If the error count value is greater than 5 need to create alert.

SQL query used to find the event data

select EventData FROM [OPSMGRDW].[Event].[vEventDetail] evdes
inner join Event.vEvent evid on evdes.EventOriginId = evid.EventOriginId
inner join dbo.vEventLoggingComputer evcomp on evid.LoggingComputerRowID = evcomp.EventLoggingComputerRowID

Event Data Structure
<EventData>
<DataItem type="System.XmlData" time="2020-08-17T13:16:02.5711926+01:00" sourceHealthServiceId="ADBCE-1111-bdfjhbdwjh-jhvbjhdf">
<EventData xmlns="http://schemas.microsoft.com/win/2004/08/events/event">
<Data>INFO: error found Received Output: count 1
</Data>
<Data>0</Data>
<Data>StdOut: INFO: error found </Data>
<Data>1</Data>
</EventData>
</DataItem>
</EventData>

Operations Manager
Operations Manager
A family of System Center products that provide infrastructure monitoring, help ensure the predictable performance and availability of vital applications, and offer comprehensive monitoring for datacenters and cloud, both private and public.
1,204 questions
SQL Server
SQL Server
A family of Microsoft relational database management and analysis systems for e-commerce, line-of-business, and data warehousing solutions.
8,571 questions
No comments
{count} votes

3 answers

Sort by: Most helpful
  1. CyrAz 5,166 Reputation points
    2020-09-09T13:40:44.51+00:00

    That's likely not the right way of achieving whatever you're trying to do.
    Could you be more specific about your actual goal?


  2. Yitzhak Khabinsky 20,016 Reputation points
    2020-09-09T15:46:36.527+00:00

    Here is how to count <Data>1</Data> in the XML.

    -- DDL and sample data population, start
    DECLARE @tbl TABLE (ID INT IDENTITY PRIMARY KEY, [EventData] XML);
    INSERT INTO @tbl ([EventData]) VALUES
    (N'<EventData>
        <DataItem type="System.XmlData" time="2020-08-17T13:16:02.5711926+01:00"
                  sourceHealthServiceId="ADBCE-1111-bdfjhbdwjh-jhvbjhdf">
            <EventData xmlns="http://schemas.microsoft.com/win/2004/08/events/event">;
                <Data>INFO: error found Received Output: count 1</Data>
                <Data>0</Data>
                <Data>StdOut: INFO: error found</Data>
                <Data>1</Data>
            </EventData>
        </DataItem>
    </EventData>');
    -- DDL and sample data population, end
    
    ;WITH XMLNAMESPACES ('http://schemas.microsoft.com/win/2004/08/events/event' AS ns1)
    SELECT ID
        , [EventData].value('count(/EventData/DataItem/ns1:EventData/ns1:Data[./text()="1"])','INT') AS [Counter]
    FROM @tbl;
    
    No comments

  3. AlexZhu-MSFT 3,616 Reputation points Microsoft Employee
    2020-09-10T01:27:11.377+00:00

    Hi,

    If I understand correctly, the data is stored in a field of the tables (in xml format) and we want to parse the xml to get the error count. If the error count is greater than 5, some actions, for example, generating an alert, may be taken.

    Please understand that operations manager is a real-time monitoring system. It measures the system periodically. If some value is beyond the threshold, it generates an alert and may probably send out a notification if we've configured that. For our problem, it's historical data. At such situation, we may consider using the sql query provided by YitzhakKhabinsky-0887 to export the result directly.

    Hope the above information helps.

    Alex Zhu


    If the response is helpful, please click "Accept Answer" and upvote it.

    No comments