Azure Log Analytics parse String

Question

I am trying to parse a string from Azure Logs. This is from the intunedevice table, and unfortunately the CreateDate column is a string and not a date format. So I want to grab this string value and do some transformation in order to compare it with now(). But my problem is that I am unable to fetch the data using regex.

FYI: The format of the CreatedDate string value is as follows:

2021-05-17 07:33:41.0000000

I would like to only get the date (e.g., 2021-05-17) in the test result below.

I am trying the following:

IntuneDevices | where TimeGenerated > ago(1d) | parse kind=regex  CreatedDate with "(\\d\\d\\d\\d[-]\\d\\d[-]\\d\\d)*" test  | project TimeGenerated, CreatedDate, now(), test

Result:

TimeGenerated [UTC]
2021-08-30T05:08:42.8809Z
CreatedDate
2021-05-17 07:33:41.0000000
Column1 [UTC]
2021-08-30T12:40:53.296239Z
test
07:33:41.0000000

So the parse works, but it takes the values away ...

IntuneDevices | where TimeGenerated > ago(1d) | parse kind=regex CreatedDate with * '(\\d{4}-\\d{2}-\\d{2})' test | project TimeGenerated, CreatedDate, now(), test

Result:

TimeGenerated [UTC]
2021-08-30T05:08:42.8809Z
CreatedDate
2021-05-17 07:33:41.0000000
Column1 [UTC]
2021-08-30T12:40:53.296239Z
test
07:33:41.0000000

So I pushed it a bit differently, but as you can see below the Message is empty when I parse the date...

print m = '18/03/2020 07:08:23 1164 PACKET 000000C164RF56B0 UDP Rcv 10.128.151.34 076e Q [2021-05-17 07:33:41.0000000] A (10)indelpus03(6)kworld(4)kay(3)com(0)' | extend Message = extract(@'\[(\d{4}-\d{2}-\d{2})\]', 1, m)

Message:

/empty/

But when using just a text sample, like aaaaa, it works...

print m = '18/03/2020 07:08:23 1164 PACKET 000000C164RF56B0 UDP Rcv 10.128.151.34 076e Q [aaaaa] A (10)indelpus03(6)kworld(4)kay(3)com(0)' | extend Message = extract(@'\[(.*)\]', 1, m)

Message:

aaaaa

SaiSakethGuduru-MT SaiSakethGuduru-MT · Accepted Answer · 2021-08-31T07:38:53

You could try using parse operator which will help you in fetching the data in regex
Below is the example of code on how to use parse values.

let Traces = datatable(EventText:string)
[
"Event: NotifySliceRelease (resourceName=PipelineScheduler, totalSlices=27, sliceNumber=invalid_number, lockTime=02/17/2016 08:40:01, releaseTime=02/17/2016 08:40:01, previousLockTime=02/17/2016 08:39:01)",
"Event: NotifySliceRelease (resourceName=PipelineScheduler, totalSlices=27, sliceNumber=15, lockTime=02/17/2016 08:40:00, releaseTime=invalid_datetime, previousLockTime=02/17/2016 08:39:00)",
"Event: NotifySliceRelease (resourceName=PipelineScheduler, totalSlices=27, sliceNumber=20, lockTime=02/17/2016 08:40:01, releaseTime=02/17/2016 08:40:01, previousLockTime=02/17/2016 08:39:01)",
"Event: NotifySliceRelease (resourceName=PipelineScheduler, totalSlices=27, sliceNumber=22, lockTime=02/17/2016 08:41:01, releaseTime=02/17/2016 08:41:00, previousLockTime=02/17/2016 08:40:01)",
"Event: NotifySliceRelease (resourceName=PipelineScheduler, totalSlices=invalid_number, sliceNumber=16, lockTime=02/17/2016 08:41:00, releaseTime=02/17/2016 08:41:00, previousLockTime=02/17/2016 08:40:00)"
];
Traces  
| parse EventText with * "resourceName=" resourceName ", totalSlices=" totalSlices:long * "sliceNumber=" sliceNumber:long * "lockTime=" lockTime ", releaseTime=" releaseTime:date "," * "previousLockTime=" previouLockTime:date ")" *  
| project resourceName ,totalSlices , sliceNumber , lockTime , releaseTime , previousLockTime

Also check the SO1 and SO2 with the related discussions.

Azure Log Analytics parse String

2 Answers