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?
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"
I saw this. Unfortunately in our locked down corporate environment I don't think I can install apps like this.
<base query> (earliest=-169h@h latest=168h@h) OR earliest=@h | eval lastweek=if(_time>=relative_time(now(),"@h"),"lasthour","lastweek")
try lastweek variables.