Querying value pairs in Watchlist

Question

Hello,

I have defined a Watchlist that contains user names and known user IP addresses. I'm Trying to set up a query that will alert if it records a log-in associated with User A1 but NOT IP Address A2. (i.e. if Donald Duck logs in from 192.198.0.1 then do not alert, however if DonaldDuck logs in from 192.168.0.2 then DO ALERT). Is there any way to alert on just the value to the right of the username? I have a query that seems to search for ALL Usernames against ALL Ipadresses

Any help would be great

Username,IPAddress
DonaldDuck,192.198.0.1
BugsBunny,192.198.0.2
JackTorrance,192.198.0.3

My base query:

let watchlist = (_GetWatchlist('mwl') | project Username, IPAddress);
SignInLog
| where UserDisplayName in watchlist and IPAddress !in watchlist
| project IPAddress, UserDisplayName

Answer 1

@I'mLenny I believe this achieves your goal. give it a try

let watchlist = (_GetWatchlist('mwl') | project strcat(Username, _ , IPAddress));
SignInLog
| extend user_ip = strcat(UserDisplayName, _ ,IPAddress)
| where user_ip !in watchlist
| project IPAddress, UserDisplayName

Answer 2

You could join the datasets, something like this?

let watchlist = (_GetWatchlist('mwl') | project Username, IPAddress);
watchlist
| join 
(
    SigninLogs
    // project
) on $left.UserName == $right.UserDisplayName 
| project UserName, userDisplayName, IPAddress

in the SigninLogs you may want to use a filter to just get the last result:

| summarize arg_max (TimeGenerated,*) by userDisplayName