Filter the output results based on custom date range

[ibob0304]

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.

[woodcock]

Based on your clarification, like this:

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

[woodcock]

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

[ibob0304]

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.

[ibob0304]

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

[gcusello]

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

your_search date_hour<12 
| ...

Bye.
Giuseppe

[ibob0304]

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

[gcusello]

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

[gcusello]

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

[ckunath]

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

[ibob0304]

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