topic Re: How to "group by" in JSON in Splunk Search

How to "group by" in JSON

anooshac — Mon, 03 Feb 2020 09:44:46 GMT

Hi all I have a json file like this,

{
 "NUM" : "#1",
 "TIME" : "1/27/2020  12:49:13",
 "STATUS" : "PASS",
 "DURATION" : "144",
"COMPONENTS":
[
  {
    "NAME" : "a",
    "ACCEPTEDCHANGESETS":
       [
"abc",  
"xyz",  
"efg",
       ],
    "NAME" : "a",
    "REJECTEDCHANGESETS":
      [
"pqr",
      ],
    "NAME" : "b",
    "ACCEPTEDCHANGESETS":
       [
"uop",
"ghf",
"yui"
]
}
]
}

How can I find the count of "ACCEPTEDCHANGESETS" by "NAME"?
Previously I had asked the same type of question and I tried referring that answer but I am not able to get a proper answer.
Can anyone please help me with this!!

Re: How to "group by" in JSON

kamlesh_vaghela — Mon, 03 Feb 2020 09:49:11 GMT

@anooshac

I think your provided JSON is invalid. Validated on https://jsonlint.com/. For quick help can you please share _raw event in code sample block ( see 101010 button) ?

Re: How to "group by" in JSON

anooshac — Mon, 03 Feb 2020 11:30:01 GMT

Hi @kamlesh_vaghela , sorry i missed out some brackets in that file.
{
"NUM": "#1",
"TIME": "1/27/2020 12:49:13",
"STATUS": "PASS",
"DURATION": "144",
"COMPONENTS": [{
"NAME": "a",
"ACCEPTEDCHANGESETS": [
"abc",
"xyz",
"efg"
],
"REJECTEDCHANGESETS": [
"pqr"
]
},
{
"NAME": "b",
"ACCEPTEDCHANGESETS": [
"uop",
"ghf",
"yui"
]
}
]
}
This is valid json file. How can i get those results?

Re: How to "group by" in JSON

jbrocks — Mon, 03 Feb 2020 11:47:11 GMT

If you have KV_MODE = JSON and your json is valid, you should have a field called "COMPONENTS{}.NAME" in your fields list. Then you can do:

index=yourindex  sourcetype=yoursourcetype| stats count by COMPONENTS{}.NAME

Re: How to "group by" in JSON

kamlesh_vaghela — Mon, 03 Feb 2020 12:48:27 GMT

@anooshac

Can you please try this?

YOUR_SEARCH | spath path=COMPONENTS{} output=data | mvexpand data | eval _raw=data| extract
| stats count(ACCEPTEDCHANGESETS{}) as Count by NAME

Sample Search:

| makeresults 
| eval _raw="{\"NUM\": \"#1\",\"TIME\": \"1/27/2020 12:49:13\",\"STATUS\": \"PASS\",\"DURATION\": \"144\",\"COMPONENTS\": [{\"NAME\": \"a\",\"ACCEPTEDCHANGESETS\": [\"abc\",\"xyz\",\"efg\"],\"REJECTEDCHANGESETS\": [\"pqr\"]},{\"NAME\": \"b\",\"ACCEPTEDCHANGESETS\": [\"uop\",\"ghf\",\"yui\"]}]}" 
| extract 
| spath path=COMPONENTS{} output=data | mvexpand data | eval _raw=data| extract
| stats count(ACCEPTEDCHANGESETS{}) as Count by NAME

Thanks

Re: How to "group by" in JSON

to4kawa — Mon, 03 Feb 2020 13:09:39 GMT

|makeresults
|eval _raw="{\"NUM\":\"#1\",\"TIME\":\"1/27/2020 12:49:13\",\"STATUS\":\"PASS\",\"DURATION\":\"144\",\"COMPONENTS\":[{\"NAME\":\"a\",\"ACCEPTEDCHANGESETS\":[\"abc\",\"xyz\",\"efg\"],\"REJECTEDCHANGESETS\":[\"pqr\"]},{\"NAME\":\"b\",\"ACCEPTEDCHANGESETS\":[\"uop\",\"ghf\",\"yui\"]}]}"
| spath path=COMPONENTS{} output=components
| stats count by components
| spath input=components
| rename *{} as *
| table NAME ACCEPTEDCHANGESETS REJECTEDCHANGESETS
| eventstats count(ACCEPTEDCHANGESETS) by NAME

Hi, folks.
how about this?

Re: How to "group by" in JSON

anooshac — Tue, 04 Feb 2020 03:41:00 GMT

Hi @kamlesh_vaghela , thanks a lot for the answer,i tried this and i'm getting overall count of the ACCEPTEDCHANGESETS. For every NAME i'm getting the count as same. What will be the problem here?

Re: How to "group by" in JSON

kamlesh_vaghela — Tue, 04 Feb 2020 04:13:40 GMT

@anooshac

With NAME = a associated ACCEPTEDCHANGESETS are abc, xyz and efg' that's Y it's showing3, and forNAME=b` also. Can you please share what is your expected output from your shared sample event?

Re: How to "group by" in JSON

anooshac — Tue, 04 Feb 2020 04:20:48 GMT

Hi @jbrocks , thanks a lot for the answer, i'm getting overall count of ACCEPTEDCHANGESETS for each NAME. What is problem here?

Re: How to "group by" in JSON

anooshac — Tue, 04 Feb 2020 05:13:33 GMT

Hello @kamlesh_vaghela , i am not able to put a screenshot here so i'll be sharing the output table which is expected.

NAME count(ACCEPTEDCHANGESETS)
a 3
b 3

Re: How to "group by" in JSON

kamlesh_vaghela — Tue, 04 Feb 2020 05:40:41 GMT

@anooshac

If you try my Sample Search: then it's giving you same result. Can you please share your sample search if possible? Are you running this search on single event or multiple event. If multiple then is it possible to have same NAME in multiple event?

Re: How to "group by" in JSON

anooshac — Tue, 04 Feb 2020 05:59:47 GMT

hi @kamlesh_vaghela , i'm really sorry there were some mistakes in data that's why i was getting wrong answer. Thanks a lot for the help. My problem is resolved now!!

Re: How to "group by" in JSON

anooshac — Tue, 04 Feb 2020 06:01:29 GMT

Hi, @to4kawa , thanks a lot for the answer, i'm getting the results properly by your query as well as @kamlesh_vaghela s query. Thank you so much for the help!!