Splunk Search

GroupBy multiple fields within single result

punixtr
New Member

I have a splunk query which results in the output as:

INFO :url="some_url": APIFilter.onComplete@87 : type=finalResponse;response_code=200;processing_time=21663;CommandType1_is_success=yes;CommandType1_exe_time=2758;CommandType2_is_success=yes;CommandType2_exe_time=8312;num_dependencies=2;is_all_dep_successful=true;dependencies_exe_time=11070;App_exe_time=10593;

I want to group by CommandType and have an output as:

Command Type | Success | Average | Median | 
CommandType1| yes | 2758 | 2758 |
CommandType2 | yes | 8312 | 8312 |

My question here is how can I group my multiple fields in the same result?

Tags (1)
0 Karma

Sukisen1981
Champion

Hi,
Not able to understand why you need Average and Median columns. I mean if 1 command type occurs only once in one event wouldn't its average and median be the same? I do think I am missing something here.
However, this query is very near to what you want, I do think we can figure out the average and median fields if i understand what you want in them

| eval x=split(_raw,";")|mvexpand x|eval flg=if(like(x, "CommandType%"), "yes", "no")| where flg="yes"|rex field=x "(?<cmdtype>.*?)_"|rex field=x "success=(?<success>.*)"|streamstats current=f window=1 last(cmdtype) as prev_cmdtype,last(success) as prev_success |rex field=x "time=(?<avg>.*)"|where prev_cmdtype=cmdtype|table x,flg,cmdtype,prev_cmdtype,success,prev_success,avg |rename cmdtype as "Command Type",prev_success as Success,avg as Average | fields - x, - flg, - prev_cmdtype, - success
0 Karma

punixtr
New Member

@Sukisen1981 Sorry if I am unable to communicate my requirements clearly here. The sample here is just one event. There will be multiple such events and I wanted to get the average and median of each CommandType for all those events.
My goal here is to get some stats around how much execution time each CommandType (dependency) takes, which in turn would help me to improve on that in future.
Will post some examples in few minutes

0 Karma

Sukisen1981
Champion

what happens when you execute the above query, do you receive results?

0 Karma

punixtr
New Member

I get something like this:
|Command Type | Success | Average |
| 1 | yes | |
| 1 | | |
| 1 | | 2151 |
| 1 | | |
| 2 | yes | |
| 2 | | |
| 2 | | 1952 |
| 2 | | |
| 1 | yes | |
| 1 | | |
| 1 | | 2697 |
| 1 | | |
...and so on

0 Karma

punixtr
New Member

Sample events:

INFO :url="some_url": APIFilter.onComplete@87 : type=finalResponse;response_code=200;processing_time=7000;CommandType1_is_success=yes;CommandType1_exe_time=1500;CommandType2_is_success=yes;CommandType2_exe_time=5000;num_dependencies=2;is_all_dep_successful=true;dependencies_exe_time=6500;App_exe_time=500;

INFO :url="some_url": APIFilter.onComplete@87 : type=finalResponse;response_code=200;processing_time=8500;CommandType1_is_success=no;CommandType1_exe_time=3000;CommandType3_is_success=yes;CommandType3_exe_time=5000;num_dependencies=2;is_all_dep_successful=false;dependencies_exe_time=7000;App_exe_time=500;

INFO :url="some_url": APIFilter.onComplete@87 : type=finalResponse;response_code=200;processing_time=10700;CommandType1_is_success=yes;CommandType1_exe_time=3000;CommandType2_is_success=yes;CommandType2_exe_time=4000;CommandType3_is_success=no;CommandType3_exe_time=3000;num_dependencies=3;is_all_dep_successful=false;dependencies_exe_time=10000;App_exe_time=700;

Sample output expected

| Command Type | Total | Average Time | Median Time | 
| 1 | 3 | 2500 | 3000 |
| 2 | 2 | 4500 | 4500 |
| 3 | 2 | 4000 | 4000 |
0 Karma

Sukisen1981
Champion

hi - Got it now and applied the code below to your fresh sample. i checked the results and it looks ok.try this - |eval x=split(_raw,";")|mvexpand x|eval flg=if(like(x, "CommandType%"), "yes", "no")| where flg="yes"|rex field=x "(?<cmdtype>.*?)_"|rex field=x "success=(?<success>.*)"|streamstats current=f window=1 last(cmdtype) as prev_cmdtype,last(success) as prev_success |rex field=x "time=(?<avg>.*)"|where prev_cmdtype=cmdtype|rename prev_success as Success,avg as Average | fields - x, - flg, - prev_cmdtype, - success | stats count(cmdtype),avg(Average),median(Average) by cmdtype| rename cmdtype as "Command Type",count(cmdtype) as Total, avg(Average) as Average,median(Average) as Median

0 Karma

Sukisen1981
Champion

looks like a log snippet. can there be multiple commandtype1 in the event or only 1 value in one event at a time?
I mean you can have commandtype1,23... and so in one event BUT, do you have commandtype1 for example repeated in the same event?
Is it possible to post the complete log?

0 Karma

punixtr
New Member

@Sukisen1981 : commandType1 ... or commandType*N* would would appear only 0 to 1 time in single event. Definitly not more than 1. This is one of the complete sample logs (though have renamed the actual types and removed unnecessary info)

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