Reporting

Extract Table in Event

RobertRi
Communicator

Hi Community!

I have a problem to extract a table in an XML event.

The data looks like this

 

 

<data type="info" text="sales:
    VW;1;30.000;
    Bentley;1;70.000;
    Seat;1;15.000;
   Dacia;1;10.000;
   Fiat;1;20.000;
">
<customer>Mr.X</customer>
<time>2020-11-28 16:21:00</time>
</data>

 

 



Now I want to have the fields for cartype, quantity and price (VW;1;30.000;).
So that I can summarize the whole sellings from one day.
Could you please help me with that?

Thank you very much!
Rob

Labels (2)
0 Karma

RobertRi
Communicator

Thank you very much!

0 Karma

ITWhisperer
SplunkTrust
SplunkTrust
| spath path="data{@text}" output=sales
| rex field=sales max_match=0 "\n\s+(?<cartype>[^;]+);(?<quantity>\d+);(?<price>[^;]+);"

RobertRi
Communicator

HI ITWhisperer!

Thank you very much for your answer, whith it I have an overview about my data.

Now I have a second question, regarding the prices.
It seems if there are more than 1 matches, the data will be stored like an array in the field.
If I export it to an csv it looks like this:

VW,2,30.000,0000
25.000,0000,

I have tried to round the price to a 2 digit value after the comma, but this does not work.

How can I grab each item in the field and modify it with an eval command?

Regards
Robert

0 Karma

ITWhisperer
SplunkTrust
SplunkTrust

What does the XML look like in this instance?

0 Karma

RobertRi
Communicator

It's the same like above except that the prices has multiple decimal places after the comma.

<data type="info" text="sales:
    VW;1;30.000;
    Bentley;1;70.000,000000;
    Seat;1;15.000,000000;
   Dacia;1;10.000,000000;
   Fiat;1;20.000,000000;
">
<customer>Mr.X</customer>
<time>2020-11-28 16:21:00</time>
</data>
0 Karma

ITWhisperer
SplunkTrust
SplunkTrust
| spath path="data{@text}" output=sales
| rex field=sales max_match=0 "\n\s+(?<cartype>[^;]+);(?<quantity>\d+);(?<price>[^;,]+);"

Just add a comma to the exclusion list for the price.

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