topic Re: How to get an average from this search? in Splunk Search

How to get an average from this search?

kvanwagoner — Mon, 03 Jun 2019 14:10:51 GMT

I'm using the following search which I have working in a dashboard.

"A PUT was made to OpenAAA API - Status: OK"
| spath AppID | search AppID=200296 Environment=prod | timechart count by Environment|

It displays the # of events for each day without issue.

But how can I get the average # of events for the same 7-day time frame?

Any help would be greatly appreciated!

Re: How to get an average from this search?

alemarzu — Mon, 03 Jun 2019 15:19:20 GMT

Hello there, have you try
... | timechart avg(count) as avgCount by Environment span=1d

Re: How to get an average from this search?

harshpatel — Mon, 03 Jun 2019 15:30:32 GMT

Hi @kvanwagoner,

You can provide span value in the timechart command to have it display count over 7 day period.

For example:

"A PUT was made to OpenAAA API - Status: OK" | spath AppID | search AppID=200296 Environment=prod | timechart span=7d count by Environment

Splunk Doc: Timechart Bin Options

Re: How to get an average from this search?

kvanwagoner — Mon, 03 Jun 2019 15:35:40 GMT

Thank you @alemarzu
I just tried "A PUT was made to OpenAAA API - Status: OK" | spath AppID | search AppID=200296 Environment=prod |timechart avg(count) as avgCount by Environment span=1d

and got No Results found

Re: How to get an average from this search?

kvanwagoner — Mon, 03 Jun 2019 15:39:15 GMT

Thanks @harshpatel
I tried that and it returned the following

_time prod
2019-05-27 2353
2019-06-03 79

Not quite what I'm looking for. I need the average over the 7 days which should be around 347.
I'm not sure what the 2353 actually represents.

Any ideas?
Thanks

This is what was returned from my original search
27th 44
28th 390
29th 586
30th 520
31st 492
1st 211
2nd 110
3rd 83

Re: How to get an average from this search?

harshpatel — Mon, 03 Jun 2019 15:55:43 GMT

Hi @kvanwagoner , Can you try this:

"A PUT was made to OpenAAA API - Status: OK" | spath AppID | search AppID=200296 Environment=prod | timechart count by Environment | bin span=7d _time | stats avg(prod) by _time

Re: How to get an average from this search?

Vijeta — Mon, 03 Jun 2019 15:56:03 GMT

You have just one Environment i.e. Production right? Use the below query -

 "A PUT was made to OpenAAA API - Status: OK" | spath AppID | search AppID=200296 Environment=prod | timechart span=1d count by Environment| eventstats sum(Production) as sum| eval  average=Production/sum

Re: How to get an average from this search?

kvanwagoner — Mon, 03 Jun 2019 17:48:29 GMT

Thanks @harshpatel
That returns 2 records when using "Last 7 days" in search
2019-05-27 334
2019-06-03 146

This is closer to what I need but I'm not sure why it's returning 2 records and the average is slightly off
355 should be the last 7 day average based on the results from my original search
I just need it to give me 1...any ideas?

Re: How to get an average from this search?

harshpatel — Mon, 03 Jun 2019 18:15:20 GMT

In what timerange you are running this query? If you just want last 7 days records you run your search for last 7 days only. Your records are of more than 7 days. That's why it is getting an extra row.

Hope this helps.

Re: How to get an average from this search?

VatsalJagani — Mon, 03 Jun 2019 18:16:29 GMT

Hi @kvanwagoner,

If this is what you are looking for!! This search gives the average of events count per day. Run this search in last 7 day. <your search> | timechart count span=1d | stats avg(count) as avg_count

Re: How to get an average from this search?

kvanwagoner — Mon, 03 Jun 2019 18:24:39 GMT

@harshpatel
I used the search criteria you gave me with a timerange of Last 7 Days.

Re: How to get an average from this search?

kvanwagoner — Mon, 03 Jun 2019 18:33:47 GMT

Thanks @VatsalJagani
I used the following with a "last 7 days" search

"A PUT was made to OpenAAA API - Status: OK" | spath AppID | search AppID=200296 Environment=prod | timechart count span=1d | stats avg(count) as avg_count

Thanks for the help!

Re: How to get an average from this search?

harshpatel — Mon, 03 Jun 2019 18:35:19 GMT

Well if you just want average then you can do something like:

"A PUT was made to OpenAAA API - Status: OK" | spath AppID | search AppID=200296 Environment=prod | timechart count by Environment | bin span=7d _time | stats avg(prod)

Re: How to get an average from this search?

kvanwagoner — Mon, 03 Jun 2019 19:06:39 GMT

That returns a single record!
But the average is still off at least by my count.

24+390+586+520+492+211+110+184 = 2517
2517/7 = 359

But the query is returning 314. So weird

Any ideas @harshpatel ?

Re: How to get an average from this search?

Vijeta — Mon, 03 Jun 2019 19:14:45 GMT

@kvanwagoner did you try above search. Please replace Production by prod in above query as your environment name is prod.

Re: How to get an average from this search?

kvanwagoner — Mon, 03 Jun 2019 19:25:56 GMT

Yes, I tried that one and changed the fields to Prod. I appreciate the help but that didn't give me what I needed.

I;m looking for a singular average for the events over a 7 day period. I think so of the other suggestions will work for me.

Re: How to get an average from this search?

harshpatel — Mon, 03 Jun 2019 19:25:58 GMT

Splunk query actually divides it by 8 if you want to divide by 7 you can use:

"A PUT was made to OpenAAA API - Status: OK" | spath AppID | search AppID=200296 Environment=prod | timechart count by Environment | bin span=7d _time | stats sum(prod) as sum_prod | eval average_count=sum_prod/7

Re: How to get an average from this search?

kvanwagoner — Mon, 03 Jun 2019 19:49:57 GMT

Thanks harshpatel!
I think I have it working now!!
Thanks Everyone

Re: How to get an average from this search?

VatsalJagani — Tue, 04 Jun 2019 05:16:07 GMT

@kvanwagoner - If what you want is "average of events count per day" then query is correct.

Re: How to get an average from this search?

kvanwagoner — Tue, 04 Jun 2019 11:11:42 GMT

Thanks @VatsalJagani !