topic Re: How to edit my search to exclude events outside of certain times of the day? in Splunk Search

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

SplunkLunk — Thu, 02 Mar 2017 20:10:29 GMT

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.

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

JDukeSplunk — Thu, 02 Mar 2017 20:21:12 GMT

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>

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

somesoni2 — Thu, 02 Mar 2017 20:40:50 GMT

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

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

DalJeanis — Thu, 02 Mar 2017 20:56:50 GMT

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"

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

SplunkLunk — Thu, 02 Mar 2017 21:00:42 GMT

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.

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

DalJeanis — Tue, 29 Sep 2020 13:03:38 GMT

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.

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

woodcock — Thu, 02 Mar 2017 21:12:14 GMT

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

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

somesoni2 — Tue, 29 Sep 2020 13:03:41 GMT

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) )

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

SplunkLunk — Tue, 29 Sep 2020 13:05:48 GMT

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.

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

SplunkLunk — Tue, 29 Sep 2020 13:05:50 GMT

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

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

DalJeanis — Thu, 02 Mar 2017 21:46:21 GMT

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.)

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

woodcock — Thu, 02 Mar 2017 21:48:15 GMT

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

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

SplunkLunk — Thu, 09 Mar 2017 13:42:53 GMT

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.

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

woodcock — Thu, 09 Mar 2017 14:51:32 GMT

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