How to "group by" in JSON

[anooshac]

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

[to4kawa]

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

[anooshac]

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

[kamlesh_vaghela · Accepted solution]

@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

[anooshac]

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?

[kamlesh_vaghela]

@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?

[anooshac]

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

[kamlesh_vaghela]

@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?

[anooshac]

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

[jbrocks]

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

[anooshac]

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

[kamlesh_vaghela]

@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) ?

[anooshac]

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?