Splunk: comparing a current value with one from 24 hours ago

user_1959973 0
Viewed 122

I have a data source in splunk that queries a database for metrics; the query runs once an hour, and returns JSON that looks something like this:

{"counts": {
    "visible":19608,"invisible":22360,"total":41968,
    "breakdown_list":{ 
        "sub_1":{ "name":"First Sub Topic" ,"total":247 ,"visible":238 ,"invisible":9 },
        "sub_2":{ "name":"Second Sub Topic" ,"total":16 ,"visible":0 ,"invisible":16 },
        ...}
    }
}

The story I'm working on now says that Splunk should raise an alert when the top-level visible count drops 10% from the value 24 hours ago.

I can get the current value like this:

index="my_index" source="My_Dev_Stats" | head 1 | spath path=counts.visible output=vis_now

And I can get the 24-hour old value like this:

index="my_index" source="My_Dev_Stats" latest=-1d | head 1 | spath path=counts.visible output=vis_yesterday

But I'm drawing a blank on how to get the results of the two queries on the same page so I can alert when

(vis_yesterday - vis_now)/vis_yesterday > 0.10

As I'm very new to splunk, I'd also appreciate any pointers on how to write queries efficiently and idiomatically.

splunk
2 Answers

There's more than one way to do that. Perhaps the easiest is to use the appendcols command to combine the two queries.

index="my_index" source="My_Dev_Stats" 
| head 1 
| spath path=counts.visible output=vis_now
| appendcols [ search index="my_index" source="My_Dev_Stats" latest=-1d 
  | head 1 
  | spath path=counts.visible output=vis_yesterday ]
| where ((vis_yesterday - vis_now)/vis_yesterday) > 0.10
user_22274200

I got someone local to help work out a solution:

index="my_index"  source="My_Dev_Stats" | head 1 | spath path=counts.visible output=vis_now
| table vis_now
| join host
    [| search "my_index"  source="My_Dev_Stats" latest=-1d | head 1 | spath path=counts.visible output=vis_yesterday
    | table vis_yesterday]
| eval delta=(vis_yesterday - vis_now)/vis_yesterday

And then run the query once an hour, and alert when delta > 0.10

user_19599730
Related