How to convert datetime format on Azure Logs Query

user_8465396 0
Viewed 74

I want to format the datetime on Azure Log this is the date time format

DATETIME = 01/Sep/2022:04:48:11 +0000

I tried to split and get 01/Sep/2022 but it wont convert

SampleLog_CL 
| extend raw = parse_json(RawData).log
| parse raw with DATETIME
| extend dt = split(DATETIME, ':')
| project DATE=format_datetime(todatetime(dt[0]), 'yyyy-MM-dd')

when I try to put the actual value 01/Sep/2022 it can convert

SampleLog_CL 
| extend raw = parse_json(RawData).log
| parse raw with DATETIME
| extend dt = split(DATETIME, ':')
| project DATE=format_datetime(todatetime("01/Sep/2022"), 'yyyy-MM-dd')

How can I convert it?

azure-log-analytics kql azure
1 Answers

Well...

// Data sample generation. Not part of the solution
let SampleLog_CL = datatable(RawData:dynamic)
[
    dynamic({"log":"01/Sep/2022:04:48:11 +0000"})
];
// Solution starts here
let months = dynamic({"Jan":01, "Feb":"02", "Mar":"03", "Apr":"04", "May":"05", "Jun":"06", "Jul":"07", "Aug":"08", "Sep":"09", "Oct":"10", "Nov":"11", "Dec":"12"});
SampleLog_CL 
| extend raw = parse_json(RawData).log
| parse raw with d "/" M "/" y ":" h ":" m ":" s " " o
| extend Timestamp = todatetime(strcat(y, "-", months[M], "-", d, " ", h, ":", m, ":", s, o))
RawData raw d M y h m s o Timestamp
{"log":"01/Sep/2022:04:48:11 +0000"} 01/Sep/2022:04:48:11 +0000 01 Sep 2022 04 48 11 +0000 2022-09-01T04:48:11Z
user_63364790
Related