topic Re: Filter the output results based on custom date range in Splunk Search

Filter the output results based on custom date range

ibob0304 — Sat, 06 May 2017 04:48:24 GMT

I want to filter the output based on the below time format, I want keep only results until 12am not after 12am.

Outputdate
05/05/2017 14:50:22.970
05/05/2017 11:50:22.230
05/05/2017 10:40:22.137
05/05/2017 06:10:22.690
05/05/2017 03:35:22.340
05/04/2017 23:50:22.901
05/04/2017 21:50:22.620

If my alert trigger at 3pm or 9am any day, it should only contain results till 12am that early morning.

Desired output should be

05/04/2017 23:50:22.901
05/04/2017 21:50:22.620

I tried to use eval and relative time like where outputeval>=relative_time(outputdate,"-1d") but it doesn't work. Is there any other way to get the results only till 12am on that day or 23:59 last day ? This data is coming from Database and not traditional log events.

Re: Filter the output results based on custom date range

ckunath — Sat, 06 May 2017 07:42:52 GMT

Is each line with an outputdate its own event? Is outputdate the timestamp of your event?

Re: Filter the output results based on custom date range

gcusello — Sat, 06 May 2017 08:50:54 GMT

Hi ibob0304,
you have to insert in you search the additional condition date_hour<12, e.g.:

your_search date_hour<12 
| ...

Bye.
Giuseppe

Re: Filter the output results based on custom date range

ibob0304 — Sat, 06 May 2017 12:44:03 GMT

This doesnt work because my data comes from db and no physical logs

Re: Filter the output results based on custom date range

ibob0304 — Sat, 06 May 2017 12:46:06 GMT

there are no traditional events, data comes from database, and each row has its own outputdate..

Re: Filter the output results based on custom date range

gcusello — Tue, 29 Sep 2020 13:58:29 GMT

This means that the date to manipulate is in a field (e.g. your_field_date), the logic is the same, try something like this:
your_search
| eval datehour=strftime(strptime(your_field_date,"%m/%d/%Y %H:%M:%S.%3N","%H")
| where datehour<12
| ...

Bye.
Giuseppe

Re: Filter the output results based on custom date range

gcusello — Tue, 29 Sep 2020 13:58:32 GMT

ok this means that the date to use for check in in a different field (e.g. called your_field_date), the logic is the same, try something like this:
your_search
| eval datehour=strftime(strptime(your_field_date,"%m/%d/%Y %H:%M:%S.%3N"),"%H")
| where datehour<12
| ...

Bye.
Giuseppe

Re: Filter the output results based on custom date range

woodcock — Sat, 06 May 2017 15:48:19 GMT

Add this to the end of your search:

... | eval date_hourmin = strftime(strptime(Outputdate,"%m/%d/%Y %H:%M:%S.%3N"), "%H%M")
| search date_hourmin >= 1200

Re: Filter the output results based on custom date range

ibob0304 — Sun, 07 May 2017 14:27:23 GMT

This is filtering all data including old days.. I have 6000 records till today morning 7am, after applying above filter it is searching only >=1200 for all days and got only 3000ish records. But I just want filter for sameday or on that particular day. Till 12am today there are 5900records and I want the output of 5900records by filtering current day records. So when ever I run the report, it should keep all the records and remove current day results after 12am.

Re: Filter the output results based on custom date range

ibob0304 — Sun, 07 May 2017 17:37:16 GMT

| eval myDate=relative_time(now(),"-8h") | convert ctime(myDate)
| search  Outputdate < myDate

I tried this but this has to modify accordingly based on alert trigger time - output time.. I am looking for some dynamic query which can exclude all today results.

Re: Filter the output results based on custom date range

woodcock — Sun, 07 May 2017 18:23:30 GMT

Based on your clarification, like this:

| eval myDate=relative_time(now(), "@d+12h")
| search  Outputdate < myDate