How to create a table where XML search input can obtain id and value?

[eandres]


<dashboard>
<search id="mySearch1">
<query>|makeresults </query>
</search>
<search id="mySearch2">
<query>* </query>
</search>


Goal: Need to make a table like:

id query
mySearch1 |makeresults
mySearch2 *
I tried using the spath command such as:
| spath input=eai:data output=ID path=dashboard.search{@id}
| spath input=eai:data output=query path=dashboard.search.query
But this only gives me the first id (same if I don't specify a path)

Then if I use the rex command to pull the id, I have 2 ids and 2 queries with no way to match which goes with which.

Regular expressions for XML isn't a great idea to pull the whole <search id="searchID"> ... </search> tag into a field but the spath command isn't giving me that option. Any ideas?

[niketn · Accepted solution]

@eandres, I am hoping you are getting dashboard simple XML code using Splunk REST API. I am not sure why you want to apply spath first and then rex.

Option 1: Using Rex Command

---

You can try the following run anywhere example where dashboard code provided as per the question is assigned to dummy field called data.

| makeresults
| eval data="<dashboard> 
   <search id=\"mySearch1\">
     <query>|makeresults </query>
   </search>
   <search id=\"mySearch2\">
     <query>*</query>
    </search>
</dashboard>"
| rex field=data "\<search\sid=\"(?<search_id>[^\"]+)\">\s+\<query\>(?<query>[^\<]+)\<\/query\>" max_match=0
| eval searchData=mvzip(search_id,query,"###")
| mvexpand searchData
| eval searchData=split(searchData,"###")
| eval search_id=mvindex(searchData,0),query=mvindex(searchData,1)
| table search_id query

Once you test this out you can remove first two pipes i.e. | makeresults and | eval data with your current query with field name for Dashboard Simple XML code and make sure the field name matches with that used in | rex field=data command.

Option 2: Using Spath Command

---

If you want to stick to spath. Following is a run anywhere example on similar lines. Assuming Simple XML dashboard data is stored in field called data. You can change the input to specific field as per actual data:

| makeresults 
| eval data="<dashboard> 
   <search id=\"mySearch1\">
     <query>|makeresults </query>
   </search>
   <search id=\"mySearch2\">
     <query>*</query>
    </search>
</dashboard>" 
| spath input=data
| rename dashboard.search{@*} as search_*, dashboard.search.* as * 
| eval searchData=mvzip(search_id,query,"###") 
| mvexpand searchData 
| eval searchData=split(searchData,"###") 
| eval search_id=mvindex(searchData,0),query=mvindex(searchData,1) 
| table search_id query

Please try out and confirm!

[eandres]

Thanks, beautiful search! I used Option 2 because the tag may or may not have the id field (or others) in it.

Yes, I am using the REST API to pull the list of views, with the data in them. Trying to pull the 'configuration' of my Splunk instance and this is one of the data points that I need.

[niketn]

Great!!! You should also check out the Knowledge Object Explorer App by @martin_mueller

[eandres]

Thanks, will do!