Splunk Search

How to edit my search to exclude events outside of certain times of the day?

SplunkLunk
Path Finder

Greetings,

I need to run a search and only pull certain events that happen between midnight and 5:00 AM. So I use the date_time function in the following search:

index=[my index] sourcetype="[my source type]" [|inputlookup Admins.csv]| search date_hour>=0 date_hour<=5|rename _time AS Time |table Time, IP_ADDRESS, USER_ID, USER_NAME, SESSION_CREATE_TIME |sort USER_NAME, -SESSION_CREATE_TIME |convert timeformat="%a %b %d, %Y %I:%M:%S %p" ctime(Time) As Time

The problem is it also includes events between 5:00 AM and 5:59 AM since it's looking at the hour. I could change it so date_time<5 but then someone could log in exactly at 5:00 AM and avoid the report. Is there a way around that (even if I could search for events between midnight and 5:01 AM)? I realize it's extremely unlikely, but I'd like to make sure that it includes any "5:00 AM" events should they occur.

0 Karma
1 Solution

woodcock
Esteemed Legend

First of all, DO NOT use the "free" date_* values because they are PRE-TZ-normalization artifacts and are (almost) always wrong. Instead, calculate your own like this:

index=[my index] sourcetype="[my source type]" [|inputlookup Admins.csv]
| eval date_hourmin=strftime(_time, "%H%M")
| search date_hourmin<=500
| rename _time AS Time
| table Time, IP_ADDRESS, USER_ID, USER_NAME, SESSION_CREATE_TIME
| sort USER_NAME, -SESSION_CREATE_TIME
| convert timeformat="%a %b %d, %Y %I:%M:%S %p" ctime(Time) As Time

View solution in original post

woodcock
Esteemed Legend

First of all, DO NOT use the "free" date_* values because they are PRE-TZ-normalization artifacts and are (almost) always wrong. Instead, calculate your own like this:

index=[my index] sourcetype="[my source type]" [|inputlookup Admins.csv]
| eval date_hourmin=strftime(_time, "%H%M")
| search date_hourmin<=500
| rename _time AS Time
| table Time, IP_ADDRESS, USER_ID, USER_NAME, SESSION_CREATE_TIME
| sort USER_NAME, -SESSION_CREATE_TIME
| convert timeformat="%a %b %d, %Y %I:%M:%S %p" ctime(Time) As Time

SplunkLunk
Path Finder

Thanks. I've revised my search based on your comments. It's pulls up the same results as the previous search but if I may run into time zone issues then I'll stick with this one.

0 Karma

woodcock
Esteemed Legend

