Getting Data In
Highlighted

Create Table from json data

Motivator

Hi

I have a json input that has the following format:
{
"body": {
"1": {
"dataId": 1,
"first": "Mihail",
"address": "Street b"
},
"3": {
"dataId": 3,
"first": "Mickey",
"address": "Street f"
},
"5": {
"dataId": 5,
"first": "Adam",
"address": "Street g"
},
"9": {
"dataId": 1,
"first": "John",
"address": "Street b"
}
}
}

I d like to create a Table from that data as follows (sorry for the ugly Table representation):

| dataID | first | address |
|------------|------------|----------|
| 1 | Mihail | street b |
| 3 | Mickey | street f |

The field names are variable because of the json structure and contain the changing id

alt text

Is there a way to make multivalue fields and use mvexpand somehow.

Regards
Chris

Tags (1)
0 Karma
Highlighted

Re: Create Table from json data

Legend

@chris can you explain what you mean by making multivalue fields. If you can add an example of the output you need that would be quite helpful for us to assist.




| eval message="Happy Splunking!!!"


0 Karma
Highlighted

Re: Create Table from json data

Motivator

Hi, i just want to get a table with the following headers: dataId,first,address (I did not manage to render a proper table using markdown or html). I thought that I will probably have multivalu fields at some point either containing dataId,first and address or one for each column of the table. I do not know how to parse the json so i do not end up having individual fields extracted. If there is a way to avoid mv fields I'm happy with that solution.

0 Karma
Highlighted

Re: Create Table from json data

Super Champion

have you tried using |spath to format this data?
http://docs.splunk.com/Documentation/SplunkCloud/6.6.1/SearchReference/Spath

0 Karma
Highlighted

Re: Create Table from json data

Legend

Try the following run anywhere search

| makeresults
| eval _raw="{
\"body\": {
\"1\": {
\"dataId\": 1,
\"first\": \"Mihail\",
\"address\": \"Street b\"
},
\"3\": {
\"dataId\": 3,
\"first\": \"Mickey\",
\"address\": \"Street f\"
},
\"5\": {
\"dataId\": 5,
\"first\": \"Adam\",
\"address\": \"Street g\"
},
\"9\": {
\"dataId\": 1,
\"first\": \"John\",
\"address\": \"Street b\"
}
}
}"
| spath
| table body*
| transpose column_name="field"
| rename "row 1" as "value"
| eval field=split(field,".")
| eval counter=mvindex(field,1)
| eval field=mvindex(field,2)
| table counter value
| stats list(value) as value by counter
| fields - counter
| eval address=mvindex(value,0)
| eval dataId=mvindex(value,1)
| eval first=mvindex(value,2)
| fields - value

Based on the screenshot, you already have query till | table body*, so try commands after that starting with transpose. Try this out and confirm. Also wait for others to answer as there might be a better way!


Edit by chris:

This is another possibility:

| makeresults
| eval _raw="{
\"body\": {
\"1\": {
\"dataId\": 1,
\"first\": \"Mihail\",
\"address\": \"Street b\"
},
\"3\": {
\"dataId\": 3,
\"first\": \"Mickey\",
\"address\": \"Street f\"
},
\"5\": {
\"dataId\": 5,
\"first\": \"Adam\",
\"address\": \"Street g\"
},
\"9\": {
\"dataId\": 1,
\"first\": \"John\",
\"address\": \"Street b\"
}
}
}"
| spath
| table body* 
| foreach body.*.* [eval <<MATCHSEG2>>=mvappend(<<MATCHSEG2>>,'<<FIELD>>')]
| eval line=mvzip(mvzip(first,address,":"),dataId,":") 
| mvexpand line 
|  eval x = split(line,":") 
|  eval first=mvindex(x,0) 
|  eval address=mvindex(x,1) 
|  eval dataId=mvindex(x,2) 
|  table first,address,dataId



| eval message="Happy Splunking!!!"


Highlighted

Re: Create Table from json data

Motivator

Thanks a lot, I'll leave the question open for a bit, to see if other solutions pop up.

0 Karma
Highlighted

Re: Create Table from json data

SplunkTrust
SplunkTrust

Here's another way to use the foreach, that doesn't hard code the field names ...

 | spath
 | foreach body.*.* [eval data<<MATCHSEG1>>=coalesce(data<<MATCHSEG1>>,"")."!!!!<<MATCHSEG2>>=\"".'<<FIELD>>'."\""]
 | foreach data* [eval alldata=mvappend(alldata,substr(<<FIELD>>,5,len(<<FIELD>>)-4))]
 | table alldata 
 | streamstats count as recno
 | mvexpand alldata
 | streamstats count as recno2 
 | makemv delim="!!!!" alldata
 | mvexpand alldata
 | rex field=alldata "^(?<fieldname>[^=]+)=(?<fieldvalue>.+)"
 | eval {fieldname}=fieldvalue
 | fields - alldata fieldname fieldvalue
 | stats values(*) as * by recno recno2
Highlighted

Re: Create Table from json data

SplunkTrust
SplunkTrust

Here's another way.

| makeresults
 | eval _raw="{
 \"body\": {
 \"1\": {
 \"dataId\": 1,
 \"first\": \"Mihail\",
 \"address\": \"Street b\"
 },
 \"3\": {
 \"dataId\": 3,
 \"first\": \"Mickey\",
 \"address\": \"Street f\"
 },
 \"5\": {
 \"dataId\": 5,
 \"first\": \"Adam\",
 \"address\": \"Street g\"
 },
 \"9\": {
 \"dataId\": 9,
 \"first\": \"John\",
 \"address\": \"Street b\"
 }
 }
 }"
 | spath
 | table body*
 | eval recno=1
 | untable recno name value
 | rex field=name "^body\.(?<temp>\d+)\.(?<fieldname>.*)"
 | xyseries temp fieldname value
 | table dataId first address

View solution in original post

Highlighted

Re: Create Table from json data

Legend

@DalJeanis, I was waiting for your answer 🙂
@chris please try out this answer and confirm.




| eval message="Happy Splunking!!!"


Highlighted

Re: Create Table from json data

Motivator

Great this is the best solution so far. It think if you replace the last table command with "| fields - temp" the names of the final columns are not needed in the search language and the search is more flexibel if any of the columns change in the json. Then of course if only a subset of the columns is required table will help.

0 Karma