Splunk Search

Splunk Query To Evaluate Absence Of A Signature In the Logs

victorcorrea
Explorer

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.

Labels (2)
0 Karma
1 Solution

ITWhisperer
SplunkTrust
SplunkTrust

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)

View solution in original post

ITWhisperer
SplunkTrust
SplunkTrust

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
Explorer

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:

victorcorrea_0-1716479352501.png

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.

0 Karma

ITWhisperer
SplunkTrust
SplunkTrust

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
Explorer

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

0 Karma

ITWhisperer
SplunkTrust
SplunkTrust

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
Explorer

Beautiful.

Many thanks, @ITWhisperer !

0 Karma
Get Updates on the Splunk Community!

Optimize Cloud Monitoring

  TECH TALKS Optimize Cloud Monitoring Tuesday, August 13, 2024  |  11:00AM–12:00PM PST   Register to ...

What's New in Splunk Cloud Platform 9.2.2403?

Hi Splunky people! We are excited to share the newest updates in Splunk Cloud Platform 9.2.2403! Analysts can ...

Stay Connected: Your Guide to July and August Tech Talks, Office Hours, and Webinars!

Dive into our sizzling summer lineup for July and August Community Office Hours and Tech Talks. Scroll down to ...