Splunk Search

How to get an average from this search?

New Member

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!

0 Karma
1 Solution

Contributor

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

View solution in original post

0 Karma

Motivator

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

0 Karma

New Member

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!

0 Karma

Motivator

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

0 Karma

New Member

Thanks @VatsalJagani !

0 Karma

Influencer

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
0 Karma

Influencer

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

0 Karma

New Member

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.

0 Karma

Contributor

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

View solution in original post

0 Karma

New Member

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

0 Karma

Contributor

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
0 Karma

New Member

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?

0 Karma

Contributor

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.

0 Karma

New Member

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

0 Karma

Contributor

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)
0 Karma

New Member

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 ?

0 Karma

Contributor

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
0 Karma

New Member

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

0 Karma

Motivator

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

0 Karma

New Member

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

0 Karma