I was recently writing a summary performance report binned by day. If you have used the summarize operator with bin you know that the TimeGenerated field has a lot of extra characters on it. So naturally my next question was how do you format the TimeGenerated Field? I took a look at the official format_datetime() scalar function on the language reference https://docs.loganalytics.io/docs/Language-Reference/Scalar-functions/format_datetime(). This isn’t a bad reference, in fact it details all the different fields you can put in the time field.
However, all the examples use the datetime() function inside the format_time() function. This input takes a string input and make it a time.
Before format, using the query:
Perf | summarize avg(CounterValue) by bin(TimeGenerated, 1d), Computer, CounterName
There is all those extra characters after the 27, this is nice to have when you want to read that, but for daily summarized data, its a bit ugly.
Now if I input the TimeGenerated field into format_datetime() using the following query:
Perf | summarize avg(CounterValue) by bin(TimeGenerated, 1d), Computer, CounterName | project Computer, CounterName, avg_CounterValue, format_datetime(TimeGenerated, 'MM-dd-yyyy')
That’s how we can use the format_datetime() function to format the TimeGenerated field for any style date you want.
Hi, I’m Billy York. I’m a Consultant at Microsoft, former Cloud and Datacenter Management MVP, specializing in monitoring and automation. Here you’ll find posts about AzureMonitor, LogAnalytics, System Center Operations Manager, Powershell, Hyper-V, Azure Automation, Azure Governance and other Microsoft related technologies.