Better splunk alerts with delta and predict keyword

Absolute value threshold triggered alert

The splunk triggers when a quantity hits certain threshold.

For example, a bookstore business want to get alert for abnormal ebook sell amount. A splunk trigger like the following could prompt the owner to check issues if the sell is too low during a particular hour.
The following query can be scheduled to run every hour at 0 minutes past the hour at number of results grater than 0.

index=Sell sourcetype=Ebook Stores IN (amazon109, ebay38,amazon339) (ACCOUNT_NAME=*-seller123-*)
| eval platform = case(MATCH(SERVER_NAME, "aws-*"),"AWS",1=1,"EBAY")
| bin _time span=1h
| stats count by _time, stores, platform
| where count < 10
| eval comment="see instruction http://bookstore.com/handle-low-sell.htm"

When triggered, we can post a message to slack channel with message

$name$
$description$
The sells amount dropped under 10 in the last hour

sells on store $result.stores$ in platform $result.platform$ is $result.count$, 
which is lower than the threshold in the last hour. 

Change triggered alert with delta keyword

This trigger won't be very useful if the bookstore is seasonal. In the busy days, this trigger might never get triggered and at slow days, this trigger might get triggered every single hour. What we are more concerned about is not the absolute amount of sell but the abnormal sudden spikes compare to the background curve.

In order to accomplish this goal, we need to modify the above query to compare the event corresponding to one bin to event corresponding to prior bin. For example, compare the sell count to the sell count one hours ago in order to get the difference.

index=Sell sourcetype=Ebook Stores IN (amazon109, ebay38,amazon339) (ACCOUNT_NAME=*-seller123-*)
| eval platform = case(MATCH(SERVER_NAME, "aws-*"),"AWS",1=1,"EBAY")
| bin _time span=1h
| stats count by _time, stores, platform
| delta count as sellchange p=1
| eval percentIncrease=(sellchange/count)
| where percentIncrease < -0.3
| eval comment="see instruction http://bookstore.com/handle-low-sell.htm"

Here we used a splunk keyword delta with p as parameter.
The delta keyword computes the difference between nearby results using the value of a specific numeric field. For each event where field is a number, the delta command computes the difference, in search order, between the field value for the event and the field value for the previous event. The delta command writes this difference into newfield. If the newfield argument is not specified, then the delta command uses delta(field). If field is not a number in either of the two values, no output field is generated.

By calculating the difference from hour to hour, we will detect sudden changes in counts, averages etc. between the hours, which generally means abnormal. This example has bin size 1 hour and p=1, that is hour over hour compare. For day over day comparison, we can use p=24, week over week comparison, we can use p=168. The p parameter has to be used together with the bin size. If bin size is 1h, p=1 means compare this hour with last hour. If bin size is 5m, p=1 means compare the latest 5 minutes with the previous 5 minutes. With small bin size, the trigger will be very sensitive to small and fast changes, with larger bin size, the trigger will be less fussy, small and rapid spikes can be smooth out while bigger problems get revealed.

Machine learning backed alert with predict keyword 

Splunk search result can be piped into machine learning algorithm, then we can use the previous data to predict the current data, if the actual data deviate from the prediction too much, an alarm should be triggered.

For example, the basic alarm can be modified to 

index=Sell sourcetype=Ebook Stores IN (amazon109, ebay38,amazon339) (ACCOUNT_NAME=*-seller123-*) earliest=-7d@h latest=now
| eval platform = case(MATCH(SERVER_NAME, "aws-*"),"AWS",1=1,"EBAY")
| timechart span=1h count 
| predict count algorithm=LLP period=24 holdback=24 future_timespan=23 upper99=high lower99=low as Prediction

| rename low(Prediction) as lowerThreshold

| rename high(Prediction) as UpperThreshold

| tail 24 | tail 23

| eval Result = case(count > UpperThreshold, "sellTooGood", count >= LowerThreshold, "Expected", count < LowerThreshold, "sellTooBad")

| where Result="sellTooGood" OR Result="sellTooBad"
| eval comment="see instruction http://bookstore.com/handle-low-sell.htm"

This query use LLP seasonal local prediction algorithm to make prediction. Based on the assumption that the sell amount has 24 hours cycle, we set period=24. future_timespan specify how many data points we want to predict, holdback specify how many latest points we don't want to use for the prediction. The upper99 and lower99 can be modified to upper90 lower90 etc. It decides how much we can tolerate false positive. When the LowerThreshold triggers alert. upper90 will have less false positive than upper99.

Alert strategy

