Pull information from a Watchlist and add it to another table

[gsingh_]

Hi,

I am using a Watchlist having all the usernames and their device names. I would like to get device names and add them to the CommonSecurityLog/CEF table. How can I do it using join or lookup? Please share some thoughts or a query sample.

Note: The CEF logs do have usernames that we can match. 

Thanks

[#2 · Accepted solution]

@gsingh_  You will not be able to add the data to the other tables as there is no feature within KQL to add data to a table, only look at data.

If you want to do a join to look at data, you can do something like:

let watchListTable = _GetWatchlist('Watchlist');
CommonSecurityLog
| join watchListTable on $left.key == $right.key

[#3]

thanks, Gary. I agree the watchlist is not going to solve the purpose. The actual requirement is to get the endpoints hostname/device name added to each of the user accounts in the CEF table.
I enabled DeviceInfo events (Under Microsoft 365 Defender Connector) as it contains Device Name as well as user accounts. Tried joining both tables on Account but got excessive events.

Something similar to the below query where I am monitoring Blocked Events with a Risk Score > 50.

let CEF = CommonSecurityLog
| where TimeGenerated >= ago(1d)
| where Action == "Blocked"
| where File_Type != "None"
| where Risk_Score>=50
| summarize Count = count() by File_Type, TimeGenerated, SourceConnector, SourceUserPrivileges, UserDept, Target_URL, File_Accessed, File_Type, Response,Action, SourceIP, DestinationIP, Attack_Type, Attack_Name, Account
| order by Count desc;
let HostInfo = DeviceInfo
| extend DeviceName, Account;
CEF
| join kind=leftouter HostInfo on Account | project-away Account1
| project Count, DeviceName, Account, TimeGenerated, SourceConnector, SourceUserPrivileges, UserDept, Target_URL, File_Accessed, File_Type, Response, Action, SourceIP, DestinationIP, Attack_Type, Attack_Name