Hi all - I'm attempting to write a query using earliest/latest based off a date field in the event, not _time. I've tried a dozen things, and no matter what I try the earliest/latest fields are not showing what I expect.
I'm using 'my_report_date' as the desired earliest/latest field. When I run the following search, the earliest should be 11/1/22, but it shows as 11/2 (these events were sent to a summary index prior to the events of 11/1). The rest of the query is finding the number of days between first/last events.
How do I refine this search to use 'my_report_date' instead of _time?
index=summary
| stats earliest(my_report_date) AS FirstFound, latest(my_report_date) AS LastFound by my_asset
| convert mktime(FirstFound) AS FirstFoundEpoch timeformat="%Y-%m-%d"
| convert mktime(LastFound) AS LastFoundEpoch timeformat="%Y-%m-%d"
| eval daysdiff=round((LastFoundEpoch-FirstFoundEpoch)/86400,0)
| stats count by my_asset, FirstFound, LastFound, daysdiff
The earliest/latest stats commands always base early/late on the event time. What you want is simply min/max of my_report_date, which must be an epoch, so you will need to convert it first, i.e.
| eval my_report_date=strptime(my_report_date, "%F")
| stats min(my_report_date) AS FirstFound, max(my_report_date) AS LastFound by my_asset
%F is shorthand for %Y-%m-%d
The earliest/latest stats commands always base early/late on the event time. What you want is simply min/max of my_report_date, which must be an epoch, so you will need to convert it first, i.e.
| eval my_report_date=strptime(my_report_date, "%F")
| stats min(my_report_date) AS FirstFound, max(my_report_date) AS LastFound by my_asset
%F is shorthand for %Y-%m-%d