Getting Data In

stats function on json data

Explorer

Hi,

I have an application that logs in json format using arrays. I want to do stats function on the elements in the array but cannot figure out how.

Log file:

{ "timestamp": "2013-01-04 09:15:54","Data":{"sample": 1, "objects" : [ { "id" : "a", "value":55 }, { "id" : "b", "value":77 }, { "id" : "c", "value":99 } ] } }
{ "timestamp": "2013-01-04 09:17:34","Data":{"sample": 2, "objects" : [ { "id" : "a", "value":88 }, { "id" : "b", "value":717 }, { "id" : "c", "value":6 } ] } }
{ "timestamp": "2013-01-04 09:19:04","Data":{"sample": 3, "objects" : [ { "id" : "a", "value":456 }, { "id" : "b", "value":77 }, { "id" : "c", "value":1 } ] } }

The query using the indexes found by splunk:

sourcetype="testtest" | stats max(Data.objects{}.value) BY Data.objects{}.id 

results in 717 for all ids when 456,717,99 is expected

What I would like to achieve is creat a chart with 'sample' ox x-axis and 'value' for each 'id' on y-axis

Hope anyone can give me a hint. Thanks

Tags (1)
2 Solutions

SplunkTrust
SplunkTrust

I had a problem with something similar, found the answer here: http://splunk-base.splunk.com/answers/63559/multiple-events-and-multiple-key-value-pairs-one-being-t.... I think then, your search would be:

sourcetype=testest|rename Data.objects{}.value as value|rename Data.objects{}.id AS id|eval x=mvzip(value,id)|mvexpand x|eval x=split(x,",")|eval value = mvindex(x,0)|eval id = mvindex(x,1)|stats max(value) as MV by id

View solution in original post

Influencer

First up you need to tell splunk to split up the json object, so your search becomes :

sourcetype="testtest" | spath

Now each event has 2 multivalues fields that contain the ids and values for all objects in the event. You cant do stats on multivalue fields, so you need to 'expand' the multivalued fields into seperate events.

To do this, we 'zip' the 2 multivalued fields together then expand it, so add

... | eval mvfield=mvzip($Data.objects{}.id$,$Data.objects{}.value$) 
    | fields mvfield Data.sample
    | mvexpand mvfield 

This gives us 1 field with id and value seperated by a ","

Next we need to split this new field into id and value again, so add

... | makemv mvfield delim="," 
    | eval id=mvindex(mvfield,0)
    | eval value=mvindex(mvfield,1)

Then finally, create the chart

... | chart max(value) as max_value over Data.sample by id

Update

To include Data.sample. When you mvexpand a field, all the new events created inherit the other field values of the original event so

    | fields mvfield Data.sample
    | mvexpand mvfield 

Gives you X new events with Data.sample as a field

View solution in original post

Influencer

First up you need to tell splunk to split up the json object, so your search becomes :

sourcetype="testtest" | spath

Now each event has 2 multivalues fields that contain the ids and values for all objects in the event. You cant do stats on multivalue fields, so you need to 'expand' the multivalued fields into seperate events.

To do this, we 'zip' the 2 multivalued fields together then expand it, so add

... | eval mvfield=mvzip($Data.objects{}.id$,$Data.objects{}.value$) 
    | fields mvfield Data.sample
    | mvexpand mvfield 

This gives us 1 field with id and value seperated by a ","

Next we need to split this new field into id and value again, so add

... | makemv mvfield delim="," 
    | eval id=mvindex(mvfield,0)
    | eval value=mvindex(mvfield,1)

Then finally, create the chart

... | chart max(value) as max_value over Data.sample by id

Update

To include Data.sample. When you mvexpand a field, all the new events created inherit the other field values of the original event so

    | fields mvfield Data.sample
    | mvexpand mvfield 

Gives you X new events with Data.sample as a field

View solution in original post

Explorer

Thanks, this gives the expected max values. What I actually want is "... | chart value over sample by id" . I'll play around with the mv-commands and see what I can do 🙂

0 Karma

SplunkTrust
SplunkTrust

I had a problem with something similar, found the answer here: http://splunk-base.splunk.com/answers/63559/multiple-events-and-multiple-key-value-pairs-one-being-t.... I think then, your search would be:

sourcetype=testest|rename Data.objects{}.value as value|rename Data.objects{}.id AS id|eval x=mvzip(value,id)|mvexpand x|eval x=split(x,",")|eval value = mvindex(x,0)|eval id = mvindex(x,1)|stats max(value) as MV by id

View solution in original post

State of Splunk Careers

Access the Splunk Careers Report to see real data that shows how Splunk mastery increases your value and job satisfaction.

Find out what your skills are worth!