Plus, there are many inputs that do not have the `date_* fields.

0 Karma

DalJeanis
SplunkTrust
SplunkTrust

Use two tests, one for before 5:00 AM and one for exactly 5:00 AM.

index=[my index] sourcetype="[my source type]" [|inputlookup Admins.csv]
| search (date_hour<5) OR (date_hour=5 AND date_minute=0)
| rename _time AS Time 
| table Time, IP_ADDRESS, USER_ID, USER_NAME, SESSION_CREATE_TIME 
| sort 0 USER_NAME, -SESSION_CREATE_TIME 
| convert timeformat="%a %b %d, %Y %I:%M:%S %p" ctime(Time) As Time

Notes -
I've counted anything in that minute from 5:00:00 to 5:00:59 as a 5:00 AM logon. You could add (...AND date_SECOND=0) if you prefer.

I have added the qualifier 0 to your sort command to give you all results, just in case you receive more results than the default limit of 10000.


You could also do this. Basically, just format the time and then test again, but the above will be more efficient.

| search date_hour>=0 date_hour<=5|
| eval mytime=strftime(_time,"%H:%M:%S") | where mytime<="05:00:00"

SplunkLunk
Path Finder

Winner, Winner, Chicken Dinner! My revised query looks like:

index=[my index] sourcetype="[my source]" [|inputlookup Admins.csv]
|search (date_hour>=0 AND date_hour<5) OR (date_hour=5 date_minute=0) |rename _time AS Time
|table Time, IP_ADDRESS, USER_ID, USER_NAME, SESSION_CREATE_TIME |sort -SESSION_CREATE_TIME |convert timeformat="%a %b %d, %Y %I:%M:%S %p" ctime(Time) As Time

I tested it out by expanding the search through 8:00 AM and got lots of results during the 8:00 AM hour. Picked one event at 8:26 AM and added the OR (date_hour=8 date_minute=26) and confirmed the 8:26 AM event showed in the results and none of the other events during the 8:00 hour showed up. So this tells me the above query will show all events from between midnight and 5:00 AM exactly.

0 Karma

woodcock
Esteemed Legend

Please take a look at my answer; you are in danger of bad conclusions!

0 Karma

DalJeanis
SplunkTrust
SplunkTrust

Thanks. Be sure to upvote somesoni2's answer as well, which was basically the same answer.

Also, see woodcock's note about time values, which are notoriously squirrelly as soon as you have the temerity to crawl outside a single time zone...

(Your organization's mileage may vary, and it may be measured in kilometers or leagues.)

0 Karma

somesoni2
SplunkTrust
SplunkTrust

Try like this

Updated

index=[my index] sourcetype="[my source type]" [|inputlookup Admins.csv]| | search NOT ( (date_hour>=0 AND date_hour<5) OR (date_hour=5  date_minute=0) ) |rename _time AS Time |table Time, IP_ADDRESS, USER_ID, USER_NAME, SESSION_CREATE_TIME |sort USER_NAME, -SESSION_CREATE_TIME |convert timeformat="%a %b %d, %Y %I:%M:%S %p" ctime(Time) As Time

If you want to be more specific, include date_second as well.

index=[my index] sourcetype="[my source type]" [|inputlookup Admins.csv]| | search NOT ( (date_hour>=0 AND date_hour<5) OR (date_hour=5  date_minute=0 date_second=0) )|rename _time AS Time |table Time, IP_ADDRESS, USER_ID, USER_NAME, SESSION_CREATE_TIME |sort USER_NAME, -SESSION_CREATE_TIME |convert timeformat="%a %b %d, %Y %I:%M:%S %p" ctime(Time) As Time

SplunkLunk
Path Finder

I think this will work. I have no sure fire way to test it unless I somehow try to have someone log in exactly at 5:00 AM. However, if I read your solution correctly it's saying not to exclude anything 5:00 AM or less even though it would have a hour of "5". That should be work. Thanks.

0 Karma

DalJeanis
SplunkTrust
SplunkTrust

I believe the "NOT (date_hour=5 AND date_minute=0)" is the reverse of the desired effect. The two conditions before it should be in parenthesis, and the NOT should be OR.

I believe the date_hour>=0 is superfluous as well, since it will never be negative, and can be eliminated.

SplunkLunk
Path Finder

Winner, Winner, Chicken Dinner! My revised query looks like:

index=[my index] sourcetype="[my source]" [|inputlookup Admins.csv]
|search (date_hour>=0 AND date_hour<5) OR (date_hour=5 date_minute=0) |rename _time AS Time |table Time, IP_ADDRESS, USER_ID, USER_NAME, SESSION_CREATE_TIME |sort -SESSION_CREATE_TIME |convert timeformat="%a %b %d, %Y %I:%M:%S %p" ctime(Time) As Time

I tested it out by expanding the search through 8:00 AM and got lots of results during the 8:00 AM hour. Picked one event at 8:26 AM and added the OR (date_hour=8 date_minute=26) and confirmed the 8:26 AM event showed in the results and none of the other events during the 8:00 hour showed up. So this tells me the above query will show all events from between midnight and 5:00 AM exactly.

0 Karma

somesoni2
SplunkTrust
SplunkTrust

Thank you @DalJeanis for catching that. Updated the condition. FOr anyone want to test date_hour and date_minute values will be returned, can try this runanywhere search

| gentimes start=-1 | eval date_hour=mvrange(0,24) | table date_hour | mvexpand date_hour | eval date_minute=mvrange(0,60)  | mvexpand date_minute | search NOT ( (date_hour>=0 AND date_hour<5) OR (date_hour=5  date_minute=0) )
0 Karma

JDukeSplunk
Builder

It might be sloppy, but one of my dashboards has hour-exclusion logic for this reason.

The code that gets generated is something like this. date_hour didn't work for us on this one.

Were doing just the oppistie of you.. We want to exclude from midnight to 6 AM.

|eval hours=strftime(_time, "%H")
|where (NOT hours=0 AND NOT hours=1 AND NOT hours=2 AND NOT hours=3 AND NOT hours=4 AND NOT hours=5) 

The dashboard code is such, if you care.

 <input type="multiselect" token="exclude">
      <label>Exclue Hours</label>
      <prefix>(NOT hours=25 AND </prefix>
      <choice value="=25">None</choice>
      <choice value="=0">12AM</choice>
      <choice value="=1">1AM</choice>
      <choice value="=2">2AM</choice>
      <choice value="=3">3AM</choice>
      <choice value="=4">4AM</choice>
      <choice value="=5">5AM</choice>
      <choice value="=6">6AM</choice>
      <choice value="=7">7AM</choice>
      <choice value="=8">8AM</choice>
      <choice value="=9">9AM</choice>
      <choice value="=10">10AM</choice>
      <choice value="=11">11AM</choice>
      <choice value="=12">12PM</choice>
      <choice value="=13">1PM</choice>
      <choice value="=14">2PM</choice>
      <choice value="=15">3PM</choice>
      <choice value="=16">4PM</choice>
      <choice value="=17">5PM</choice>
      <choice value="=18">6PM</choice>
      <choice value="=19">7PM</choice>
      <choice value="=20">8PM</choice>
      <choice value="=21">9PM</choice>
      <choice value="=22">10PM</choice>
      <choice value="=23">11PM</choice>
      <suffix>)</suffix>
      <delimiter> AND </delimiter>
      <valuePrefix>NOT hours</valuePrefix>
      <default>=0,=1,=2,=3,=4,=23</default>
    </input>
0 Karma
Get Updates on the Splunk Community!

Index This | I am a number, but when you add ‘G’ to me, I go away. What number am I?

March 2024 Edition Hayyy Splunk Education Enthusiasts and the Eternally Curious!  We’re back with another ...

What’s New in Splunk App for PCI Compliance 5.3.1?

The Splunk App for PCI Compliance allows customers to extend the power of their existing Splunk solution with ...

Extending Observability Content to Splunk Cloud

Register to join us !   In this Extending Observability Content to Splunk Cloud Tech Talk, you'll see how to ...