topic How do you extract fields from an XML file? in Dashboards & Visualizations

How do you extract fields from an XML file?

varmamkm — Mon, 19 Nov 2018 11:53:32 GMT

sample data

<MAINNODE>
     <NODE1 ID="C1" DATE="2018-11-16 09:20:01">
        <NODE2>
            <NODE3 CODE="A1" AMOUNT="100"/>
            <NODE3 CODE="A2" AMOUNT="200"/>
            <NODE3 CODE="A3" AMOUNT="300"/>
        </NODE2>
    </NODE1>
    <NODE1 ID="C2" DATE="2018-11-16 09:20:01">
        <NODE2>
            <NODE3 CODE="A2" AMOUNT="100"/>
            <NODE3 CODE="A1" AMOUNT="200"/>
        </NODE2>
    </NODE1>
 </MAINNODE>

Can you please help me out to build a regular expression or any other method (ex: xpath) to get the rows as mentioned below.

ID="C1", DATE="2018-11-16 09:20:01", CODE="A1", AMOUNT="100"
ID="C1", DATE="2018-11-16 09:20:01", CODE="A2", AMOUNT="200"
ID="C1", DATE="2018-11-16 09:20:01", CODE="A3", AMOUNT="300"
ID="C2", DATE="2018-11-16 09:20:01", CODE="A2", AMOUNT="100"
ID="C2", DATE="2018-11-16 09:20:01", CODE="A1", AMOUNT="200"

Re: How do you extract fields from an XML file?

richgalloway — Mon, 19 Nov 2018 13:44:49 GMT

Why does it have to be a regular expression? Have you considered the xpath command?

Re: How do you extract fields from an XML file?

varmamkm — Mon, 19 Nov 2018 15:27:14 GMT

Thanks! i have solved this by using spath.. can you please validate the query below

index="xmlfile" | spath output=NODE3CODE path=NODE1.NODE2.NODE3{@CODE} | spath output=NODE3AMOUNT path=NODE1.NODE2.NODE3{@AMOUNT} | eval x=mvzip(NODE3CODE, NODE3AMOUNT) | mvexpand x | eval x = split(x,",") | eval NODE3CODE=mvindex(x,0) | eval NODE3AMOUNT=mvindex(x,1) | table DATE ID NODE3CODE NODE3AMOUNT

Re: How do you extract fields from an XML file?

akocak — Mon, 19 Nov 2018 17:27:39 GMT

You can obviously do all sort of things during index-time. However, if search time, this app probably has everything you need:

https://splunkbase.splunk.com/app/455/

if you had this app, your search would look like
index="xmlfile" | xmlkv

if you need nested xml to be extracted,
index="xmlfile" |xmlkvrecursive

as mentioned, spath or rex would work on this task too.