Forum Discussion

finchl1973's avatar
finchl1973
Copper Contributor
Nov 15, 2023

KQL | where User !in (AuditSearch)

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

KQL
kusto
  • Clive_Watson's avatar
    Clive_Watson
    Bronze Contributor
    Nov 15, 2023

    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

     

      • finchl1973's avatar
        finchl1973
        Copper Contributor
        Nov 20, 2023
        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)

Resources