How do I refine this search to use 'my_report_date' instead of _time?

[mistydennis]

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

[bowesmana · Accepted solution]

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