Splunk Search

How to group by object's keys?

developer_de
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

somesoni2
Revered Legend

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

somesoni2
Revered Legend

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

developer_de
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

somesoni2
Revered Legend

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

developer_de
New Member

Works great. Thanks a lot!!

0 Karma
.conf21 CFS Extended through 5/20!

Don't miss your chance
to share your Splunk
wisdom in-person or
virtually at .conf21!

Call for Speakers has
been extended through
Thursday, 5/20!