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?
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
@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
what happens when you execute the above query, do you receive results?
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
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 |
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
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?
@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)