When using the datetime_utc_to_local and format_datetime functions, I've noticed the min and max functions become unaware on how to read the datetime and produce the wrong results.
Have I written this query wrong? What's the alternative way to do this?
DeviceInfo
| where TimeGenerated > ago(30d)
| extend ['Generated Time AEST'] = format_datetime(datetime_utc_to_local(TimeGenerated,'Australia/Canberra'), 'dd-MM-yy [hh:mm:ss tt]')
| summarize count(), min(['Generated Time AEST']), max(['Generated Time AEST']) by DeviceName
Min and max function take the input as string and not datetime format; This is the reason that the min and max data for date column is not coming correctly. It is better to summarize before formatting the TimeGenerated column. After summarizing,you can format the min and max column.Below is the code:
DeviceInfo
| where TimeGenerated > ago(30d)
| summarize count(),min_value=min(['TimeGenerated']),max_value=max(['TimeGenerated']) by DeviceName
| extend min_value=format_datetime(datetime_utc_to_local(min_value,'Australia/Canberra'),'dd-MM-yy [hh:mm:ss tt]'),
max_value=format_datetime(datetime_utc_to_local(max_value,'Australia/Canberra'),'dd-MM-yy [hh:mm:ss tt]')