Forum Discussion
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
- 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.- 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
- 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
