I need stats on transactions (WAN outages) over a given period - 1 day, for instance - to be grouped by hour.
However, if a transaction starts in one hour and ends in the next or subsequent hours, it is counted in stats in each of the hours from start to end. It should be counted only once for the whole duration:
...|transaction host startswith="is down" endswith="is up"|stats count(duration) as "Count by hour" BY date _ hour | sort by date _ hour
I guess I need to count only start or end of each transaction, but can this be done in the same search, or is there yet a better way to achieve this?
MTIA
... | bucket _time span=1h | stats count by _time
Don't use the date_* fields, use _time.