KQL Search

Query Details

HomeAI ToolsDevice Query

Multiple All Watchlist Items

Query

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

Explanation

This query combines two tables, Watchlist and ConfidentialWatchlist, and retrieves all the rows where the TimeGenerated is before the current time. It then filters the rows to only include those with _DTItemType equal to 'watchlist-item'. Next, it groups the rows by _DTItemId and selects the row with the maximum value for _DTTimestamp, _DTItemStatus, LastUpdatedTimeUTC, SearchKey, WatchlistItem, and WatchlistAlias. It removes any rows with _DTItemStatus equal to 'Delete' and sorts the remaining rows by WatchlistAlias and LastUpdatedTimeUTC in ascending order. It then removes certain columns (_DTTimestamp, _DTItemStatus, _DTItemId, LastUpdatedTimeUTC) and expands the WatchlistItem column. Finally, it reorders the columns to display WatchlistAlias first and SearchKey second.

Details

Jose Sebastián Canós profile picture

Jose Sebastián Canós

Released: March 1, 2023

Tables

WatchlistConfidentialWatchlist

Keywords

Devices,Intune,User

Operators

unionisfuzzytruewhere<now=='watchlist-item'summarizehint.shufflekey_DTItemIdarg_max_DTTimestamp_DTItemStatusLastUpdatedTimeUTCSearchKeyWatchlistItemWatchlistAlias!='Delete'sort byascproject-away_DTTimestamp_DTItemStatus_DTItemIdLastUpdatedTimeUTCevaluatebag_unpackproject-reorder.

Actions