Forum Discussion

CalumS's avatar
CalumS
Icon for Bronze III rankBronze III
2 months ago

Kusto Query Language: Ep.9 – Parsing Complex Data Types.

Hi all, 

I am stuck on Question 6 as part of the KQL Parsing Complex Data Types.

I have been doing adaptations of the following query to only get a blank AvgTime table each time.  

Event_CL
| where EventData contains "KB2267602"
| extend ParsedData = parse_json(EventData)
| summarize AvgTime = avg(todatetime(ParsedData["@time"]))

I may be missing something obvious or not, but any help would be thankful. 

cloud security
help & support

4 Replies

  • AshleyKingscote's avatar
    AshleyKingscote
    Icon for Immerser rankImmerser
    2 months ago

    Hey, you're pretty close.

    Try using `parse_xml` on the EventData, as it's in XML format and not JSON.
    Then you can summarize the attribute directly like: `summarize avg(todatetime(EventData.DataItem["@time"]))`

    • CalumS's avatar
      CalumS
      Icon for Bronze III rankBronze III
      2 months ago

      Hey, thank for the help, got me a bit of the way there. 

      Ended up working with the following:
      Event_CL
      | extend ParsedXml = parse_xml(EventData)
      | where tostring(ParsedXml) contains "KB2267602"
      | summarize avg_time = avg(todatetime(ParsedXml.DataItem["@time"]))

      But got a value and put it in to be incorrect, did attempt a few variations of it incase it wanted in a certain format.