Using Microsoft Sentinel Watchlists in a Cross Workspace query

Introduction

In my last post, I talked about how to get a single (or a few) entries from a Microsoft Sentinel watchlist. I introduced the fact that watchlists are stored in the “Watchlist” table. We can use this to perform cross workspace queries.

_GetWatchlist

Again, in my last blog post, I talked about the “_GetWatchlist” KQL function and showed all the code. Here, I will expand the discussion to show exactly what the function does. Note that I got rid of the union call and the “ConfidentialWatchlist” table since it is not (yet) used.

Watchlist
| 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)

Let’s dissect this line by line.

Watchlist

Let’s face it, if you don’t know what this does, you really need to stop reading this post and learn KQL.

| where TimeGenerated < now()

This makes sure that you get all the rows from the table. Without this, you would only get the rows that were created before the time dropdown in your query. Since the row could be created up to 12 days ago (see What is a watchlist – Microsoft Sentinel | Microsoft Learn for an explanation)

| where _DTItemType == 'watchlist-item'

This makes sure you only get the watchlist rows returned. The other value could be “watchlist” which is for the definition of the watchlist itself.

| where WatchlistAlias == watchlistAlias

This makes sure you only get the watchlist you want. The “watchlistAlias” is being passed into “_GetWatchlist” and is the name of the watchlist you want to see.

| summarize hint.shufflekey=_DTItemId arg_max(_DTTimestamp, _DTItemStatus, LastUpdatedTimeUTC, SearchKey, WatchlistItem) by _DTItemId

As with any other Microsoft Sentinel table, no data is deleted from the “Watchlist” table until the data retention expires. This means that any changes to a row in the watchlist will trigger the creation of a new row. This call will make sure you get the latest and greatest rows from the watchlist itself.

| where _DTItemStatus != 'Delete'

As I just stated, no rows are deleted from the “Watchlist” table. If you do delete a row in the watchlist, it will just be marked as “Delete” to denote that this row is deleted. I don’t know if the code that replicates the watchlist every 12 days will replicate the deleted rows or not.

| project-away _DTTimestamp, _DTItemStatus

Get rid of the two columns we don’t care about

| evaluate bag_unpack(WatchlistItem)

Finally, take all the watchlist columns that were stored in the “WatchlistItem” column and expand them out so you will see each column that is in the watchlist.

Cross Workspace Query

Now that we know the watchlist is just another Microsoft Sentinel table and how the code works, it is easy to get this in a cross workspace query.

workspace("<workspaceGUID>").watchlist

This will make a cross workspace call and will return all the watchlists. You can then use the rest of the “_GetWatchlist” code to filter out the data to get the information you need.

Note on bag_unpack

I didn’t know this originally, but you can drastically increase the speed of “bag_unpack” if you give it the list of columns that will be extracted.

From the official KQL page for “bag_unpack”, bag_unpack plugin – Azure Data Explorer & Real-Time Analytics | Microsoft Learn, there is an example that illustrates this.

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)

So, if you are writing your own code for the “bag_unpack” make sure to include the name of the columns if you can for better performance.

Summary

You can easily make a cross workspace query for watchlists if you know how they work behind the scenes.