Help get SCOM values from denormalized SQL XML datatype

I am trying to get a simple table view to match to events in windows event logs and Service center managment packs. This does not need to be in a single select statement.


Select
  [DisplayName]
,DataSourceXML.query('/Rule/Expression/And/Expression/SimpleExpression/ValueExpression/Value') as [eventid]
From
  dbo.table1

returns all value elements at that level. I only want the integer value where xpathQueryType type='initeger'  
Please review the XML Contents of two sample rows.
Becuase of the content in Snippet ID=8224550
There should be one row with eventid 5341
The Row from the table has snippet Snippet ID=8224551
This should return 12 rows with eventid values of 4956, 4957, and 5165 through  5174.

Multiple inserts into a temp table is fine since we have a nest issue
  <Expression>
    <And>
      <Expression>
        <Or>
          <Expression>
Please Help


 






<Rule>
  <ComputerName>$Target/Host/Property[Type="MSWL!Microsoft.Windows.Computer"]/NetworkName$</ComputerName>
  <LogName>Application</LogName>
  <Expression>
    <And>
      <Expression>
        <SimpleExpression>
          <ValueExpression>
            <XPathQuery Type="Integer">EventDisplayNumber</XPathQuery>
          </ValueExpression>
          <Operator>Equal</Operator>
          <ValueExpression>
            <Value>5341</Value>
          </ValueExpression>
        </SimpleExpression>
      </Expression>
      <Expression>
        <SimpleExpression>
          <ValueExpression>
            <XPathQuery Type="String">PublisherName</XPathQuery>
          </ValueExpression>
          <Operator>Equal</Operator>
          <ValueExpression>
            <Value>Office SharePoint Server</Value>
          </ValueExpression>
        </SimpleExpression>
      </Expression>
    </And>
  </Expression>
</Rule>

Open in new window

