topic Re: XML nested field issues in Splunk Search

XML nested field issues

rasty — Tue, 29 Sep 2020 19:06:08 GMT

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:

Re: XML nested field issues

niketn — Mon, 16 Apr 2018 09:46:06 GMT

@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

Re: XML nested field issues

rasty — Tue, 29 Sep 2020 19:06:11 GMT

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.

Re: XML nested field issues

niketn — Mon, 16 Apr 2018 13:57:42 GMT

@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

Re: XML nested field issues

rasty — Mon, 16 Apr 2018 16:35:14 GMT

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

Re: XML nested field issues

niketn — Mon, 16 Apr 2018 16:49:33 GMT

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 🙂

Re: XML nested field issues

rasty — Mon, 16 Apr 2018 17:07:28 GMT

Really, thank you so much niketnilay for everything 🙂