Hi
I am trying to write a query where I can monitor transactions/hr/user. I would like an output where I have the hourly count and historic hourly average.
I started with this, for past 24 hours, to look for users above a 10000 events per hour ...
index=some_db sourcetype=syslog_tranactions |bin _time span=1h | stats count by created_by | WHERE count > 10000
But now I was asked to get a baseline of average transactions per hour per user as a running average as a separate output column
Something like this:
User, Hourly Count, Historic Ave
Jon, 125, 140
I am hoping someone could point me in the right direction as I peruse the documentation.
Thank you
Try the following: add _time to the by clause of your initial stats, to generate hourly counts for each user. Then use eventstats to calculate historic average by user.
index=some_db sourcetype=syslog_tranactions
|bin _time span=1h
| stats count as hourly_count by created_by,_time
| eventstats avg(hourly_count) as historic_avg by created_by
If you need that historic_avg to be a running avg (ie. only based on the previous hours, rather than calculating 1 avg. over all your search results), you could use a similar query but using streamstats instead of eventstats:
index=some_db sourcetype=syslog_tranactions
|bin _time span=1h
| stats count as hourly_count by created_by,_time
| sort _time
| streamstats avg(hourly_count) as historic_avg by created_by
Try the following: add _time to the by clause of your initial stats, to generate hourly counts for each user. Then use eventstats to calculate historic average by user.
index=some_db sourcetype=syslog_tranactions
|bin _time span=1h
| stats count as hourly_count by created_by,_time
| eventstats avg(hourly_count) as historic_avg by created_by
If you need that historic_avg to be a running avg (ie. only based on the previous hours, rather than calculating 1 avg. over all your search results), you could use a similar query but using streamstats instead of eventstats:
index=some_db sourcetype=syslog_tranactions
|bin _time span=1h
| stats count as hourly_count by created_by,_time
| sort _time
| streamstats avg(hourly_count) as historic_avg by created_by
thank you for the answer.
If I want to put a threshold like, "| WHERE count > 1000"
is this the best way?
index=some_db sourcetype=syslog_tranactions
|bin _time span=1h
| stats count as hourly_count by created_by,_time
|WHERE hourly_count>1000
| sort _time
| streamstats avg(hourly_count) as historic_avg by created_by
That depends a bit on what exactly you want to achieve with that threshold. You may want to put it all the way at the bottom, such that the historic avg is still calculated accurately. In your suggestion, hours with small counts will be ignored for the historic avg.
Also: do you want to filter out low hourly counts. Or do you want to completely ignore users with a low total count? For that latter case, you would need to calculate that total first.
Thank you, that makes sense. I will run a few tests and as another question if I get stuck. Thank you.