topic Re: How to group by in nested JSON? in Splunk Search

How to group by in nested JSON?

anooshac — Tue, 28 Jan 2020 11:46:17 GMT

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.

Re: How to group by in nested JSON?

kamlesh_vaghela — Tue, 28 Jan 2020 14:03:16 GMT

@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

| stats count by ID

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

Please let us know for further assistance.

Re: How to group by in nested JSON?

to4kawa — Tue, 28 Jan 2020 14:15:31 GMT

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.

Re: How to group by in nested JSON?

anooshac — Wed, 30 Sep 2020 03:56:17 GMT

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?

Re: How to group by in nested JSON?

anooshac — Wed, 29 Jan 2020 03:43:16 GMT

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.

Re: How to group by in nested JSON?

to4kawa — Wed, 29 Jan 2020 04:01:36 GMT

hi @anooshac, my answer is updated.

Re: How to group by in nested JSON?

kamlesh_vaghela — Wed, 29 Jan 2020 04:07:58 GMT

@anooshac yes it is event count.

Try this for distinct ID count

|stats dc(ID) by STATUS

Re: How to group by in nested JSON?

anooshac — Wed, 29 Jan 2020 04:35:26 GMT

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

Re: How to group by in nested JSON?

anooshac — Wed, 29 Jan 2020 04:35:37 GMT

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

Re: How to group by in nested JSON?

to4kawa — Wed, 30 Sep 2020 03:50:57 GMT

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

Re: How to group by in nested JSON?

anooshac — Wed, 29 Jan 2020 05:28:31 GMT

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?

Re: How to group by in nested JSON?

to4kawa — Wed, 29 Jan 2020 05:44:48 GMT

my answer is updated.

Re: How to group by in nested JSON?

anooshac — Wed, 29 Jan 2020 06:18:45 GMT

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

Re: How to group by in nested JSON?

to4kawa — Wed, 29 Jan 2020 06:23:08 GMT

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

Re: How to group by in nested JSON?

to4kawa — Wed, 29 Jan 2020 06:39:08 GMT

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

Re: How to group by in nested JSON?

anooshac — Wed, 29 Jan 2020 07:12:14 GMT

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

Re: How to group by in nested JSON?

kamlesh_vaghela — Wed, 29 Jan 2020 07:14:28 GMT

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

Re: How to group by in nested JSON?

anooshac — Wed, 29 Jan 2020 08:38:26 GMT

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

Re: How to group by in nested JSON?

kamlesh_vaghela — Wed, 29 Jan 2020 10:05:44 GMT

@anooshac

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