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.
Based on your clarification, like this:
| eval myDate=relative_time(now(), "@d+12h")
| search Outputdate < myDate
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
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.
| 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.
Hi ibob0304,
you have to insert in you search the additional condition date_hour<12
, e.g.:
your_search date_hour<12
| ...
Bye.
Giuseppe
This doesnt work because my data comes from db and no physical logs
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
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
Is each line with an outputdate its own event? Is outputdate the timestamp of your event?
there are no traditional events, data comes from database, and each row has its own outputdate..