Splunk Search

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

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

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

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

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, an upvote would be appreciated.
0 Karma

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

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, an upvote would be appreciated.
0 Karma

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

SplunkTrust
SplunkTrust

I'm not familiar enough with spath to answer.

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