CODEWITHSUNDEEP
kqlmicrosoft365-defender
Kornuptiko
Kornuptiko

Reputation: 17

'where' operator: Failed to resolve table or column or scalar expression named

For a Query in Microsoft Defender Advanced Hunting I want to use Data from an external Table (here the KQL_Test_Data.csv) but when I try to run it I get the Error message:

'where' operator: Failed to resolve table or column or scalar expression named 'IOC'

and when i highlight the whole Query as told in 'where' operator: failed to resolve scalar expression named 'timeOffsetMin' i get this error message:

No tabular expression statement found

This is the code i used:

let IOC = externaldata(column:string) 
[   
h@"https://raw.githubusercontent.com/Kornuptiko/TEMP/main/KQL_Test_Data.csv" 
] 
with(format="csv");

DeviceNetworkEvents 
| where Timestamp > ago(30d) 
| where RemoteIP in (IOC);

Upvotes: 1

Views: 1132

Answers (1)

David דודו Markovitz
David דודו Markovitz

Reputation: 44951

Assuming microsoft365-defender supports externaldata:

Your file is not a valid CSV, and KQL is strict about this.
As a work-around we can read the file as txt and then parse it.

let IOC = externaldata(column:string) 
[   
    h@"https://raw.githubusercontent.com/Kornuptiko/TEMP/main/KQL_Test_Data.csv" 
] 
with(format="txt")
| parse column with * '"' ip '"' *
| project ip;
DeviceNetworkEvents 
| where Timestamp > ago(30d) 
| where RemoteIP in (IOC);

Upvotes: 1

Related Questions