<Rule>
  <ComputerName>$Target/Host/Property[Type="MSWL!Microsoft.Windows.Computer"]/NetworkName$</ComputerName>
  <LogName>Application</LogName>
  <Expression>
    <And>
      <Expression>
        <Or>
          <Expression>
            <SimpleExpression>
              <ValueExpression>
                <XPathQuery Type="Integer">EventDisplayNumber</XPathQuery>
              </ValueExpression>
              <Operator>Equal</Operator>
              <ValueExpression>
                <Value>4956</Value>
              </ValueExpression>
            </SimpleExpression>
          </Expression>
          <Expression>
            <SimpleExpression>
              <ValueExpression>
                <XPathQuery Type="Integer">EventDisplayNumber</XPathQuery>
              </ValueExpression>
              <Operator>Equal</Operator>
              <ValueExpression>
                <Value>4957</Value>
              </ValueExpression>
            </SimpleExpression>
          </Expression>
          <Expression>
            <SimpleExpression>
              <ValueExpression>
                <XPathQuery Type="Integer">EventDisplayNumber</XPathQuery>
              </ValueExpression>
              <Operator>Equal</Operator>
              <ValueExpression>
                <Value>5165</Value>
              </ValueExpression>
            </SimpleExpression>
          </Expression>
          <Expression>
            <SimpleExpression>
              <ValueExpression>
                <XPathQuery Type="Integer">EventDisplayNumber</XPathQuery>
              </ValueExpression>
              <Operator>Equal</Operator>
              <ValueExpression>
                <Value>5166</Value>
              </ValueExpression>
            </SimpleExpression>
          </Expression>
          <Expression>
            <SimpleExpression>
              <ValueExpression>
                <XPathQuery Type="Integer">EventDisplayNumber</XPathQuery>
              </ValueExpression>
              <Operator>Equal</Operator>
              <ValueExpression>
                <Value>5167</Value>
              </ValueExpression>
            </SimpleExpression>
          </Expression>
          <Expression>
            <SimpleExpression>
              <ValueExpression>
                <XPathQuery Type="Integer">EventDisplayNumber</XPathQuery>
              </ValueExpression>
              <Operator>Equal</Operator>
              <ValueExpression>
                <Value>5168</Value>
              </ValueExpression>
            </SimpleExpression>
          </Expression>
          <Expression>
            <SimpleExpression>
              <ValueExpression>
                <XPathQuery Type="Integer">EventDisplayNumber</XPathQuery>
              </ValueExpression>
              <Operator>Equal</Operator>
              <ValueExpression>
                <Value>5169</Value>
              </ValueExpression>
            </SimpleExpression>
          </Expression>
          <Expression>
            <SimpleExpression>
              <ValueExpression>
                <XPathQuery Type="Integer">EventDisplayNumber</XPathQuery>
              </ValueExpression>
              <Operator>Equal</Operator>
              <ValueExpression>
                <Value>5170</Value>
              </ValueExpression>
            </SimpleExpression>
          </Expression>
          <Expression>
            <SimpleExpression>
              <ValueExpression>
                <XPathQuery Type="Integer">EventDisplayNumber</XPathQuery>
              </ValueExpression>
              <Operator>Equal</Operator>
              <ValueExpression>
                <Value>5171</Value>
              </ValueExpression>
            </SimpleExpression>
          </Expression>
          <Expression>
            <SimpleExpression>
              <ValueExpression>
                <XPathQuery Type="Integer">EventDisplayNumber</XPathQuery>
              </ValueExpression>
              <Operator>Equal</Operator>
              <ValueExpression>
                <Value>5172</Value>
              </ValueExpression>
            </SimpleExpression>
          </Expression>
          <Expression>
            <SimpleExpression>
              <ValueExpression>
                <XPathQuery Type="Integer">EventDisplayNumber</XPathQuery>
              </ValueExpression>
              <Operator>Equal</Operator>
              <ValueExpression>
                <Value>5173</Value>
              </ValueExpression>
            </SimpleExpression>
          </Expression>
          <Expression>
            <SimpleExpression>
              <ValueExpression>
                <XPathQuery Type="Integer">EventDisplayNumber</XPathQuery>
              </ValueExpression>
              <Operator>Equal</Operator>
              <ValueExpression>
                <Value>5174</Value>
              </ValueExpression>
            </SimpleExpression>
          </Expression>
        </Or>
      </Expression>
      <Expression>
        <SimpleExpression>
          <ValueExpression>
            <XPathQuery Type="String">PublisherName</XPathQuery>
          </ValueExpression>
          <Operator>Equal</Operator>
          <ValueExpression>
            <Value>Office SharePoint Server</Value>
          </ValueExpression>
        </SimpleExpression>
      </Expression>
    </And>
  </Expression>
</Rule>

Open in new window

