Getting Data In

Json Query

david_martinez
Engager

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!

Tags (2)
0 Karma

to4kawa
Ultra Champion
| 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.

0 Karma

martin_mueller
SplunkTrust
SplunkTrust

In your json you once have a field "user", and once have a field "users". That can't be good.

0 Karma

martin_mueller
SplunkTrust
SplunkTrust

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

david_martinez
Engager

It was a mistake for copy-paste, sorry -> fixed. Thanks for the advice

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 ...