Splunk Search

Compare percentages with a week ago

ShagVT
Path Finder

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_nameLast hourLast Week
event195%96%
event285%41%
event372%100%
event425%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?

Labels (3)
Tags (1)
0 Karma

ShagVT
Path Finder

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"

0 Karma

s2_splunk
Splunk Employee
Splunk Employee

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.

0 Karma

ShagVT
Path Finder

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

0 Karma

to4kawa
Ultra Champion

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

 

try lastweek variables.

0 Karma
Get Updates on the Splunk Community!

Improve Your Security Posture

Watch NowImprove Your Security PostureCustomers are at the center of everything we do at Splunk and security ...

Maximize the Value from Microsoft Defender with Splunk

 Watch NowJoin Splunk and Sens Consulting for this Security Edition Tech TalkWho should attend:  Security ...

This Week's Community Digest - Splunk Community Happenings [6.27.22]

Get the latest news and updates from the Splunk Community here! News From Splunk Answers ✍️ Splunk Answers is ...