<Rule>
  <ComputerName>$Target/Host/Property[Type="MSWL!Microsoft.Windows.Computer"]/NetworkName$</ComputerName>
  <LogName>Application</LogName>
  <Expression>
    <And>
      <Expression>
        <Or>
          <Expression>
            <SimpleExpression>
              <ValueExpression>
                <XPathQuery Type="Integer">EventDisplayNumber</XPathQuery>
              </ValueExpression>
              <Operator>Equal</Operator>
              <ValueExpression>
                <Value>4956</Value>
              </ValueExpression>
            </SimpleExpression>
          </Expression>
          <Expression>
            <SimpleExpression>
              <ValueExpression>
                <XPathQuery Type="Integer">EventDisplayNumber</XPathQuery>
              </ValueExpression>
              <Operator>Equal</Operator>
              <ValueExpression>
                <Value>4957</Value>
              </ValueExpression>
            </SimpleExpression>
          </Expression>
          <Expression>
            <SimpleExpression>
              <ValueExpression>
                <XPathQuery Type="Integer">EventDisplayNumber</XPathQuery>
              </ValueExpression>
              <Operator>Equal</Operator>
              <ValueExpression>
                <Value>5165</Value>
              </ValueExpression>
            </SimpleExpression>
          </Expression>
          <Expression>
            <SimpleExpression>
              <ValueExpression>
                <XPathQuery Type="Integer">EventDisplayNumber</XPathQuery>
              </ValueExpression>
              <Operator>Equal</Operator>
              <ValueExpression>
                <Value>5166</Value>
              </ValueExpression>
            </SimpleExpression>
          </Expression>
          <Expression>
            <SimpleExpression>
              <ValueExpression>
                <XPathQuery Type="Integer">EventDisplayNumber</XPathQuery>
              </ValueExpression>
              <Operator>Equal</Operator>
              <ValueExpression>
                <Value>5167</Value>
              </ValueExpression>
            </SimpleExpression>
          </Expression>
          <Expression>
            <SimpleExpression>
              <ValueExpression>
                <XPathQuery Type="Integer">EventDisplayNumber</XPathQuery>
              </ValueExpression>
              <Operator>Equal</Operator>
              <ValueExpression>
                <Value>5168</Value>
              </ValueExpression>
            </SimpleExpression>
          </Expression>
          <Expression>
            <SimpleExpression>
              <ValueExpression>
                <XPathQuery Type="Integer">EventDisplayNumber</XPathQuery>
              </ValueExpression>
              <Operator>Equal</Operator>
              <ValueExpression>
                <Value>5169</Value>
              </ValueExpression>
            </SimpleExpression>
          </Expression>
          <Expression>
            <SimpleExpression>
              <ValueExpression>
                <XPathQuery Type="Integer">EventDisplayNumber</XPathQuery>
              </ValueExpression>
              <Operator>Equal</Operator>
              <ValueExpression>
                <Value>5170</Value>
              </ValueExpression>
            </SimpleExpression>
          </Expression>
          <Expression>
            <SimpleExpression>
              <ValueExpression>
                <XPathQuery Type="Integer">EventDisplayNumber</XPathQuery>
              </ValueExpression>
              <Operator>Equal</Operator>
              <ValueExpression>
                <Value>5171</Value>
              </ValueExpression>
            </SimpleExpression>
          </Expression>
          <Expression>
            <SimpleExpression>
              <ValueExpression>
                <XPathQuery Type="Integer">EventDisplayNumber</XPathQuery>
              </ValueExpression>
              <Operator>Equal</Operator>
              <ValueExpression>
                <Value>5172</Value>
              </ValueExpression>
            </SimpleExpression>
          </Expression>
          <Expression>
            <SimpleExpression>
              <ValueExpression>
                <XPathQuery Type="Integer">EventDisplayNumber</XPathQuery>
              </ValueExpression>
              <Operator>Equal</Operator>
              <ValueExpression>
                <Value>5173</Value>
              </ValueExpression>
            </SimpleExpression>
          </Expression>
          <Expression>
            <SimpleExpression>
              <ValueExpression>
                <XPathQuery Type="Integer">EventDisplayNumber</XPathQuery>
              </ValueExpression>
              <Operator>Equal</Operator>
              <ValueExpression>
                <Value>5174</Value>
              </ValueExpression>
            </SimpleExpression>
          </Expression>
        </Or>
      </Expression>
      <Expression>
        <SimpleExpression>
          <ValueExpression>
            <XPathQuery Type="String">PublisherName</XPathQuery>
          </ValueExpression>
          <Operator>Equal</Operator>
          <ValueExpression>
            <Value>Office SharePoint Server</Value>
          </ValueExpression>
        </SimpleExpression>
      </Expression>
    </And>
  </Expression>
</Rule>

Open in new window

LVL 10
BanthorAsked:
Who is Participating?
 
Kevin CrossChief Technology OfficerCommented:
Hi. See if this XPath will do:

'/Rule//Expression/SimpleExpression[ValueExpression/XPathQuery[@Type="Integer"]="EventDisplayNumber"]/ValueExpression/Value'

- Grab any node below /Rule that has an Expression/SimpleExpression node with a ValueExpression/XPathQuery element of Type="Integer" whose value is "EventDisplayNumber"
- From there grab the ValueExpression/Value element.

See attached code snippet for tested example using first two code snippets above. Was not sure if the third was just a duplicate as you only mentioned the two. I get 13 rows, which matches your question comment; therefore, I did not test third.

