Hello,
I have the next json in splunk:
{
_data : {
services : [
{
id : "FB00000",
users : [
100,
122
]
},
{
id : "FB11111",
users : [
404,
797
]
}
],
socialNetwork : "FB"
},
_timestamp : "01-02-02013T01:00:04.582+0100",
_type : "ServiceReport"
}
I would like to know the query to generate a table with the following format:
ID USER
FB00000 100
FB00000 122
FB11111 404
FB11111 797
I tried with:
sourcetype="singleline_json" AND _type="ServiceReport"
| rename _data.services{}.users{} as USER
| rename _data.services{}.id as ID
| fields ID, USER
| mvexpand USER
| eval x=split(USER,",")
| eval USER = mvindex(x,0)
| table ID, USER
But it does not work.
Thanks in advance!
| makeresults
| eval _raw="{\"_data\":{\"services\":[{\"id\":\"FB00000\",\"users\":[100,122]},{\"id\":\"FB11111\",\"users\":[404,797]}],\"socialNetwork\":\"FB\"},\"_timestamp\":\"01-02-02013T01:00:04.582+0100\",\"_type\":\"ServiceReport\"}"
| spath path=_data.services{} output=data
| kv
| rename data.services{}.* as *
| stats count by data.socialNetwork timestamp type data
| spath input=data
| stats count values(*) as * by users{}
| rename users{} as users, data.socialNetwork as socialNetwork
| table id users socialNetwork timestamp type
mvexpand gives "mvexpand output will be truncated due to excessive memory usage
Because of this kind of problem,
I thought of an expansion method that doesn't use mvexpand
and mvzip
.
The point is that the field that becomes multivalue is extracted once and expanded without using mvexpand
by stats
.
In your json you once have a field "user", and once have a field "users". That can't be good.
I see. Your names in front of the colons also lack quotation marks to be valid JSON.
What exactly isn't working, what results are you getting?
Edit: Once you fix that you can do this:
... | spath path=_data.services{} output=services | mvexpand services | spath input=services | stats values(users{}) as users by id | mvexpand users
to get this:
id users
1 FB00000 100
2 FB00000 122
3 FB11111 404
4 FB11111 797
It was a mistake for copy-paste, sorry -> fixed. Thanks for the advice