topic Filtering values from a JSON file in Getting Data In

Filtering values from a JSON file

lain179 — Thu, 27 Feb 2014 01:53:31 GMT

I am trying to parse information from a json file, but am having difficulty doing this.
Here is my sample json file:

{ "message":"OK", "status":200, "responseEntity":[ { "counters":{ "SearchResult.close()":{ "name":"SearchResult.close()", "count":42, "max":9.0, "min":0.0 } }, "errors":{ }, "groupDescription":"Counters", "groupName":"Group01" }, { "counters":{ "SearchResult.close()":{ "name":"SearchResult.close()", "count":7, "max":8.0, "min":7.0 } }, "errors":{ }, "groupDescription":"Counters", "groupName":"Group2" }

I want to be able to filter the value for "SearchResult.close().count" for groupName "group2", however I am unable to do that. I tried

| spath path=responseEntity{}.groupName output=groupName | mvexpand groupName |

But when I filter by json_group, I still get ALL values(responseEntity{}.counters.SearchResult.close().count values would be both 42, and 7)

inputs.conf file

[monitor://\HOST01\groupInfo.json]

disabled = 0

followTail = false

host = HOST01

sourcetype = JSON Testing

crcSalt =

props.conf file

[JSON Testing]

TRUNCATE = 0

KV_MODE = json

Is there a way that I can filter by groupName, then only get values that are associated with that group name, for count, max, min etc? Or is there an issue with my json file itself?

Thank you

Re: Filtering values from a JSON file

bboe — Tue, 04 Mar 2014 01:44:15 GMT

There is a long and storied history with this kind of use case (breaking up json data in this way). Unfortunately, all of the solutions are as elegant as a drunken hippo in a bouncy castle.

Here's something that might work:

| spath output=groupName path=responseEntity{}.groupName | rename responseEntity{}.counters.SearchResult.close().count AS count | eval x=mvzip(groupName,count)|mvexpand x|eval x=split(x,",")|eval groupName = mvindex(x,0)|eval count = mvindex(x,1)| table groupName,count

What's going on here is we're zipping together the values (in order) of the two fields (with mvzip) then turning those into separate events based on the field (with mvexpand), then splitting those zipped fields into their original components (with split and mvindex).

Re: Filtering values from a JSON file

lain179 — Tue, 04 Mar 2014 18:12:27 GMT

This worked perfectly, and when i added | where groupName="Group2"
I was finally able to only get a single value!(Which is one of the things I was having a lot of trouble with)