Splunk Search

How to group by in nested JSON?

anooshac
Communicator

Hi all,
i have a json file like this.

{
 "JOB_NUM" : "1",
 "JOB_TIME" : "1/1/2020",
 "JOB_STATUS" : "PASS",
 "JOB_DURATION" : "304",
 "TESTCASES": 
  [
    { 
          "ID" : "a",
      "TIME" : "01.01.2020",
      "DURATION" : "126",
      "STATUS" : "PASS"
    },
    {
          "ID" : "b",
      "TIME" : "01.01.2020",
      "DURATION" : "62",
      "STATUS" : "FAIL"
        },
    {
          "ID" : "c",
      "TIME" : "01.01.2020",
      "DURATION" : "85",
      "STATUS" : "PASS" 
        },
        {
          "ID" : "d",
      "TIME" : "01.01.2020",
      "DURATION" : "135",
      "STATUS" : "PASS" 
        }
]
}

I want to group by "ID" by "STATUS" (both are inner fields). I have tried a lot. But still i am not able to get the proper answer. Can anyone please help me in this.

Thanks in advance.

0 Karma
1 Solution

kamlesh_vaghela
SplunkTrust
SplunkTrust

@anooshac

You can have your required field by executing below search. Can you please try this ?

YOUR_SEARCH
| rename TESTCASES{}.* as * 
| eval tmp = mvzip(mvzip(mvzip(ID,STATUS),TIME),DURATION) 
| mvexpand tmp 
| eval ID=mvindex(split(tmp,","),0),STATUS=mvindex(split(tmp,","),1),TIME=mvindex(split(tmp,","),2),DURATION=mvindex(split(tmp,","),3)
| table JOB_NUM JOB_TIME JOB_STATUS JOB_DURATION ID TIME DURATION STATUS

Sample Search:

| makeresults 
| eval _raw="{\"JOB_NUM\" : \"1\",\"JOB_TIME\" : \"1/1/2020\",\"JOB_STATUS\" : \"PASS\",\"JOB_DURATION\" : \"304\",\"TESTCASES\":[{\"ID\" : \"a\",\"TIME\" : \"01.01.2020\",\"DURATION\" : \"126\",\"STATUS\" : \"PASS\"},{\"ID\" : \"b\",\"TIME\" : \"01.01.2020\",\"DURATION\" : \"62\",\"STATUS\" : \"FAIL\"},{\"ID\" : \"c\",\"TIME\" : \"01.01.2020\",\"DURATION\" : \"85\",\"STATUS\" : \"PASS\"},{\"ID\" : \"d\",\"TIME\" : \"01.01.2020\",\"DURATION\" : \"135\",\"STATUS\" : \"PASS\"}]}" 
| extract 
| rename TESTCASES{}.* as * 
| eval tmp = mvzip(mvzip(mvzip(ID,STATUS),TIME),DURATION) 
| mvexpand tmp 
| eval ID=mvindex(split(tmp,","),0),STATUS=mvindex(split(tmp,","),1),TIME=mvindex(split(tmp,","),2),DURATION=mvindex(split(tmp,","),3)
| table JOB_NUM JOB_TIME JOB_STATUS JOB_DURATION ID TIME DURATION STATUS

With the proper data-set you can do any aggregation using stats command.

Adding into above sample search:

 | stats count by ID

OR

| stats count by ID

OR

| stats <<ANY LOGIC AS PER YOUR REQUIREMENT>>

Please let us know for further assistance.

View solution in original post

0 Karma

to4kawa
Ultra Champion

Sample:

| makeresults 
| eval _raw="{\"JOB_NUM\" : \"1\",\"JOB_TIME\" : \"1/1/2020\",\"JOB_STATUS\" : \"PASS\",\"JOB_DURATION\" : \"304\",\"TESTCASES\":[{\"ID\" : \"a\",\"TIME\" : \"01.01.2020\",\"DURATION\" : \"126\",\"STATUS\" : \"PASS\"},{\"ID\" : \"b\",\"TIME\" : \"01.01.2020\",\"DURATION\" : \"62\",\"STATUS\" : \"FAIL\"},{\"ID\" : \"c\",\"TIME\" : \"01.01.2020\",\"DURATION\" : \"85\",\"STATUS\" : \"PASS\"},{\"ID\" : \"d\",\"TIME\" : \"01.01.2020\",\"DURATION\" : \"135\",\"STATUS\" : \"PASS\"}]}" 
| spath 
| rename TESTCASES{}.* as * 
| eval _counter=mvrange(0,4) 
| stats list(*) as * by _counter 
| foreach * 
    [ eval <<FIELD>>=if(mvcount(<<FIELD>>)=1,<<FIELD>>,mvindex('<<FIELD>>',_counter))] 
