How to specify time zone in an alert search, when excluding a specific time range?

[Scott_K]

How do I specify the time zone in an alert search where I need to exclude a specific time period?

- I want to exclude the time period of midnight to 12:20am UTC

- I want to be able to change my time zone in my preferences as needed

- I don't want to change the owner of my alert to "nobody"

After my basic search criteria I have this, which works as long as my profile is set to UTC:
| eval Hour=strftime(_time,"%H")
| eval Minute=strftime(_time,"%M")
| search NOT ( (Hour=00 AND Minute >= 00) AND (Hour=00 AND Minute <= 20) )

[PickleRick]

You touch several issues here.

One thing is that with strftime time is always formatted with the user's configured timezone. So if you do

| makeresults | eval _time=0

you will always get the epoch rendered with your user's timezone. There's no way around it - you can't render the timezone in another timezone. You can "cheat" a bit and modify the original timestamp to make the resulting string seem as if it was in another timezone but that's not the case we have and need here.

Another thing is that for searches the easiest and most effective way to speed up your searches is to limit the time as much as possible so the indexers only need to read from the buckets that really contain the interesting events. That's why you don't want to do

index=whatever earliest=0 | eval time=strftime("%F",_time) | where time="2022-10-16"

 You'd rather do

index=whatever earliest=@d

In case of big and active indexes it might make a difference if you would be skipping events from a whole bucket.

I'd go for dynamically generated time range using a subsearch. Use strptime to generate set of earliest/latest pairs to include/exclude.

For example - this search selects only events from midnight (current user's timezone but if you want another base time you should use strftime() instead of relative_time()) till 4AM for last 10 days.

index=winevents 
[| makeresults count=10 
| streamstats count 
| eval earliest=relative_time(now(),"@d")-(count*86400) 
| eval latest=relative_time(now(),"@d")-(count*86400)+14400
| table earliest latest ] 

[Scott_K]

@PickleRick My alert is configured to only search for "Last 5 minutes" and it runs on a chron schedule every 5 minutes. We need this to run all day, except we have events soon after midnight UTC we do not want to alert on, therefore I am excluding the time period from midnight to 12:20am UTC.

I want to be able to set my profile to use my local time zone. If I do that, I'll have to change my alert twice a year for Daylight Saving Time (since UTC doesn't change). And, I can't change my profile time zone as needed without breaking the alert time exception. So, what options do I have, if any?

[PickleRick]

OK. Firstly, let me say that "run every 5 minutes and search for last 5 minutes" is not a good practice.

You either end up with too many searches spawned at the same time and getting delayed/skipped. Or you define scheduling window and get holes or duplicates in your results because one run is gettng scheduled at 00:05:03 and covers time from 00:00:03 till 00:05:03 and next one is spawned at 00:10:23 so you get a "hole" between 00:05:03 and 00:05:23 and next one is spawned at 00:15:01 so you get events between 00:10:01 and 00:10:23 included twice.

Another thing is that if some of your data can get delayed, in transport and indexing, you might miss some of them with such searches - remember that _time is not the same as _indextime.

So it's better to have your searches aligned to some absolute time windows (like instead of running for "last hour" you search for "one hour before top of last hour" and one hour from that.

So that's a general remark.

So I'd go for creating a subsearch generating a earliest - latest pair of fields which:

1) Aligns the earliest and latest to - for example - 5 minutes (You can skip this one if you ignore my previous explanation XD)

