Splunk Search

How to group by object's keys?

New Member

I would like to create stats from the data whose structure looks like mentioned below:

  {
         data:  {
             messageType: Test_LOG  
             myList:    {
                  Test1:    {
                           counts:  {
                                RequestReceived:     2  
                                ResponseSent:    2  
                                negativeResponsesSent:   1  
                           }    
                  } ,
                     Test2: {
                           counts:  {
                                RequestReceived:     1  
                                ResponseSent:    1  
                                negativeResponsesSent:   1  
                           }    
                  } 
            }   
        }   
         hostname:   ip-12212
         name:   test-logs  
         nodeEnv:    test   
         pid:    4338   
         time:   2018-05-14T14:53:45.599Z   
         ts:     1526309625599  
         v:  0  
         version:    1.1
    }

How can I create stats such that I get counts for RequestReceived and ResponseSent for Test1 and Test 2. Something like a report below:

 Tests    RequestReceived      ResponseSent
 Test1    2                     2
 Test2    1                     1
0 Karma
1 Solution

SplunkTrust
SplunkTrust

Assuming your raw data is a valid JSON , try something like this

your base search | table _raw | spath | table *RequestReceived *ResponseSent | eval temp=1 | untable temp name value | rex field=name "data\.myList\.(?<TestName>[^\.]+)\.counts\.(?<Counter>.+)" | table TestName Counter value | chart values(value) over TestName by Counter

View solution in original post

0 Karma

SplunkTrust
SplunkTrust

Assuming your raw data is a valid JSON , try something like this

your base search | table _raw | spath | table *RequestReceived *ResponseSent | eval temp=1 | untable temp name value | rex field=name "data\.myList\.(?<TestName>[^\.]+)\.counts\.(?<Counter>.+)" | table TestName Counter value | chart values(value) over TestName by Counter

View solution in original post

0 Karma

New Member

Hi somesoni2,
Thanks for your help. This solution works fine for the case I asked above. But in case if I would like to add additional columns like time and host to the stats table, it would not work. Could you please help if this solution could to extended to something like:

  Tests    Time                             host    RequestReceived      ResponseSent
  Test1    2018-05-14T14:53:45.599Z      ip-12212      2                                  2
  Test2    2018-05-14T14:53:45.599Z      ip-12212      1                                  1
0 Karma

SplunkTrust
SplunkTrust

All those fields should be available after the spath command, so give this version a try

your base search | table _raw | spath 
| table time host *RequestReceived *ResponseSent | eval temp=time."##".host | untable temp name value | rex field=name "data\.myList\.(?<TestName>[^\.]+)\.counts\.(?<Counter>.+)"  | eval temp=temp."##".TesetName | table temp Counter value | chart values(value) over temp by Counter
| rex field=temp "(?<Time>.+)##(?<host>.+)##(?<Tests>.+)" 
| table Tests Time host RequestReceived ResponseSent 

Basically, since the chart command supports group by 2 columns only, we'll merge all the required fields into one field (field temp here, in which we add time and host and later on TestName) , do chart with that one field and extract/separate those fields afterwards.

0 Karma

New Member

Works great. Thanks a lot!!

0 Karma