Sentinel Kusto Query todatetime function does not work with dynamic values.

Question

I have a kusto query to calculate MTTR by client.

When an incident is resolved, an analyst comments the resolution time in the format R: time where time is when the incident was resolved and R is to make the comment unique. Example R: Friday, May 10, 2024 12:19 PM.

The query searches incidents with this comment and extract the time. Eg. Friday, May 10, 2024 12:19 PM.

The problem is, when I try to convert the extracted time to datetime, it returns an empty value. But, when I hard-input the extracted time, it works very well. Also, when I compare the hard-inputted time and the dynamic time, they're thesame (true). What could be the problem?

Result Image:

Query:

SecurityIncident
| extend comments = parse_json(Comments)
| mv-apply escalated = comments on (where tostring(escalated.message) == "N")
| mv-apply resolved = comments on (where tostring(resolved.message) matches regex "(?i)R ?: ?([a-z]+, [a-z]+ [0-9]+, [0-9]+ [0-9]+:[0-9]+ (AM|PM))")
| where isnotempty(escalated) and isnotempty(resolved)
| extend EscalatedTime = todatetime(escalated.createdTimeUtc) 
| extend _ResolvedTime = extract("(?i)R ?: ?([a-z]+, [a-z]+ [0-9]+, [0-9]+ [0-9]+:[0-9]+ (AM|PM))", 1, tostring(resolved.message))
| extend is_same_time = _ResolvedTime == "Friday, May 10, 2024 12:19 PM"
| extend ResolvedTodateTime = todatetime(_ResolvedTime)
| extend ResolvedTodateTimeHardCoded = todatetime("Friday, May 10, 2024 12:19 PM")
| project ResolvedTodateTime, ResolvedTodateTimeHardCoded, is_same_time

Answer 1

@Julius Ekane

Thank you for posting your query on Microsoft Q&A, from above description I could understand that the value _ResolvedTime in format R:time is not getting converted by todatetime function which ends up in an empty value.

Please do correct me if this is not the ask by responding in the comments section.

This issue is more related to KQL rather then Sentinel but the best shot I gave was the format.

todatetime() function and datetime() literals follows ISO 8601 and it is strongly recommended to use only the ISO 8601 formats.

My suggestion here is to use format_datetime() instead of directly using R:time with todatetime() function.

Thanks,

Akshay Kaushik

Please "Accept the answer (Yes)" and "share your feedback ". This will help us and others in the community as well.