When setting alerts, we are facing the eternal question how much we should alert. If the alert is too sensitive, we get lots of false alarms, on the other extreme,  we might miss critical events that could hurt us a lot for even one miss! A typical scenario is, an instructor bought 100 books in one hour, in the next hour, the sell count is normal, say 20. The change alert will be triggered, but that is a normal situation. If the 100 book sell event happens at mid-night and the alert triggered a pagerduty call, the responder might get annoyed. In another mid-night, a disk failure could also trigger the change alert, in that case, ignore it will result in financial loss or harm the business reputation. Of course we can amend the existing alert for this particular case, but another 2 similar situations could trigger the alert, the story goes on and on.

No matter what technology we use, we have to sample then decide to be or not to be, there is no silver bullet unfortunately. Someone has to apply domain knowledge to decide what is expected and what is not. However, we can use a set of alerts combined with other technologies to alleviate the problem if cut a precise decision threshold in one alert is hard.

One dimension we can engineer is the splunk sample strategy, we can use different strategies to sample the data. Lets call it quick-early, quick-late, slow-late strategies.

• quick-early. If you want to have a "quick" alarm, then sample every 5 mins for the last 5 mins. This will have most false positives, but it will capture most of the fast changing abnormal signals as soon as possible.
• quick-late. If you want to have a "quick" alarm but take into consideration more time, then sample every 5 mins, but for the last 15 minutes. This will get rid of the majority of false positives, however, the abnormal signals will be detected later than the previous strategy.
• slow-late. If you want to have a "slow" alarm, then sample every 15 mins for the last 15 mins. This will have the lest false positives, but it has chance to ignore fast changing abnormals and is also slower to detect the abnormals than the quick-early strategy.

For example, we can create a splunk query that summarizes each hour over 15 hours and only trigger if the hour-based-alarm goes off for more than a few times. Set the alarm for a 5 hour run summarizing over 15 hours, and that should even out the fast changing spikes thus reduce the false positives.

Another dimension we can engineer is how we response to the alert. For example, if the alert is configured to trigger pagerduty, we can configure the pagerduty response strategy. We can have an alarm go off and then auto snooze or auto resolve if it does not go off again. This way we can have it alert us and set it to low priority, but escalate it to high priority if it happens again or let it naturally be resolved by pager duty if it does not reoccur in X number of hours. 

Like a work group need different types of team members, we can use a group of splunk alerts to monitor the same concern. For example, we can have one alert with low-priority but covers a lot of false positives, we can set the alert to be auto resolved if not repeating or simply log the alert message to a slack channel or send an email for later review. We can have other alerts with high-priority, which only capture the obvious issues and trigger pagerduty. 

How to group splunk stats by common string in field value

We all know splunk can make time chart. For example, we want to know how many http requests are received on a particular type of servers. A typical splunk query could be:

index=http_stats_10d sourcetype=FRONT_END_LB host=*-mobileweb-* | timechart count by host

The timechart will be grouped by host such as pvd-mobileweb-001, pvd-mobileweb-002, pvd-mobileweb-003, chi-mobileweb-001, chi-mobileweb-002, tor-mobileweb-001, tor-mobileweb-002, tor-mobileweb-003.

Now let's assume we want to group the timechart by data site prefix string pvd, chi and tor instead of the whole hostname string. The following technique will do the trick.

eval site=mvindex(split(host, "-"), 0)

the above command reads, split host string by "-" and take the the index 0 element from the result array, and assign it to variable site. This way we extracts the prefix from the host string.

Now we can revise our splunk query to group by site instead of by host.

index=http_stats_10d sourcetype=FRONT_END_LB host=*-mobileweb-*
| eval site=mvindex(split(host, "-"),0)
| timechart count by site

SQL table join equivalent for splunk index join

splunk have a feature to join 2 indexes data together by a field. This solution is similar to sql's table join by column.
Here we can have have these analogy:

• splunk query<-> sql query, 
• splunk index <->sql table, 
• splunk field <-> sql column.
• splunk subquery join <-> sql table join

Subquery join

For example, you have index service_request and another index service_bill

For service_request index, you have a splunk query to get all the services request for a particular vendor.
index="service_request" vendor=Dell

For service_bill index, you have a query to get all service bills that are pending
index="service_bill" PENDING

Now, the problem is the billing PENDING status field is not in service_request index, so if we want to generate stats such as the percentage of service requests that are in complete status has not been paid, we need to join the two indexes. To join them, we need common field, assume both index has request_id field. We can then write the following splunk query to join the two indexes.

index="service_request" vendor=Dell
| eval REQUEST_ID=request_id
| join REQUEST_ID [search index="service_bill" PENDING | fields REQUEST_ID]
| stats count AS total, count(eval(service_status="COMPLETE")) as outOfStatus
| stats avg(eval(outOfStatus/total)*100) as outOfStatusRate

There is an important detail: the splunk subsearch query result can have maximum 10500 records. If you are query a days of data, you might be fine, if you are querying 10 year of data, you are most likely exceed the limit.
If query
index="service_bill" PENDING
returns more than 10500 rows, your final stats will be wrong!

