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
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*
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
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
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
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.
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
I'm not familiar enough with spath to answer.