Microsoft Secure Tech Accelerator
Apr 03 2024, 07:00 AM - 11:00 AM (PDT)
Microsoft Tech Community

KQL | where User !in (AuditSearch)

Copper Contributor

Hi,

 

I'm searching through AuditLogs to check for a previous event and using the let statement to assign to a temporary table called AuditSearch.

 

Another search of the AuditLog is being done with following where statement to see if a previous entry exists.  This works Ok if a record is added to the temporary table, however if no records are there and is empty the where statement doesn't work.

 

Q what is what the best way to either

- check for the temporary table has no records

or add a dummy record to the table.  as long as something exists it works doesnt need to match.

 

| where | where User !in (AuditSearch) 

 

thanks

 

Lee

3 Replies

@finchl1973 

 

Perhaps create a fake table and use Union isfuzzy=true to handle the error?  

let AuditSearch = materialize ( AuditLogs 
                | distinct OperationName);
let fake_   = datatable (name:string)['fake value'];
union isfuzzy=true AuditSearch, fake_
//| extend OperationName = "This is not in the original" /// supply a made up value 
| where OperationName !in (AuditSearch)
| distinct OperationName

 

@Clive_Watson 

 

Thanks will take a look.

Hi,

Decided to use a table join with rightanti which shows the results whereby second search doesn't appear in first search and also works if first search doesn't find any results (which the !in didnt work for that scenario)