Forum Discussion

CalumS's avatar
CalumS
Icon for Bronze III rankBronze III
4 months ago
Solved

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 | whe...
cloud security
help & support
  • AshleyKingscote's avatar
    AshleyKingscote
    3 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"]))`

AshleyKingscote's avatar
AshleyKingscote
Icon for Immerser rankImmerser
3 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
    3 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. 

     

    • SamDickison's avatar
      SamDickison
      Icon for Community Manager rankCommunity Manager
      3 months ago

      Hi CalumS​ did you manage to completed the lab?

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

        Hi SamDickison​ Not managed to yet with the help above or had time to get back and try again. Will do soon hopefully and get back to you.