Microsoft Entra Suite Tech Accelerator
Aug 14 2024, 07:00 AM - 09:30 AM (PDT)
Microsoft Tech Community

KQL | where User !in (AuditSearch)

Copper Contributor



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) 





3 Replies



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




Thanks will take a look.


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)