topic How to calculate median, avg for total results? in Splunk Search

How to calculate median, avg for total results?

badbuda — Wed, 22 Mar 2023 20:35:55 GMT

hey,

I need to build a report, that contains approx 500 thousand events. the requirement is that the report will contain three rows -
I need to count if httpStatus is ok or not, and classify each eventId in its propper position. (the requirement is that we will have minimal amount of rows!!! I cant duplicate or have more then 10 rows)

so basically the report looks like this:

I have uri column that contains all of my desired info, and all of my calculations of median, avg, precentage etc, are based on the time field as follows:

now my question is-

I need to add a new line of totals, based on the other lines.

beacuse Im using functions such as avg, median etc, I dont think I can use |addtotals

and a very important note is that all of my values in the columns time and uri are not distinct. that means they can appear more then once, and then my calculations are wrong, and I cant base a following stats based on the previous one. Ive tried using list, but it has a limit of 100 values, and I have hundred of thousands.

what can I do to add another total row that will calculate all of my events ?

Ive tried adding |appendPipe it this way based on the results Ive gotten in the stats command, but of course I got wrong values (because the time result is not distinct, and the values shown in the stats are distinct)

thats my report after adding the total calculation (that didnt work)

|*MY SEARCH * |stats count(request.uri) as totalCount values(uri) as uri values(timeTaken.total) as newTime perc95(timeTaken.total) as prec95 perc5(timeTaken.total) as prec5 median(timeTaken.total) as med avg(timeTaken.total) as average max(date) as maxDate min(date) as minDate values(timeTaken.total) as time by status |appendpipe [stats sum(totalCount) as totalCount values(uri) as uri values(newTime) as newTime perc95(time) as prec95 perc5(time) as prec5 median(time) as med avg(time) as average| eval status="TOTAL"] |table uri totalCount prec95 prec5 med average status maxDate minDate time

I really hope that Ive made my question clear

thank's in advance 🙂

Re: calculating median, avg for total results

richgalloway — Wed, 22 Mar 2023 19:22:09 GMT

Since you know the names of all of the fields, there's no reason why you can't use addtotals or (better) addcoltotals.

|*MY SEARCH * |stats count(request.uri) as totalCount values(uri) as uri values(timeTaken.total) as newTime perc95(timeTaken.total) as prec95 perc5(timeTaken.total) as prec5 median(timeTaken.total) as med avg(timeTaken.total) as average max(date) as maxDate min(date) as minDate values(timeTaken.total) as time by status |table uri totalCount prec95 prec5 med average status maxDate minDate time |addcoltotals totalCount prec95 prec5 med average

Re: calculating median, avg for total results

badbuda — Wed, 22 Mar 2023 19:38:14 GMT

Thank you for your answer,

But can I add averages (and medians, percentiles etc)?

Addcoltotals will just sum these fields together, won't it?

Adding two averages together won't give me the right average I'm looking for

Am I wrong?

Re: calculating median, avg for total results

PickleRick — Wed, 22 Mar 2023 20:23:21 GMT

You're absolutely right. Adding two averages doesn't give you a "total average" of course.

It seems your original idea was OK, just had small mistakes in the middle.

|*MY SEARCH *
|stats count(request.uri) as totalCount values(uri) as uri values(timeTaken.total) as newTime perc95(timeTaken.total) as prec95 perc5(timeTaken.total) as prec5 median(timeTaken.total) as med avg(timeTaken.total) as average max(date) as maxDate 
min(date) as minDate values(timeTaken.total) as time by status 

|appendpipe [stats count(request.uri) as totalCount values(uri) as uri values(timeTaken.total) as newTime perc95(timeTaken.total) as prec95 perc5(timeTaken.total) as prec5 median(timeTaken.total) as med avg(timeTaken.total) as average max(date) as maxDate min(date) as minDate values(timeTaken.total) as time| eval status="TOTAL"]

And you don't need table at the end.

Re: calculating median, avg for total results

badbuda — Thu, 23 Mar 2023 05:44:46 GMT

thanks, thats exactly what I tried doing at the beginning

but after doing stats for the first time, if Im not saving my fields in stats command, they will not appear in the following query. is there any way of saving previous data before stats command?

I did exactly as you said and these are my results

Re: How to calculate median, avg for total results?

badbuda — Thu, 23 Mar 2023 06:15:19 GMT

I think I found my solution!
I've changed the first stats command to another appendpipe, so it wont ruin the totals-

|appendpipe [stats count(request.uri) as totalCount values(uri) as uri values(timeTaken.total) as newTime perc95(timeTaken.total) as prec95 perc5(timeTaken.total) as prec5 median(timeTaken.total) as med avg(timeTaken.total) as average max(date) as maxDate min(date) as minDate| eval finalStatus="TOTAL"] |appendpipe [stats count(request.uri) as totalCount values(uri) as uri values(timeTaken.total) as newTime perc95(timeTaken.total) as prec95 perc5(timeTaken.total) as prec5 median(timeTaken.total) as med avg(timeTaken.total) as average max(date) as maxDate min(date) as minDate values(timeTaken.total) as time by status | eval finalStatus="ok"]

now my results seems to make sense.

thank you all!

Re: calculating median, avg for total results

PickleRick — Thu, 23 Mar 2023 07:51:19 GMT

Ahh, right. I don't often use appendpipe so I got confused by the example in the docs. Yes, appendpipe operates at the already aggregated values so it won't be very helpful here.

Unfortunately if you have a parameter which has to be calculated over the whole set you can't calculate it partially 😉 Your solution will skew results a bit because with the first appendpipe you change the overall count of the results. And if I'm not mistaken it will not be that much more effective than eventstat so you could probably use that one instead.

The other solution could be to "manually" calculate the parameters - but that could be tedious.