Unable to retrieve XML tags which as mutliple attributes

[asoma0707]

Hi,

I am novice to splunk and trying to learn explore things in it. Currently I am stuck with one problem while extracting the values from XML.

Here is the sample xml format from splunk message

2020:04:15:02:03:54.643 INFO xyz NOFMT 123456778 tomcat-http--105 10123456778 logged message:
<test> <dept id="0" value="Facilities"/> <dept id="1" value="Support"/> <dept id="2" value="IT" group="test"/></test>

I need to extract the xml message and trying to compare the values based on the dept ID.
for eg:
where id=0 and LIKE(value,"%facilities%") OR id=1 and LIKE(value,"%IT%")

Need to filter the messages based on above condition.

While, I am trying to extract the ID and values but results are getting cluttered and not showing the desired results.

Please help me in this regard.

[kamlesh_vaghela]

@asoma0707

You can use rex command for the extraction of desired values and then you can process further. Can you please try below search?

YOUR_SEARCH | rex field=_raw "message:(?<message>.*)" | eval _raw=message | spath | rename test.dept{@*} as * | table message id value

Sample Search:

| makeresults | eval _raw="2020:04:15:02:03:54.643 INFO xyz NOFMT 123456778 tomcat-http--105 10123456778 logged message:<test> <dept id=\"0\" value=\"Facilities\"/> <dept id=\"1\" value=\"Support\"/> </test>"| rex field=_raw "message:(?<message>.*)" | eval _raw=message | spath | rename test.dept{@*} as * | table message id value

This will give you message, id and value fields. As your XML has multiple dept values, id and value fields will have multivalued fields.

If you want to have dept id and value in separate rows then just append below search.

| eval tmp=mvzip(id,value) | mvexpand tmp | eval id=mvindex(split(tmp,","),0), value=mvindex(split(tmp,","),1) | table message id value

Sample Search:

| makeresults | eval _raw="2020:04:15:02:03:54.643 INFO xyz NOFMT 123456778 tomcat-http--105 10123456778 logged message:<test> <dept id=\"0\" value=\"Facilities\"/> <dept id=\"1\" value=\"Support\"/> </test>"| rex field=_raw "message:(?<message>.*)" | eval _raw=message | spath | rename test.dept{@*} as * | eval tmp=mvzip(id,value) | mvexpand tmp | eval id=mvindex(split(tmp,","),0), value=mvindex(split(tmp,","),1) | table message id value

Updated Answer:

YOUR_SEARCH | rex field=_raw "<test>(?<message>[\s\S]*?)<\/test>" max_match=0 | rex field=message mode=sed "s/(\/>([\s])<dept|\/>()<dept)/\/>|<dept/g" | eval message=split(message,"|") |mvexpand message | eval _raw=message | spath | rename dept{@*} as * | table message id value group

Sample Search:

| makeresults 
| eval _raw="2020:04:15:02:03:54.643 INFO xyz NOFMT 123456778 tomcat-http--105 10123456778 logged message:<test> <dept id=\"0\" value=\"Facilities\"/> <dept id=\"1\" value=\"Support\"/><dept id=\"2\" value=\"IT\" group=\"test\"/> </test>" 
| rex field=_raw "<test>(?<message>[\s\S]*?)<\/test>" max_match=0 | rex field=message mode=sed "s/(\/>([\s])<dept|\/>()<dept)/\/>|<dept/g" | eval message=split(message,"|") |mvexpand message | eval _raw=message | spath | rename dept{@*} as * | table message id value group

Thanks

[asoma0707]

Thank you for your reply.
This solution works perfectly. After running the query, I found another use case of xml:



Now, group is additional attribute that showing in few of the xml tags. Could you please help in extract "group" as well.

desired Output like below:

deptid value group
0 Facilities

1 Support
2 IT test

Thanks in advance.

[asoma0707]

somehow, new sample xml is not showing up in the reply. Hence, I updated the sample xml in original question. Please refer to that

[kamlesh_vaghela]

@asoma0707

I have updated my answer. Can you please try and confirm?

[richgalloway]

What have you tried so far?