Splunk Query To Evaluate Absence Of A Signature In the Logs

[victorcorrea]

Hi Splunk Community,

I need to build an alert that will be triggered if a specific signature is not present in the logs for a period of time.

The message shows up in the logs every 3 or 4 seconds in BAU conditions, but there are some instances of longer intervals going up to 4 minutes.

What I had in mind was a query that ran over a 15-time timeframe using 5-minute buckets - to ensure that I would catch the negative trend and not only the one offs.

I have made it this far in the query:

index=its-em-pbus3-app "Checking the receive queue for a message of size"
| bin _time span=5m aligntime=@m
| eval day_of_week = strftime(_time,"%A")
| where NOT (day_of_week="Saturday" OR day_of_week="Sunday")
| eval date_hour = strftime(_time, "%H") 
| where (date_hour > 7 AND date_hour < 19)
| stats count by _time 

**I only need the results for Monday to Friday between the hours of 7AM and 7PM.

The query returns the count by _time, which is great, but if the signature is not present I don't get any hits, obviously.

So I can count the number of occurrences within the 5-minute buckets, but I can't assess the intervals or determine the absence using count.

I thought of, perhaps, manipulating timestamps so I could calculate the difference between current time and the last timestamp of the event, but I am not exactly sure how to compare a timestamp to "now".

I would appreciate if I could get some advice on either how to count "nulls" or how to cross-reference the timestamps of the signature against current time.

Thank you all in advance.

[ITWhisperer]

The timechart command will fill in the missing timeslots for you

| where (date_hour >= 7 AND date_hour < 19)
| timechart span=5m count

 (btw, you probably want >= 7 if you want from 7am) 

[victorcorrea]

Hi @ITWhisperer ,

Thanks for reply and catching the issue with the date_hour operator. You're absolutely right as I do want to include 7AM.

I tried using timechart instead of stats in the query and selected a timeframe during which I know for a fact that there are no occurrences of the event containing the signature.

I was expecting to see a count of 0 but instead I got no results:

I tried adding fillnull to the bottom of the query as I thought that, maybe, the count was returning null instead of "0" to no avail.

[ITWhisperer · Accepted solution]

You need at least 1 result during the period to get the results filled in. If there are no results, you could try something like this

| appendpipe
    [| stats count as _count
    | where _count=0
    | addinfo
    | rename info_min_time as _time 
    | eval count=0
    | fields _time count]
| timechart sum(count) as count
| eval count=coalesce(count,0)

[victorcorrea]

Many thanks, @ITWhisperer .

That did the trick beautifully. The only bit I didn't quite understand in the query was the renaming the info_min_time as _time, I'll accept the comment as solution, but if you'd be so kind to shed some light on what that line is doing it would be wonderful.

Thanks again,
Victor

[ITWhisperer]

If the _count is zero, there are no events, so the addinfo and rename is setting a value for the _time field for the event being added by append pipe to be the start time of the search period. This is so that the subsequent timechart command has at least one event with a _time value in the search period. With that one event, the timechart command will then fill in the missing time slots.

[victorcorrea]

Beautiful.

Many thanks, @ITWhisperer !