Dashboards & Visualizations

How do you extract fields from an XML file?

varmamkm
New Member

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"
0 Karma
1 Solution

akocak
Contributor

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.

View solution in original post

0 Karma

akocak
Contributor

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.

0 Karma

richgalloway
SplunkTrust
SplunkTrust

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

---
If this reply helps you, Karma would be appreciated.
0 Karma

varmamkm
New Member

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

0 Karma
Get Updates on the Splunk Community!

Index This | I am a number, but when you add ‘G’ to me, I go away. What number am I?

March 2024 Edition Hayyy Splunk Education Enthusiasts and the Eternally Curious!  We’re back with another ...

What’s New in Splunk App for PCI Compliance 5.3.1?

The Splunk App for PCI Compliance allows customers to extend the power of their existing Splunk solution with ...

Extending Observability Content to Splunk Cloud

Register to join us !   In this Extending Observability Content to Splunk Cloud Tech Talk, you'll see how to ...