Forum Discussion

misstaek's avatar
misstaek
Copper Contributor
Jan 27, 2022

KQL: setting query time leads to problem in watchlist column projecting

Hello to the community!

 

I have stumbled upon a very strange issue when using watchlists.

 

I have a watchlist with 2 columns (userPrincipalName,allowedActivity) that I am then using to whitelist activities.

Watchlist is imported using: 

let WhitelistedUsers = _GetWatchlist("testQuery") | project userPrincipalName, allowedActivity;

 

Then I wanted to set it to a specific time frame to test it on given data set:

set query_now = datetime("1/14/2022, 1:45:46.556 PM");

 

Problem is that when setting my query for a specific time, I get the following error from the watchlist:

'project' operator: Failed to resolve scalar expression named 'userPrincipalName'. Commenting the set query_now solves the project problem (not my problem though).

 

I tried to set the time before and after watchlist import but that does not solve the issue. I could not find any posts around the topic (quite a specific one), so anyone observed similar behaviors or has a possible explanation? I can probably work around the set query_now with other functions but I gotten used to it, and find this behavior extremely strange

BUG
KQL
sentinel
watchlist

5 Replies

  • Ankit's avatar
    Ankit
    Brass Contributor
    Aug 07, 2025

    Hola !

    The problem isn’t your watchlist — it’s the set command.

    When you use set querynow = before loading your watchlist, KQL’s “brain” (parser) gets confused. The GetWatchlist() function is actually a little macro behind the scenes, and with set in front of it, KQL messes up and forgets that your watchlist has a column called userPrincipalName. That’s why you get the “Failed to resolve” error.

    If you remove set or move it after your watchlist load, it works fine. Even better, use let instead of set for your date variable

    Think of set as “global, sometimes messy” and let as “local, safe.”
    In short — switch set to let, or move it down in your query, and the error will go away.

    gracias !! 

  • Rixonp's avatar
    Rixonp
    Copper Contributor
    Nov 06, 2023
    I was wondering if you've found a way to get around this. It is making backtesting analytic rules with watchlists impossible.
    Using the Results Simulation graph shows gives me a query with these set statements that end up not working when I try and run them.

    Also let does not seem to work the same way set does with regards to these tests.
    • Clive_Watson's avatar
      Clive_Watson
      Bronze Contributor
      Nov 07, 2023
      The result simulation is doing the evaluations for you, the default message says with "current data".
      I suspect like the Rule Query window there is extra filtering applied, its probably doing a query_time so you cant do one as well (e.g Rule query window excludes 14+ day lookback and union * etc...)
      Only someone from the Sentinel team can say for sure
  • Clive_Watson's avatar
    Clive_Watson
    Bronze Contributor
    Jan 27, 2022
    You should use Let rather than Set (Set is a Azure Data Explorer statement)

    So in Sentinel Logs it would be (unless you are using ADX?):

    let query_now = datetime("1/14/2022, 1:45:46.556 PM");
    print query_now
    • rstanile's avatar
      rstanile
      Copper Contributor
      Aug 01, 2025

      I still can confirm the issue, and it is present when we want to go back in time for a few days. 

       

      This code gives me full watchlist: 

      _GetWatchlist("Svc_Accts_without_MFA")

      This code gives me only three records out of 75: 

      set query_datetimescope_column = "TimeGenerated";
set query_datetimescope_from = datetime(2025-07-03T23:00:00.000Z);
set query_datetimescope_to = datetime(2025-07-05T23:00:00.000Z);
set query_now=datetime(2025-07-05T23:00:00.000Z);
_GetWatchlist("Svc_Accts_without_MFA")

      All records in the watchlist were updated months ago. 

      The "set" commands should impact only DATA queries, using tables, not watchlists. 

Resources