Kevin
;WITH dbo_table1(DisplayName, DataSourceXML) AS (
SELECT 'Event1',
CONVERT(XML, 
'<Rule>
  <ComputerName>$Target/Host/Property[Type="MSWL!Microsoft.Windows.Computer"]/NetworkName$</ComputerName>
  <LogName>Application</LogName>
  <Expression>
    <And>
      <Expression>
        <SimpleExpression>
          <ValueExpression>
            <XPathQuery Type="Integer">EventDisplayNumber</XPathQuery>
          </ValueExpression>
          <Operator>Equal</Operator>
          <ValueExpression>
            <Value>5341</Value>
          </ValueExpression>
        </SimpleExpression>
      </Expression>
      <Expression>
        <SimpleExpression>
          <ValueExpression>
            <XPathQuery Type="String">PublisherName</XPathQuery>
          </ValueExpression>
          <Operator>Equal</Operator>
          <ValueExpression>
            <Value>Office SharePoint Server</Value>
          </ValueExpression>
        </SimpleExpression>
      </Expression>
    </And>
  </Expression>
</Rule>'
)
UNION ALL SELECT 'Event2',
CONVERT(XML,
'<Rule>
  <ComputerName>$Target/Host/Property[Type="MSWL!Microsoft.Windows.Computer"]/NetworkName$</ComputerName>
  <LogName>Application</LogName>
  <Expression>
    <And>
      <Expression>
        <Or>
          <Expression>
            <SimpleExpression>
              <ValueExpression>
                <XPathQuery Type="Integer">EventDisplayNumber</XPathQuery>
              </ValueExpression>
              <Operator>Equal</Operator>
              <ValueExpression>
                <Value>4956</Value>
              </ValueExpression>
            </SimpleExpression>
          </Expression>
          <Expression>
            <SimpleExpression>
              <ValueExpression>
                <XPathQuery Type="Integer">EventDisplayNumber</XPathQuery>
              </ValueExpression>
              <Operator>Equal</Operator>
              <ValueExpression>
                <Value>4957</Value>
              </ValueExpression>
            </SimpleExpression>
          </Expression>
          <Expression>
            <SimpleExpression>
              <ValueExpression>
                <XPathQuery Type="Integer">EventDisplayNumber</XPathQuery>
              </ValueExpression>
              <Operator>Equal</Operator>
              <ValueExpression>
                <Value>5165</Value>
              </ValueExpression>
            </SimpleExpression>
          </Expression>
          <Expression>
            <SimpleExpression>
              <ValueExpression>
                <XPathQuery Type="Integer">EventDisplayNumber</XPathQuery>
              </ValueExpression>
              <Operator>Equal</Operator>
              <ValueExpression>
                <Value>5166</Value>
              </ValueExpression>
            </SimpleExpression>
          </Expression>
          <Expression>
            <SimpleExpression>
              <ValueExpression>
                <XPathQuery Type="Integer">EventDisplayNumber</XPathQuery>
              </ValueExpression>
              <Operator>Equal</Operator>
              <ValueExpression>
                <Value>5167</Value>
              </ValueExpression>
            </SimpleExpression>
          </Expression>
          <Expression>
            <SimpleExpression>
              <ValueExpression>
                <XPathQuery Type="Integer">EventDisplayNumber</XPathQuery>
              </ValueExpression>
              <Operator>Equal</Operator>
              <ValueExpression>
                <Value>5168</Value>
              </ValueExpression>
            </SimpleExpression>
          </Expression>
          <Expression>
            <SimpleExpression>
              <ValueExpression>
                <XPathQuery Type="Integer">EventDisplayNumber</XPathQuery>
              </ValueExpression>
              <Operator>Equal</Operator>
              <ValueExpression>
                <Value>5169</Value>
              </ValueExpression>
            </SimpleExpression>
          </Expression>
          <Expression>
            <SimpleExpression>
              <ValueExpression>
                <XPathQuery Type="Integer">EventDisplayNumber</XPathQuery>
              </ValueExpression>
              <Operator>Equal</Operator>
              <ValueExpression>
                <Value>5170</Value>
              </ValueExpression>
            </SimpleExpression>
          </Expression>
          <Expression>
            <SimpleExpression>
              <ValueExpression>
                <XPathQuery Type="Integer">EventDisplayNumber</XPathQuery>
              </ValueExpression>
              <Operator>Equal</Operator>
              <ValueExpression>
                <Value>5171</Value>
              </ValueExpression>
            </SimpleExpression>
          </Expression>
          <Expression>
            <SimpleExpression>
              <ValueExpression>
                <XPathQuery Type="Integer">EventDisplayNumber</XPathQuery>
              </ValueExpression>
              <Operator>Equal</Operator>
              <ValueExpression>
                <Value>5172</Value>
              </ValueExpression>
            </SimpleExpression>
          </Expression>
          <Expression>
            <SimpleExpression>
              <ValueExpression>
                <XPathQuery Type="Integer">EventDisplayNumber</XPathQuery>
              </ValueExpression>
              <Operator>Equal</Operator>
              <ValueExpression>
                <Value>5173</Value>
              </ValueExpression>
            </SimpleExpression>
          </Expression>
          <Expression>
            <SimpleExpression>
              <ValueExpression>
                <XPathQuery Type="Integer">EventDisplayNumber</XPathQuery>
              </ValueExpression>
              <Operator>Equal</Operator>
              <ValueExpression>
                <Value>5174</Value>
              </ValueExpression>
            </SimpleExpression>
          </Expression>
        </Or>
      </Expression>
      <Expression>
        <SimpleExpression>
          <ValueExpression>
            <XPathQuery Type="String">PublisherName</XPathQuery>
          </ValueExpression>
          <Operator>Equal</Operator>
          <ValueExpression>
            <Value>Office SharePoint Server</Value>
          </ValueExpression>
        </SimpleExpression>
      </Expression>
    </And>
  </Expression>
</Rule>'
)
)
SELECT [DisplayName]
     , R.V.value('.', 'INT') AS [EventID]
