KQL query issues with dynamic and datetime

Bogdan Dragu 46 Reputation points Microsoft Employee

I’m having some issues with KQL queries where I want to retrieve the value of some custom dimension that I’ve logged in custom events. That custom dimension contains a datetime value, and I want to write a query which does something with that datetime (for instance, get the max datetime), so I want to retrieve it as a datetime. However , when retrieving a custom dimension using KQL, the value of the custom dimension is returned as a dynamic data-type, so I want to convert it to a datetime, and that’s where things become interesting. Consider this dataset (which exists of only one row atm)

As you can see, I have a customDimension with the name ‘LastTelemetryReceivedTimestamp’ which contains a datetime. However, when I want to get the customevent with the max LastTelemetryReceivedTimestamp per vesselId, like this:
I get this error: 79517-image.png
We receive this error because the customDimension value is retrieved as dynamic. Fine you might think, just convert it explicitly to datetime like this:
But as you can see, now I receive ‘null’. The todatetime function outputs null if it was not able to convert the given value to a datetime.
However, if I do this:
This just works. Does anyone has a good idea about this, or knows how this can be solved ? How can I successfully convert a dynamic value to a datetime value ?

Azure Analysis Services
Azure Analysis Services
An Azure service that provides an enterprise-grade analytics engine.
392 questions
{count} votes

Accepted answer
  1. HimanshuSinha-msft 19,231 Reputation points Microsoft Employee

    Hello@Bogdan Dragu asked · 13 hours ago,
    Thanks for the ask and using the Microsoft Q&A platform .

    Have you tried this ? At this time I am not having the datest to try this on myside .


    Please do let me know how it goes .
    Please do consider to click on "Accept Answer" and "Up-vote" on the post that helps you, as it can be beneficial to other community members

    0 comments No comments

0 additional answers

Sort by: Most helpful