KQL query missing column

Question

Hello,

We have Microsoft Defender EDR/XDR in our environment. I have created many KQL query to search for information of certain devices and vulnerability and I often run into issue is that some of column (or scalar expression) is/are not present. This is the error that I got (example on one of my KQL query that I used to query for devices antivirus scan results).

This is my KQL query

DeviceEvents

| where TimeGenerated >= ago(7d)

|where DeviceName == "Win11-test"

| project DeviceName,

TimeGenerated,

ActionType,

InitiatingProcessFileName,

ScanResult

| order by TimeGenerated desc

"The name 'ScanResult' does not refer to any known column, table, variable or function".

I ran into this issue often every time I create KQL query. There always seems like some of the "column, table, variable or function" is/are missing.

How do I fix this issue?

Thanks in advance for your help.

Answer 1

The error you are encountering, "The name 'ScanResult' does not refer to any known column, table, variable or function," indicates that the ScanResult column does not exist in the DeviceEvents table within the context of your query. To resolve this issue, you should first verify the schema of the DeviceEvents table to ensure that the ScanResult column is indeed available.

Here are some steps you can take to troubleshoot and fix the issue:

1. Check the Schema: Use a simple query to retrieve the schema of the DeviceEvents table. You can run a query like this:

      DeviceEvents
      | getschema
   

   This will show you all the available columns in the DeviceEvents table.
2. Modify Your Query: If ScanResult is not part of the schema, you will need to remove it from your project statement or replace it with an existing column that provides the information you need.
3. Consult Documentation: If you are unsure about the available columns or their meanings, refer to the official Microsoft documentation for Microsoft Defender or the Kusto Query Language (KQL) reference guides.
4. Use Alternative Tables: If the information you are looking for is not in DeviceEvents, consider checking other related tables that might contain the desired data, such as DeviceFileEvents or AlertEvidence.

By following these steps, you should be able to identify the missing column and adjust your query accordingly.