How to get a single row from a Microsoft Sentinel watchlist quickly

Introduction

19 Jan 2024 UPDATE: I have posted this same information (not quite as detailed) in the Microsoft Sentinel blog at Querying Watchlists – Microsoft Community Hub however, it does have a section on “bag_unpack” and the best way to use it.

As I am sure you already know, you can get the entries from a watchlist using the “_GetWatchlist()” function. That returns all the entries in the watchlist, but what if you just want one entry?

You can always filter after you load the watchlist, but there is a better way. This post will show you how.

Note: For all the queries I am showing, I will be using the SolutionData watchlist, which I created using code that I posted in Create a CSV containing Microsoft Sentinel Solution information – Yet Another Security Blog (garybushey.com)

Second note: It appears that the add-in I use for code has been updated and doesn’t want to show all the line numbers.

_GetWatchlist

This has always been the way to get the watchlist entries. As I said before, this will return the entire watchlist. If you want just one row, you can always do something like the following:

_GetWatchlist('SolutionData')
| where SearchKey == 1006

This will work, but what if you have a lot of rows? You can upload a 3.8MB file or have an even larger file if you store it in Blob storage. That can be a lot of rows being returned.

Here is something I just learned which, in hindsight, I should have known. “_GetWatchlist” is a function. That means you can look, and modify, the code. If you look at the code, shown below, it has to not only load all the rows from the “Watchlist” table (we will ignore the “ConfidentialWatchlist” table for now), but then it has to run a “bag_unpack” on EACH row. This can take a long time and could even cause a timeout in certain cases.

union Watchlist, ConfidentialWatchlist
| where TimeGenerated < now()
| where _DTItemType == 'watchlist-item'
| where WatchlistAlias == watchlistAlias
| summarize hint.shufflekey=_DTItemId arg_max(_DTTimestamp, _DTItemStatus, LastUpdatedTimeUTC, SearchKey, WatchlistItem) by _DTItemId
| where _DTItemStatus != 'Delete'
| project-away _DTTimestamp, _DTItemStatus
| evaluate bag_unpack(WatchlistItem)

So, what can we do about this? Well, if you want just one or a few rows, like shown above, there is a better function to call. You can call the “_ASIM_GetWatchlistRaw()” function. It takes 2 parameters, the first is the name of the watchlist and the second can either be empty, or an array of the SearchKey values you want to match. Note that you have to pass in an array, even if there is only 1 value you want to search. Also, keep in mind that it does not do the unpacking for you, so you will need to do it yourself.

With this in mind, the search code shown above could be redone as:

let SearchIndex = dynamic("[1]");
_ASIM_GetWatchlistRaw('SolutionData',SearchIndex)
| evaluate bag_unpack(WatchlistItem)

The biggest advantage to this is that not only can you easily search for just those values that you want to find, the data is filtered BEFORE you do the “bag_unpack” thereby saving a lot of time. As this is a function as well, you could always change it to do the “bag_unpack” for you if you wish. I would change the name of the function so it isn’t called “Raw” anymore if you do.

The code for the “_ASIM_GetWatchlistRaw” is shown below:

let function = (watchlist: string, keys: dynamic = dynamic([])) {
    Watchlist
    | where TimeGenerated < now()
    | where _DTItemType == 'watchlist-item'
    | where WatchlistAlias == watchlist
    | where array_length(keys) == 0 or SearchKey in (keys)
    | summarize hint.shufflekey=_DTItemId arg_max(_DTTimestamp, _DTItemStatus, LastUpdatedTimeUTC, SearchKey, WatchlistItem) by _DTItemId
    | where _DTItemStatus != 'Delete'
    | project-away _DTTimestamp, _DTItemStatus
};
function (watchlist, keys)

Watchlist table

If you have looked at the code, you will have noticed a table called “Watchlist”. Yes, all the information about the watchlist is stored in the “Watchlist” table. The “ConfidentialWatchlist” table that is in part of the “_GetWatchlist()” function may be for future enhancements.

There will be one row for the table definition itself. One way to see this is the “WatchlistName” column is not empty and the “WatchlistItemId” field is a GUID comprised of all zeros. The other rows will correspond to an entry in the watchlist itself. The “WatchlistItem” will contain all the information that makes up the watchlist’s columns. This is the column that you need to unpack to get all the watchlist’s data.

Since this is just like any other Microsoft Sentinel tables, none of the information in the table will be deleted (at least not during the retention period). Therefore, every change will create a new row.

In the image below, I performed the following tasks reading from top to bottom:

1. Created the list using a CSV file that had two entries in it. The name of the watchlist is “gabtest” and I used the “IP Subnet” field as the search key. The alias of the watchlist is also “gabtest”, but you cannot tell that from the fields I am showing. This will create 1 row for the table definition and then a single row for each row in the CSV file (other than the first row of the CSV file which is used to determine the watchlist’s column names)
2. Updated the entry with the search key “192.168.1.1” (4th row down)
3. Added a new entry with the search key “192.168.1.3” (5th row)
4. Deleted this newly created entry. (6th row)

So, while the GUI doesn’t have any sort of history feature, the history is still there. Unfortunately, you cannot access this information using the REST APIs either.

Summary

This blog post introduced the new function, “_ASIM_GetWatchlistRaw()” which allows you to return only the rows you want when searching a watchlist. It also discussed the “Watchlist” table.

Special thanks to Ofer Shezaf for creating the new function and answering my questions.