XML nested field issues

[rasty]

Hi all,

I have a XML file like:

<CxXMLResult>
    <Query name="Stored_XSS">
        <Result NodeId="1">
        </Result>
        <Result  NodeId="2">
        </Result>
        <Result  NodeId="3">
        </Result>
    </Query>
<CxXMLResult>

So number of result tag actually represents number of vulnerability by Query.

When I search like : index="deneme_index6" CxXMLResult.Query{@name}=* | stats count(CxXMLResult.Query{@name})

Returns
CxXMLResult.Query{@name} | count
Stored_XSS 1

But actually, There are three Stored_XSS because there are three Result tag under the Query of Stored_XSS.

So I search like: index="deneme_index6" | spath output=result path=CxXMLResults.Query | mvexpand result | table result | spath input=result output=NodeID path=Result{@NodeId} | stats count(NodeID) by result

I can count Result tags under each Query separetly. But I lose Query's name. How can i add Query's name in my table.

My Splunk Screen like:

[niketn · Accepted solution]

@rasty please try the following answer with run anywhere example

| makeresults
| eval _raw="<CxXMLResult>
       <Query name=\"Stored_XSS\">
           <Result NodeId=\"1\">
           </Result>
           <Result  NodeId=\"2\">
           </Result>
           <Result  NodeId=\"3\">
           </Result>
       </Query>
       <Query name=\"Use_Of_Hardcoded_Password\">
           <Result NodeId=\"1\">
           </Result>
           <Result  NodeId=\"2\">
           </Result>
       </Query>
   <CxXMLResult>"
 | spath output=Query path="CxXMLResult.Query"
 | spath output=QueryName path="CxXMLResult.Query{@name}"
 | fields - _*
 | eval Data= mvzip(Query,QueryName)
 | fields Data
 | mvexpand Data
 | makemv delim="," Data
 | eval QueryName=mvindex(Data,1),Query=mvindex(Data,0)
 | fields - Data
 | rex field=Query "NodeId=\"(?<NodeId>[^\"]+)\"" max_match=0
 | eval count=mvcount(NodeId)
 | fields QueryName NodeId count

[rasty]

| spath output=Query path="CxXMLResults.Query"
  | spath output=QueryName path="CxXMLResults.Query{@name}"
  | fields - _*
  | eval Data= mvzip(Query,QueryName)
  | fields Data
  | mvexpand Data
  | makemv delim="</Result>," Data
  | eval QueryName=mvindex(Data,1),Query=mvindex(Data,0)
  | fields - Data
  | rex field=Query "NodeId=\"(?<NodeId>[^\"]+)\"" max_match=0
  | stats count(NodeId) by QueryName

This is work for me Thank you for all help niketnilay.

Can you explain steps ? Because I really want to learn everythings 🙂 I am new in my carrier

Regards..

[niketn]

Usually you should Start by adding only one pipe at time to see how it is working.

• The spath Query gets all the NodeIds under Query and then second spath gets their Names.
• The mvzip() function stitches the multivalued fields together one by one. By default it puts a comma 1, between the zipped values. You can specify optional third parameter for mvzip() to specify any other delimiter string.
• I had used makemv command to convert expanded data ti multivalue based on command delimeter field as stated above. Seems like </Result> is working for you.
• mvindex() function reads data from multivalue field based on its index starting from 0. In our case 1st index i.e. 0 is Query and 2nd Index i.e. 1 is Query Name.
• Finally I am using rexcommand to extract NodeId with max_match=0 to fetch unlimited results. If you have some upper limit to the NodeIds you can mention that number instead.

Refer to Splunk Documentation for multi value eval functions: http://docs.splunk.com/Documentation/Splunk/latest/SearchReference/MultivalueEvalFunctions

Hope this clarifies... Don't forget to up vote the comment/s that helped 🙂

[rasty]

Really, thank you so much niketnilay for everything 🙂

[niketn]

@rasty, following is a run anywhere example based on sample data/query provided. You will need query from spath onward. Please try out and confirm!

| makeresults
| eval _raw=" <CxXMLResult>
     <Query name=\"Stored_XSS\">
         <Result NodeId=\"1\">
         </Result>
         <Result  NodeId=\"2\">
         </Result>
         <Result  NodeId=\"3\">
         </Result>
     </Query>
 <CxXMLResult>"
 | spath output=QueryName path="CxXMLResult.Query{@name}"
 | spath output=QueryResults path="CxXMLResult.Query.Result{@NodeId}"
 | eval VulnerabilityCount=mvcount(QueryResults)
 | table QueryName VulnerabilityCount

[rasty]

Hi niketnilay,

it will not work as desired. Because for example,

XML like:

<CxXMLResult>
      <Query name="Stored_XSS">
          <Result NodeId="1">
          </Result>
          <Result  NodeId="2">
          </Result>
          <Result  NodeId="3">
          </Result>
      </Query>
      <Query name="Use_Of_Hardcoded_Password">
          <Result NodeId="1">
          </Result>
          <Result  NodeId="2">
          </Result>
          <Result  NodeId="3">
          </Result>
      </Query>
  <CxXMLResult>

So when I do | spath output=QueryResults path="CxXMLResult.Query.Result{@NodeId}"
| eval VulnerabilityCount=mvcount(QueryResults)
| table QueryName VulnerabilityCount VulnerabilityCount will take value of six. Because all of CxXMLResult in one event. But I want to Stored_XSS 3 and Use_Of_Hardcoded_Password 3.