Another import details is, when join a query and a subquery by common field, the two field names have to be exact match, upper case and lower case letters are considered different. In the example, field name request_id in index="service_request" has to be mapped to upper case REQUEST_ID with "eval REQUEST_ID=request_id" in order to match the field name REQUEST_ID in index="service_bill" before we join them.

This limitation is set by splunk platform, we can not really do much about it. One thing we can do as splunk user is to prune the subquery to return only the records we needed.
for example, if the service_bill index also has a VENDOR field, use it to prune out those rows won't be relevant.
search index="service_bill" PENDING VENDOR="DELL"

and the join will be

index="service_request" vendor=Dell
| eval REQUEST_ID=request_id
| join REQUEST_ID [search index="service_bill" PENDING VENDOR="DELL" | fields REQUEST_ID]
| stats count AS total, count(eval(service_status="COMPLETE")) as outOfStatus
| stats avg(eval(outOfStatus/total)*100) as outOfStatusRate

that way, we can do stats with larger time span.

The index subquery join technique can also be applied to generate timechart. For example, we want to know further about the trending of payment pending time for those requests. We can add that field from the subquery

index="service_request" vendor=Dell
| eval REQUEST_ID=request_id
| join REQUEST_ID [search index="service_bill" PENDING VENDOR="DELL" | fields REQUEST_ID, PAYMENT_DUE_HOURS]
| timechart span=1h avg(PAYMENT_DUE_HOURS) by service_status

Subquery Inner join vs. Outer join

In the above example, 

...query... | join common_field [...subquery...] 

| timechart/stats/ect.

The "join" keyword did an inner join, the main query and subquery need to have a common field. After join, we actually appended a few fields from the subquery to each events in the main query. The augmented main query events can then be used normally. We can filter it, pipe it to stats command or timechart command etc. The final effect is an inner join, but the splunk implementation 

run subquery query 

search index="service_bill" PENDING VENDOR="DELL" 

for the time range the main query use, then perform an inner join. So if the above query has more than 10500 events, the results set get truncated.

There is another kind of query and subquery join in splunk

...query...| append [...subquery...]

|timechart/stats/etc.

The "append" keyword did an outer join, the two indexes event fields don't have anything common exact they are arranged by time, the event set A of main query and the event set B of subquery are simply outer joined together, so we get A+B number of events.

Subquery append will be useful if we want to generate the time chart of 2 field's ratio from different indexes that share no common field

For example, we have one index impression_stats to store the advertisement impression events, and another index buy_stats to store the advertisement click events. We want to get the time chart of ad impression count to ad click count during a time period.

We can use the following example query:

index="impression_stats" platform="blogspot" | bin _time=1h | stats count as impression by _time

| append [search index="buy_stats" sourcetype="web_ad" | bin _time=1h | stats count as buy by _time]

| stats max(impression) as a, max(buy) as b by _time

| eval c=rount((b/a),0)

| stats max(c) as "Click-to-Impression Ratio" by _time

Since we don't have to coordinate the results of the main query and subquery other than _time field, we can  reduce the amount of records in the subquery final with bin and stats. 

After put the records in buckets of 1 hour then count the number of events in that bucket, we reduce the original event set to its 1 hour interval time based statistics events sets. If we have to make stats for bigger time span thus need to have more events in the subarray, we can increase the time interval in "bin _time=1h" so that we reduce the results set more aggressively, in order to not exceed the 10500 maximum events counts in subquery results. The main query and subquery don't have common field, how they are outer joined? They are outer joined by _time field. So if subquery use bin side 1 hour, the main query have to use the same bin size 1 hour, otherwise, the later stats by  _time won't make sense.

Here we use the following technique to coordinate the counts from main query and subquery. 

| stats max(impression) as a, max(buy) as b by _time

At a particular _time value, we will have 2 events, one is from main query, another is from subquery. With outer join, the main query's impression field will has a positive value, the subquery's impression field will has a default/null value as 0. 

max(impression) 

will get the non-zero value from the two. For the same reason, 

max(buy) 

will get the non-zero value from the 2 events as well. 

So at the next step, we can get the ratio  with

| eval c=rount((b/a),0)

finally, we can draw the time chart with command

| stats max(c) as "Click-to-Impression Ratio" by _time

Of course, nothing prevent us from putting a multiple curves together with the same _time field as x-axis.

index="impression_stats" platform="blogspot" | bin _time=1h | stats count as impression by _time

| append [search index="buy_stats" sourcetype="web_ad" | bin _time=1h | stats count as buy by _time]

| stats max(impression) as a, max(buy) as b by _time

| eval c=rount((b/a),0)

| stats max(a) as "Impression", max(b) as "Click", max(c) as "Click-to-Impression Ratio" by _time