FROM dbo_table1
CROSS APPLY DataSourceXML.nodes('/Rule//Expression/SimpleExpression[ValueExpression/XPathQuery[@Type="Integer"]="EventDisplayNumber"]/ValueExpression/Value') AS R(V)
;

Open in new window

0
 
BanthorAuthor Commented:
Outstanding! Yes the third was some posting difficulty.
I was able to apply the Cross apply without further modification.
Please Confirm that Brace here:
SimpleExpression[ValueExpression
finds any valueExpression under the SimpleExpression regardless of depth.
0
 
Kevin CrossChief Technology OfficerCommented:
SimpleExpression[ValueExpression ...] expects that ValueExpression will be immediately below SimpleExpression and is meant to test that there is a XPathQuery element with Type attribute equal to "Integer" immediately below a  ValueExpression that is itself a direct child of SimpleExpression.

Since it appeared the variation in depth was coming before the Expression node that contains the SImpleExpression, this bit of code handles that:

/Rule//Expression

It will pick up any Expression node at any depth of Rule that has a direct child of SimpleExpression with criteria specified above in the brace.

If additionally, the ValueExpression/XPathQuery bit can be at varying levels, then you will need to adjust to something like this:

'/Rule//Expression/SimpleExpression[//ValueExpression/XPathQuery[@Type="Integer"]="EventDisplayNumber"]/ValueExpression/Value'

Just note once you do, you might grab SimpleExpression at too high a level for ValueExpression/Value to grab the correct element if you only want EventID's. For example, when I test this on the data you provided, it picks up 'Office SharePoint Server' also and because of my conversion to INT, it fails. That may be what you want though. Another way that works for only EventID's is to use XPath to get to the ValueExpression/Value then in the test, use ../../ to get to the SimpleExpression parent and then from there use the // to indicate to search any level below that.

'/Rule//Expression/SimpleExpression/ValueExpression/Value[../..//ValueExpression/XPathQuery[@Type="Integer"]="EventDisplayNumber"]'

Hope that helps!

Best regards,

Kevin
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.