Splunk Search

Filter the output results based on custom date range

ibob0304
Communicator

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.

Tags (3)
0 Karma

woodcock
Esteemed Legend

Based on your clarification, like this:

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

woodcock
Esteemed Legend

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
0 Karma

ibob0304
Communicator

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.

0 Karma

ibob0304
Communicator
| 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.

0 Karma

gcusello
SplunkTrust
SplunkTrust

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

your_search date_hour<12 
| ...

Bye.
Giuseppe

0 Karma

ibob0304
Communicator

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

0 Karma

gcusello
SplunkTrust
SplunkTrust

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

0 Karma

gcusello
SplunkTrust
SplunkTrust

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

0 Karma

ckunath
Communicator

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

0 Karma

ibob0304
Communicator

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

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