azurekqlazure-log-analytics

KQL Query to split Windows Event Logs


Currently I have logging into Log Analytics, for AppLocker.

The Event Data is pulling the information through in the following way:

enter image description here

Which is pulling the events for the endpoint as seen below:

enter image description here

I'd like to split\parse this so that it's more readable into their own columns but my KQL knowledge isn't really up to much.

Ideally I'd like to pull out the fqbn for the Publisher and the FileHash into separate columns.

Is someone can get me started with the query it'd be really helpful.

Thanks,

A


Solution

  • Try using the parse operator; You can declare a column name that you want to parse (EventData) and then, in string format, declare your delimiter before your new field name that you want to parse into its own column followed by the ending delimiter. So;

    | parse EventData with * "<fqbn>" FQBN "</fqbn>" *
    

    And if you want to parse another field into a column:

    | parse EventData with * "<FileHash>" FileHash "</FileHash>" * "<fqbn>" FQBN "</fqbn>" *
    

    note: since 'FileHash' occurs before 'fqbn', then it would have to be parsed first.