Format the TimeGenerated Field Azure Log Analytics

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

format the timegenerated field

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')

format the timegenerated field

 

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 Cloud and Datacenter Engineer, specializing in monitoring and automation. Here you’ll find posts about System Center Operations Manager, Powershell, Hyper-V, Azure Automation, Azure Log Analytics and other Microsoft related technologies.