special thanks to @Ofer_Shezaf for showing me the new function call.
Watchlists
Watchlists are a feature of Microsoft Sentinel that provide great flexibility and useability. They allow for user-defined tables that can be used in KQL queries to provide additional data. By uploading data using CSV files, users control the data that are in the watchlists and that data can be modified and new rows added as needed.
Watchlists provide a "searchkey" field that is unique across the watchlist and can be used to reference an individual row as discussed below.
Querying Watchlists
The Microsoft Sentinel portal UI provides a way to query the watchlist in the logs watchlist. You can select the watchlist and then click on the "View in Logs" button. This will then transfer the user to Logs, or Advanced Hunting if using Unified SOC Platform, and will execute the following command:
_GetWatchlist('<Watchlist Name>')
This will load the entire watchlist and return all the rows. While this works fine for most of the applications, if your watchlist is very large, it could cause a timeout issue depending on the rest of the KQL query.
For those cases, use the "_ASIM_GetWatchlistRaw" function to return specific rows. It takes two parameters: The Workbook name and a dynamic list of the values for the "searchkey" column that will be used to determine which rows to return.
The example below demonstrates how to use this function:
let Users = dynamic(["User1@contoso.com", "user1@contoso.com"]);
_ASIM_GetWatchlistRaw('VIP',Users)
| evaluate bag_unpack(WatchlistItem)
Note the call to "evaluate bag_unpack(WatchlistItem)" at the end. This is needed to expand the raw information that is stored in the watch list rows into the same format you get using "_GetWatchlist"
Additional Comments on bag_unpack
(once again, thanks @Ofer_Shezaf )
"bag_unpack" is very expensive in terms of performance, and when using it you reduce – sometimes significantly - the value of using the new function, "_ASIM_GetWatchlistRaw".
To improve you can use either:
- bag_unpack with the output schema feature, which instructs the function on the expected fields and makes it performant. This involves telling "bag_unpack" what fields are contained in the column that is being unpacked as in the example below taken from the KQL documentation:
datatable(d:dynamic)
[
dynamic({"Name": "John", "Age":20}),
dynamic({"Name": "Dave", "Age":40}),
dynamic({"Name": "Jasmine", "Age":30}),
]
| evaluate bag_unpack(d) : (Name:string, Age:long)
- or just get the values of the dynamic (i.e. WatchlistItems.yourvalue).
The importance of the new Watchlist function is that it returns raw results and lets you use the values yourself, as you know the fields stored in the list, rather than using bag_unpack. The filter is somewhat of a bonus. The advantage is, that if you avoid bag_unpack, you can use more complex filters than just key values. Even using “has” on the WatchlistItems field will be a performance boost.
