Splunk Search

How do you create new fields from the current field values?

IRHM73
Motivator

Hi,

I wonder whether someone can help me please.

I'm using the following query to extract data from the raw JSON file to create a new field called myField, which I then expand to create separate values within the field rather than a continuous string:

real-time-information_wmf(RequestReceived) 
| rex field=detail.filterFields "\((?<myField>.*)\)" 
| eval test=split(myField,",") | mvexpand test
| stats values(test) as test by _time detail.serviceName

The query works except for the transforming commands section.

The current output is as per "Screenshot 1" and I'd like to have the output in "Screenshot 2"

I've tried using the chart command, stats as above with and without transpose and the foreach command but I can't get this to work.

Could someone look at this please and offer some guidance on where I've gone wrong.

Incidentally is there a way to create a table in these posts?

Many thanks and kind regards

Chris

alt text

0 Karma

vnravikumar
Champion

Hi

Try this

| makeresults 
| eval test="apiAvailableTimestamp,benefitsPayroll,directorsNIC,employeePensionContributions" 
| makemv delim="," test 
| mvexpand test 
| transpose 0 column_name=test 
| where test="test" 
| foreach row* 
    [ eval field<<MATCHSTR>> = '<<FIELD>>'] 
| fields - test row*
0 Karma

IRHM73
Motivator

Hi @vnravikumar ,

Thank you for coming back to me with this.

The problem is that this needs to have an element which separates the events. At the moment it's putting them all one one row and goes from Field1 Field10 Field 100, whereas I'd like them to be sequential if possible please?

Many thanks and kind regards

Chris

0 Karma

richgalloway
SplunkTrust
SplunkTrust

You may need to use some force to get the fields you want. Something like this:

`real-time-information_wmf(RequestReceived)` 
| rex field=detail.filterFields "\((?<myField>.*)\)" 
| eval test=split(myField,",")
| eval field1=mvindex(test, 0), field2=mvindex(test,1), field3=mvindex(test, 2), field4=mvindex(test, 3), field5=mvindex(test,4), field6=mvindex(test, 5), field7=mvindex(test, 6)
| table _time detail.serviceName field1 field2 field3 field4 field5 field6 field7
---
If this reply helps you, Karma would be appreciated.
0 Karma

IRHM73
Motivator

Hi @richgalloway .

Thank you for coming back to me and my apologies for not making this clear. But there are approx 80 fields.

Is there anyway that this could be done automatically. That's why I was trying to use the foreach. I'd also looked at using spath as well to create the new fields direct from the JSON file.

Kind Regards

Chris

0 Karma

richgalloway
SplunkTrust
SplunkTrust

Using rex to parse JSON is not the best idea unless you only need a few fields. For 80 fields you really need spath or re-evaluate how the data is onboarded.

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

IRHM73
Motivator

Hi @richgalloway.

Thank you for coming back to me with this.

I did look at spath, but forgive me, because this could be down to my lack of knowledge, but is there a way of using spath where it automatically extracts all the fields?

Many thanks and kind regards

Chris

0 Karma

richgalloway
SplunkTrust
SplunkTrust

I'm not familiar enough with spath to answer.

---
If this reply helps you, Karma would be appreciated.
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 ...