How do I report on message volume over time

[Mick]

We are attempting to create a report that compares message traffic for the past two complete weeks.

We have this as an initial start:

earliest_time=-14d@w0 latest_time=@w0 | eval week_name=if((round((now()-_time)/(60*60*24)))< 7 , "Current Week", "Previous Week") | chart count by tag::eventtype, week_name

The problem that we have is that there is no way to get the time of the oldest entry on the returned events. The best we can do is now() which gives us the time the search was started. If we wanted to do a report comparing the past 14 days of traffic it would work perfectly. Unfortunately we want to handle any given period, not just the past 14 days, so we need another method to determine time deltas between events.

[steveyz · Accepted solution]

You could do something like this in 4.0:

earliest=... latest=... | bin span=7d _time as week_start_time | convert ctime(week_start_time) timeformat="Week Starting %b %d" | chart count by tag::eventtype, week_start_time

In 4.1: you can do the following if you want your week to always start on a Sunday.

earliest=... latest=... | eval week_start_time = strftime(relative_time(_time,"@w0"), "Week Starting %b %d") | chart count by tag::eventtype, week_start_time

You can change @w0 to @w1 for monday, @w2 for tuesday .... etc

[gkanapathy]

Or just shorter:
earliest=-14d@w0 latest=@w0 | timechart span=7d count by tag::eventtype