Forum Discussion

Ciyaresh's avatar
Ciyaresh
Brass Contributor
Mar 14, 2023

Escape character in KQL?

Hello guys, 

 

I have a problem where I'm unable to escape characters in my KQL query.  We are ingesting some custom windows event logs that are being "normalized" in XML format. However, when sentinel normalizes these events, it puts all of them nested fields. Does this for some of the Azure logs as well. However, usually I can retrieve some data from those by using the following query

 

 

AzureActivity
| where OperationName == "signin"
| expand Identity == OperationName.AdditionalFields.LoginIdentity

So basically using the expand I extract identity field that is nested within other 2 fields.  This works fine, however, the log I'm working with has numbers as field. So when I try the same thing but with

 

AzureActivity
| where OperationName == "signin"
| expand Identity == OperationName.01.LoginIdentity

 

It errors, it doesn't like that "01" part and query wont run, not that because 01 doesn't exist because it does but because its an integer? not sure. So I am trying to escape the "01" part. See below for the log example that might help explaining 

 

 

So in the image above, I want to extract fields like operation, processIdentity etc. So then I can use this fields for further filtering or use it to build a workbook. So then in my query I can say, 

 

WindowsEvent
| where EventID = "7777"
| where processIdentity = "identity"

 any help apricated 🙂 

KQL
kusto
siem
    • Ciyaresh's avatar
      Ciyaresh
      Brass Contributor
      Mar 15, 2023

      Clive_Watson thanks for the detailed reply. I tried using the method you listed but numbers are still there. It looks exactly the same even before using mv-expand and project operators. Only in this case you are only projecting the EventData field so ignores everything else.  My goal is to be able to use the fields within 01 column. So then I can create workbooks saying

      | where operation == "value"

      | summarize count() by Operation 

       

       

      • Clive_Watson's avatar
        Clive_Watson
        Bronze Contributor
        Mar 16, 2023

        Ciyaresh 

         

        The project was just an example and to simply what was returned - it wasn't intended to be a finished query.  I dont have your data and it hard to read from the screenshot.

        This is just a sample query on data I have - you should be able to parse the xml or json?

            Event
    | where isnotempty(EventData)
    | limit 10
    | project EventData, EventID
    | extend a=parse_xml(EventData)  // or  | extend a=parse_json(EventData)

         
        If you get results, you should be able to right click and "extend column" the data, again just to test its possible. 

         

Resources