Splunk Search

How to "group by" in JSON

anooshac
Communicator

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

Tags (2)
0 Karma
1 Solution

kamlesh_vaghela
SplunkTrust
SplunkTrust

@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

View solution in original post

0 Karma

to4kawa
Ultra Champion
|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?

0 Karma

anooshac
Communicator

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

0 Karma

kamlesh_vaghela
SplunkTrust
SplunkTrust

@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

0 Karma

anooshac
Communicator

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?

0 Karma

kamlesh_vaghela
SplunkTrust
SplunkTrust

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

0 Karma

anooshac
Communicator

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

0 Karma

kamlesh_vaghela
SplunkTrust
SplunkTrust

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

0 Karma

anooshac
Communicator

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

0 Karma

jbrocks
Communicator

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
0 Karma

anooshac
Communicator

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

0 Karma

kamlesh_vaghela
SplunkTrust
SplunkTrust

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

0 Karma

anooshac
Communicator

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?

0 Karma
Get Updates on the Splunk Community!

What's new in Splunk Cloud Platform 9.1.2312?

Hi Splunky people! We are excited to share the newest updates in Splunk Cloud Platform 9.1.2312! Analysts can ...

What’s New in Splunk Security Essentials 3.8.0?

Splunk Security Essentials (SSE) is an app that can amplify the power of your existing Splunk Cloud Platform, ...

Let’s Get You Certified – Vegas-Style at .conf24

Are you ready to level up your Splunk game? Then, let’s get you certified live at .conf24 – our annual user ...