Forum Discussion
Looping through watchlists
I'm not sure if what I'm trying to do is feasible/possible, but I thought I'd ask. I have a KQL query that returns data (which is a first) SigninLogs
| where UserPrincipalName != ''
| lookup k...
papagolf A couple of things
1) I would move the _GetWatchList('Userlist') into a let statement and then use the new table name in your join
2) Don't use the SearchKey as the field to do the comparison on. It will make it harder to remember what the actual field you are using later.
let userList = _GetWatchlist('Userlist');
SigninLogs
| where UserPrincipalName != ''
| lookup kind=inner userList on $left.UserPrincipalName == $right.SearchKey
| summarize count() by IPAddress, LocationWithout knowing more about the watchlist, it would be hard to tell what could be wrong with the code. Any reason you chose to do it this way rather than using a join?
Thanks for the suggestions,
The csv file is nothing more than a single column of email addresses.
As for why I chose this route, it’s more that I couldn’t figure out the best way to do it. I started with the external data, moved to a watchlist, read lots of blogs but nothing is quite giving me the data I’m expecting.
The goal here is to take the list of 300 users in the csv, query the IPs they’ve logged in from so that I can build a conditional access policy around those IPs.
The problem I’m having is I’m not getting back anywhere near the level of data I’m expecting.
The csv file is nothing more than a single column of email addresses.
As for why I chose this route, it’s more that I couldn’t figure out the best way to do it. I started with the external data, moved to a watchlist, read lots of blogs but nothing is quite giving me the data I’m expecting.
The goal here is to take the list of 300 users in the csv, query the IPs they’ve logged in from so that I can build a conditional access policy around those IPs.
The problem I’m having is I’m not getting back anywhere near the level of data I’m expecting.
- Try using a join instead of a lookup and see if that works better since the data coming from your Watchlist is a table.