Compare percentages with a week ago

[ShagVT]

Hello all, I'm trying to put together a dashboard that - among other things - compares the success rate of various transactions over the last hour with the same hour a week ago.  My base search results in rows that have two fields I particularly care about: event_name and event_status.

My desired outcome would look something like this:

event_name	Last hour	Last Week
event1	95%	96%
event2	85%	41%
event3	72%	100%
event4	25%	69%

Here is the current query i have, which seems to basically work.

<base query> earliest=-169h@h latest=now
| fields + event_name, event_status, _time
| fields - _raw
| eval weekAgoHour = relative_time(now(), "-168h@h")
| eval lastHour = relative_time(now(), "-1h@h")
| eval ReportKey = "omit"
| eval ReportKey = case(_time < weekAgoHour,"Last Week", _time > lastHour,"Last Hour")
| where ReportKey != "omit"
| eventstats count(eval(event_status=="FAILED")) as FailedCount, count(eval(event_status=="SUCCESS")) as SuccessCount by event_name, ReportKey
| eval pctSuccess = round(SuccessCount/(SuccessCount+FailedCount)*100, 1)."%"
| chart values(pctSuccess) by event_name, ReportKey

The problem here is that it has to look at hundreds of millions of irrelevant rows ... everything that has happened in the last 169 hours.  Surely there must be a more efficient way to do this?  Maybe with multisearch?

[ShagVT]

For other intrepid developers, here is the approach I have worked out which seems to have worked pretty well.  Again, event_status and event_name are my own fields.  The results are a little more complex as well ... they show both the absolute counts as well as the percentages.  Also, having the time controls on the inner search allowed me to create a dropdown on a dashboard that included a number of predefined selections for timing by creating a field that contains values like "earliest=-169h@h latest=-168h@h" which I can then insert as a simple variable like $previousTimeWindow$ in place of those fields in the inner query.

<base query>
| fields + event_name, event_status, _time
| fields - _raw
| stats count(eval(event_status=="SUCCESS")) as SuccessCount, count(eval(event_status=="FAILED")) as FailedCount by event_name
| eval "Percent Success" = round(SuccessCount/(SuccessCount+FailedCount)*100, 1)."%"
| eval ReportKey="Current"
| append
[ search <base query> earliest=-169h@h latest=-168h@h
| fields + event_name, event_status, _time
| fields - _raw
| stats count(eval(event_status=="SUCCESS")) as SuccessCount, count(eval(event_status=="FAILED")) as FailedCount by event_name
| eval "Percent Success" = round(SuccessCount/(SuccessCount+FailedCount)*100, 1)."%"
| eval ReportKey="Previous"]
| chart values(SuccessCount), values(FailedCount), values("Percent Success") by event_name, ReportKey
| rename "values(FailedCount): Current" as "Current Failed", "values(FailedCount): Previous" as "Previous Failed", "values(Percent Success): Current" as "Current Success Rate", "values(Percent Success): Previous" as "Previous Success Rate", "values(SuccessCount): Current" as "Current Success", "values(SuccessCount): Previous" as "Previous Success", "event_name" as "Event Name"
| fields "Event Name", "Current Success", "Current Failed", "Current Success Rate", "Previous Success", "Previous Failed", "Previous Success Rate"

[s2_splunk]

One option is to check out the Timewrap app (custom search command) to see if it meets your needs. It's dated, but should still work. Here is a Blog post from the author on the topic.

[ShagVT]

I saw this.  Unfortunately in our locked down corporate environment I don't think I can install apps like this.

[to4kawa]

<base query> (earliest=-169h@h latest=168h@h) OR earliest=@h | eval lastweek=if(_time>=relative_time(now(),"@h"),"lasthour","lastweek")

try lastweek variables.