| table JOB_NUM JOB_TIME JOB_STATUS JOB_DURATION ID TIME DURATION STATUS 
| stats count by ID STATUS 
| where STATUS="PASS"

VIZ>Pie Chart with Trellis
Recommend(4):

index="abc"
| spath path=TESTCASES{} output=json
| spath input=json
| table ID TIME DURATION STATUS
| eval _counter=mvrange(0,mvcount(ID)) 
| stats list(*) as * by _counter 
| foreach * 
    [ eval <<FIELD>>=if(mvcount(<<FIELD>>)=1,<<FIELD>>,mvindex('<<FIELD>>',_counter))] 
| table ID TIME DURATION STATUS
| stats sum(DURATION) as  DURATION  by  ID STATUS
| where STATUS="$status$"

viz> Pie Chart with trellis.

0 Karma

anooshac
Communicator

hi @to4kawa I don't know why but i am getting only one value in the table

0 Karma

to4kawa
Ultra Champion

you want to select STATUS and display by Pie Chart ,ID count?
what's this token $state$?

0 Karma

anooshac
Communicator

hi @to4kawa i got pie chart using @kamlesh_vaghela's solution. Now i want a drill down from that pie chart. The token $state$ is STATUS. On clicking the respective STATUS it should lead to a table containing all the ID's. I am trying for that, if you know how to do it can you please help me in this!!

0 Karma

to4kawa
Ultra Champion

I see, I'll count total DURATION.
my answer is updated.

0 Karma

anooshac
Communicator

Hi @to4kawa thank you so much for the response. It is working, but the output table is having only 3 rows. But the data has many testcases.

0 Karma

to4kawa
Ultra Champion

hi @anooshac, my answer is updated.

0 Karma

anooshac
Communicator

It is working , but some of the values are repeating in table. Why is that so?

0 Karma

to4kawa
Ultra Champion

JOB_NUM,JOB_TIME,JOB_STATUS,JOB_DURATION are dare to display multiple.
Do not you need them?

0 Karma

anooshac
Communicator

I need to group the IDs. Thanks a lot for responding. I got the answer using @kamlesh_vaghela's solution. I am trying to make a drill down from the pie chart .
The query is,
index=''abc" STATUS="$state$" | table TESTCASES{}.ID,TESTCASES{}.DURATION,TESTCASES{}.STATUS

But i am not getting proper answer. Do you know how to solve this?

0 Karma

to4kawa
Ultra Champion

my answer is updated.

0 Karma

kamlesh_vaghela
SplunkTrust
SplunkTrust

@anooshac

You can have your required field by executing below search. Can you please try this ?

YOUR_SEARCH
| rename TESTCASES{}.* as * 
| eval tmp = mvzip(mvzip(mvzip(ID,STATUS),TIME),DURATION) 
| mvexpand tmp 
| eval ID=mvindex(split(tmp,","),0),STATUS=mvindex(split(tmp,","),1),TIME=mvindex(split(tmp,","),2),DURATION=mvindex(split(tmp,","),3)
| table JOB_NUM JOB_TIME JOB_STATUS JOB_DURATION ID TIME DURATION STATUS

Sample Search:

| makeresults 
| eval _raw="{\"JOB_NUM\" : \"1\",\"JOB_TIME\" : \"1/1/2020\",\"JOB_STATUS\" : \"PASS\",\"JOB_DURATION\" : \"304\",\"TESTCASES\":[{\"ID\" : \"a\",\"TIME\" : \"01.01.2020\",\"DURATION\" : \"126\",\"STATUS\" : \"PASS\"},{\"ID\" : \"b\",\"TIME\" : \"01.01.2020\",\"DURATION\" : \"62\",\"STATUS\" : \"FAIL\"},{\"ID\" : \"c\",\"TIME\" : \"01.01.2020\",\"DURATION\" : \"85\",\"STATUS\" : \"PASS\"},{\"ID\" : \"d\",\"TIME\" : \"01.01.2020\",\"DURATION\" : \"135\",\"STATUS\" : \"PASS\"}]}" 
| extract 
| rename TESTCASES{}.* as * 
| eval tmp = mvzip(mvzip(mvzip(ID,STATUS),TIME),DURATION) 
| mvexpand tmp 
| eval ID=mvindex(split(tmp,","),0),STATUS=mvindex(split(tmp,","),1),TIME=mvindex(split(tmp,","),2),DURATION=mvindex(split(tmp,","),3)
| table JOB_NUM JOB_TIME JOB_STATUS JOB_DURATION ID TIME DURATION STATUS

