How to get today's and last 7 day's average in single query

[spoolunk]

I have a query
index=errors earliest=@d latest=now |stats count(ErrorCode) as ErrorCountForToday by host

I would like this query to show me error count for today (as doing already) and the average error count for the last 7 days . so that I can compare how much increase decrease happened.

Currently we are using a input csv which populates a file with 7 days average data and we pick it up from there.

Let me know if that is achievable from the query.

Thanks,

[woodcock · Accepted solution]

Like this:

index=errors earliest=-7d@d latest=now
| timechart span=1d count(ErrorCode) AS ErrorCount BY host
| untable _time host count
| eval start_of_today = relative_time(now(), "@d") 
| eval when= if((_time >= start_of_today), "Today", "Last Week")
| chart avg(count) AS count BY when

[spoolunk]

Hello.

Thanks for your answer. But when I am running this, I am only seeing today's data, not the average of 7 days.

[woodcock]

You are quite correct; there was a flaw in my original answer. Try the updated answer.

[sayanidasgupta]

what is the updated query, could you please tell

[woodcock]

I edited the answer so the one that you see is the fixed one.

[sayanidasgupta]

Thanks much @woodcock.
Actually I am looking something like below -
Single Table containing -
stats count by DID TN - for today
avg count for last 7 day by DID and TN
deviation of today vs. Last 7 day avg count.

e.g.

DID TN Today Last7Dayavg Deviation - Today vs Last 7 day avg
123 A 230 330 -100
124 B 90 70 20

[sayanidasgupta]

Could you please help, could not able to do this for a long.......

[woodcock]

Ask a new question and @-me.

[spoolunk]

Thanks, this is working now. 🙂