Forum Discussion

jams_k8's avatar
jams_k8
Copper Contributor
Jan 12, 2023

KQL "not in watchlist" not working

Hi, I am trying to create a query that will display SHA256 hashes of .XLL files that are known as malicious or unknown hashes, while ignoring known good hashes. I have created 2 watchlists, "good" and "bad" containing known good SHA256 hashes and known bad and my query uses the following logic:

 

 

let good_ = _GetWatchlist("good_xll") | project  SHA256;
let bad_ = _GetWatchlist("bad_xll") | project  SHA256;
DeviceImageLoadEvents
| where FileName endswith ".xll"
| where SHA256 !in(good_) or SHA256 in(bad_)

 

 

There should only be 1 result appearing because I created my own malicious test .xll file but instead, it shows up along with 1 or 2 other .xlls with hashes that are part of the "good" watchlist.

 

I have tested various methods and the only thing that ended up working was to manually create a list of strings within the query like: where SHA256 !in("hash1""hash2""hash3", etc...)

 

Thanks in advance and any suggestions of what I may be doing wrong?

2 Replies

  • Clive_Watson's avatar
    Clive_Watson
    Bronze Contributor
    Are you have a case sensitivity issue, maybe try:

    | where FileName endswith tolower(".dll")

    or

    | where SHA256!in~ (good_)
    • jams_k8's avatar
      jams_k8
      Copper Contributor

      Clive_Watson 

      Hi, I tried adding your suggestions but it is still displaying the items included in the watchlist.

       

      I've attached a screenshot of my actual query and the watchlist I am using and as you can see, the resulting SHA256s are exact matches to that of the workbook. Suspicious file.xll is my test file that is being properly reported, but I can't seem to figure out why the SHA256s in the workbook are still being displayed

      My query

      Watchlist results

Resources