With the proper data-set you can do any aggregation using stats command.

Adding into above sample search:

 | stats count by ID

OR

| stats count by ID

OR

| stats <<ANY LOGIC AS PER YOUR REQUIREMENT>>

Please let us know for further assistance.

0 Karma

anooshac
Communicator

Hi @kamlesh_vaghela thank you so much for your response. It worked!!
But when i execute |stats count(ID) by STATUS i get count more than the actual count of IDs. Is it event count?
If i give distinct_count the IDs which are repeated will not be added into the count.What will be the problem here?

0 Karma

kamlesh_vaghela
SplunkTrust
SplunkTrust

@anooshac yes it is event count.

Try this for distinct ID count

|stats dc(ID) by STATUS
0 Karma

anooshac
Communicator

Hi @kamlesh_vaghela It is working! Once again thanks a lot!
I need one more help. I am trying to give one drill down from this result to display the IDs of respective status. I saved this result as pie chart. When i click on any status the drill down is displaying all the IDs instead of respective ID's of that status. My query is

index=''abc" STATUS="$state$" | table TESTCASES{}.ID,TESTCASES{}.DURATION,TESTCASES{}.STATUS

How can i correct this? can you please help me!!

0 Karma

kamlesh_vaghela
SplunkTrust
SplunkTrust

@anooshac

Try this XML. You will get idea about drilldown.

<dashboard>
  <label>Pie Chart Drilldown Example</label>
  <row>
    <panel>
      <chart>
        <search>
          <query>index="abc" | rename TESTCASES{}.* as * | eval tmp = mvzip(mvzip(mvzip(ID,STATUS),TIME),DURATION) | mvexpand tmp | eval ID=mvindex(split(tmp,","),0),STATUS=mvindex(split(tmp,","),1),TIME=mvindex(split(tmp,","),2),DURATION=mvindex(split(tmp,","),3)| table JOB_NUM JOB_TIME JOB_STATUS JOB_DURATION ID TIME DURATION STATUS | stats dc(ID) as ID_Count by STATUS</query>
          <earliest>-24h@h</earliest>
          <latest>now</latest>
          <sampleRatio>1</sampleRatio>
        </search>
        <option name="charting.chart">pie</option>
        <option name="charting.drilldown">all</option>
        <drilldown>
          <link target="_blank">search?q=index="abc" | rename TESTCASES{}.* as * | eval tmp = mvzip(mvzip(mvzip(ID,STATUS),TIME),DURATION) | mvexpand tmp | eval ID=mvindex(split(tmp,","),0),STATUS=mvindex(split(tmp,","),1),TIME=mvindex(split(tmp,","),2),DURATION=mvindex(split(tmp,","),3) | where STATUS="$click.value$"| table JOB_NUM JOB_TIME JOB_STATUS JOB_DURATION ID TIME DURATION STATUS</link>
        </drilldown>
      </chart>
    </panel>
  </row>
</dashboard>
0 Karma

anooshac
Communicator

hi @kamlesh_vaghela i tried and i don't know why but the values are displaying twice in the table.

0 Karma

kamlesh_vaghela
SplunkTrust
SplunkTrust

@anooshac

Can you please share search you implemented in drilldown? please mask confidential values if there.

0 Karma
Get Updates on the Splunk Community!

Index This | I am a number, but when you add ‘G’ to me, I go away. What number am I?

March 2024 Edition Hayyy Splunk Education Enthusiasts and the Eternally Curious!  We’re back with another ...

What’s New in Splunk App for PCI Compliance 5.3.1?

The Splunk App for PCI Compliance allows customers to extend the power of their existing Splunk solution with ...

Extending Observability Content to Splunk Cloud

Register to join us !   In this Extending Observability Content to Splunk Cloud Tech Talk, you'll see how to ...