stats function on json data

[perseger]

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

[alacercogitatus · Accepted solution]

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

[jonuwz · Accepted solution]

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

[perseger]

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 🙂

[alacercogitatus · Accepted solution]

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