2) "Breaks" the timestamps (like setting latest=1 - Splunk won't find anything if earliest>latest) in case the time conforms to some predefined condition - in your case - it's around midnight. To verify that you can use strptime without providing a timezone within the time string - in such case splunk should use your user's defined time zone.

[Scott_K]

I apologize for my ignorance, but I'm still confused on this.  I'll look into aligning my search to an absolute time window. But, for the sake of simplicity, assume I am running this every 5 minutes, searching only the last 5 minutes  (accepting the inherent problems with that). Trying to use your example, how would I prevent the alert from triggering when events are greater than 200 between midnight and 12:20am UTC. And what I wanted to change that to between midnight and 2:30am?

index=myindex source=logfile*
| eval earliest=relative_time(now(),"@d")-(count*86400)
| eval latest=relative_time(now(),"@d")-(count*86400)+14400
| stats count as Events by source
| where Events >=200

Thank you

[PickleRick · Accepted solution]

I'm not sure what you did but you seem to have cut the subsearch part and just left some parts of the subsearch in your main search.

The whole purpose of the subsearch is to generate a set of parameters for your main search.

The subsearch is evaluated before the main search and the results are provided as additional parameters to the main search.

So if I do a simple

index=whatever [ | makeresults
   | eval earliest=0,latest=now()
   | table earliest latest ]

Splunk will firstly run the subsearch, set the earliest to 0, the latest to current timestamp. And then return those parameters to the main search which will effectively turn to

index=whatever earliest=0 latest=a_current_timestamp

Of course a_current_timestamp is just a placeholder here - it would be substituted with the real timestamp.

So, after this a bit long intro...

You want a subsearch that sets your earliest and latest conditionally depending on the real time.

So the start is relatively easy

| makeresults
| eval earliest=now()-300
| eval latest=now()

Now the tricky part is that you don't want to return results if the current time falls within some time range. So just render the current time to a variable and use this variable to decide.

For example:

| eval hour=strftime(now(),"%H")

Mind you that it's a string so we have to either parse it into nuber with tonumber() or compare it as string.

For now the string will suffice - we filter by the hour

| where hour!="11"

This will make the search only return values if we call it outside of the 11:00:00-11:59:59 period (the hour part is not equal to 11)

In more complicated cases (and yours seems to be one - we'll get to it shortly), you can be tempted to just parse your range ends from string specifications and verify the boundaries with a simple numeric comparison but it gets tricky since simple HH:MM timestamps from the future are parsed as yesterday, not today. So it gets more and more confusing (as all time manipulation does)

And finally we just return two fields, not the auxiliary ones

| table earliest latest

But in your case the time comparison involves another timezone so it's even more tricky.

Since you want another timezone, you'd probably try to go for

| eval earliest_limit=strptime("00:00UTC","%H:%M%Z")

and then try using where to check how your now() compares.

The problem with this approach is that if you give a timestamp without a date Splunk will assume last 24hours. So if you try to search for "14:38" at 10AM, strptime() will return a yesterday's 14:38. So it's no good.

Luckily, as long as we don't need time periods that cross midnight (unix epoch is exactly at midnight UTC), we can use a neat trick to check the time part only. We can just do a mod 86400 operation to completely ignore whole days and be left with just the number of hours within a day.

So your whole search finally ends up something like this:

index=myindex source=logfile*
  [ | makeresults
    | eval earliest=now()-300
    | eval latest=now()
    | eval earliest_limit=strptime("00:00UTC","%H:%M%Z") % 86400
    | eval latest_limit=strftime("00:20UTC","%H:%M%Z") % 86400
    | eval now=now() % 86400
    | where NOT ( now>=earliest_limit AND now<=latest_limit)
    | table earliest latest ]
| stats count as Events by source
| where Events >=200

With different timezone or even UTC period containing midnight (like 9PM-2AM) you'd probably have to do a bit different contition (like rephrase your "not 9PM-2AM" to "2AM-9PM").

[Scott_K]

@PickleRick when I run this I get the error, "Error in 'EvalCommand': Type checking failed. '%' only takes numbers."

[PickleRick]

My typo. There should be strptime() both times, not strftime().

strptime parses time string into a timestamp

strftime does the oposite - renders timestamp into a time string.

Obviously you need strptime both times.

[Scott_K]

I got this working, so thank you. I could not get any search results with the brackets no matter how I modified it, so I removed them and the makeresults and all is well.

[Scott_K]

Thank you for the detailed explanation

[gcusello]

Hi  @Scott_K ,

you approach is correct, to always have a time period.

but, in this case (when the hour is the same and the starting minute is 0)  you can use a simpler solution:

| eval Hour=strftime(_time,"%H")
| eval Minute=strftime(_time,"%M")
| search NOT (Hour=0 Minute<=20)

Ciao.

Giuseppe

[Scott_K]

@gcusello I don't understand how this change (while not a bad change) impacts my time zone issue in any way?