Compare the Event Xml data in SCOM database

Question

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>

Answer 1

I have checked all answers. These Answers are good and nice to apply. Acrylic paint tubes

Answer 2

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.

Answer 3

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;

Answer 4

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