Looping through watchlists

%3CLINGO-SUB%20id%3D%22lingo-sub-3185100%22%20slang%3D%22en-US%22%3ELooping%20through%20watchlists%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-3185100%22%20slang%3D%22en-US%22%3E%3CP%3EI'm%20not%20sure%20if%20what%20I'm%20trying%20to%20do%20is%20feasible%2Fpossible%2C%20but%20I%20thought%20I'd%20ask.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EI%20have%20a%20KQL%20query%20that%20returns%20data%20(which%20is%20a%20first)%26nbsp%3B%3C%2FP%3E%3CPRE%20class%3D%22lia-code-sample%20language-sql%22%3E%3CCODE%3ESigninLogs%0A%7C%20where%20UserPrincipalName%20!%3D%20''%0A%7C%20lookup%20kind%3Dinner%20%20_GetWatchlist('Userlist')%20on%20%24left.UserPrincipalName%20%3D%3D%20%24right.SearchKey%20%0A%7C%20summarize%20count()%20by%20IPAddress%2C%20Location%3C%2FCODE%3E%3C%2FPRE%3E%3CP%3EThis%20returns%20a%20number%20of%20IP%20addresses%20for%20the%20time%20range%20but%20it%20does%20not%20seem%20to%20return%20enough%20data.%3C%2FP%3E%3CP%3EI%20have%20a%20group%20of%20300%20users%20in%20the%20watchlist%20that%20I%20need%20to%20pull%20their%20IP%20address%20sign%20in%20details%20for.%20I%20don't%20need%20to%20know%20which%20user%20has%20signed%20in%20from%20where%2C%20I%20just%20need%20to%20know%20the%20addresses%20that%20this%20group%20of%20users%20connect%20from.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EThis%20has%20been%20cobbled%20together%20from%20multiple%20attempts%20to%20return%20some%20data%2C%20so%20any%20pointers%2Fguidance%20on%20how%20I%20can%20get%20this%20to%20do%20what%20I%20need%20would%20really%20help!%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-3186067%22%20slang%3D%22en-US%22%3ERe%3A%20Looping%20through%20watchlists%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-3186067%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F1311606%22%20target%3D%22_blank%22%3E%40papagolf%3C%2FA%3E%26nbsp%3BA%20couple%20of%20things%3C%2FP%3E%3CP%3E1)%20I%20would%20move%20the%20_GetWatchList('Userlist')%20into%20a%20let%20statement%20and%20then%20use%20the%20new%20table%20name%20in%20your%20join%3C%2FP%3E%3CP%3E2)%20Don't%20use%20the%20SearchKey%20as%20the%20field%20to%20do%20the%20comparison%20on.%26nbsp%3B%20It%20will%20make%20it%20harder%20to%20remember%20what%20the%20actual%20field%20you%20are%20using%20later.%26nbsp%3B%3C%2FP%3E%3CPRE%20class%3D%22lia-code-sample%20language-applescript%22%3E%3CCODE%3Elet%20userList%20%3D%20_GetWatchlist('Userlist')%3B%0ASigninLogs%0A%7C%20where%20UserPrincipalName%20!%3D%20''%0A%7C%20lookup%20kind%3Dinner%20userList%20on%20%24left.UserPrincipalName%20%3D%3D%20%24right.SearchKey%20%0A%7C%20summarize%20count()%20by%20IPAddress%2C%20Location%3C%2FCODE%3E%3C%2FPRE%3E%3CP%3E%26nbsp%3BWithout%20knowing%20more%20about%20the%20watchlist%2C%20it%20would%20be%20hard%20to%20tell%20what%20could%20be%20wrong%20with%20the%20code.%26nbsp%3B%20Any%20reason%20you%20chose%20to%20do%20it%20this%20way%20rather%20than%20using%20a%20join%3F%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-3186336%22%20slang%3D%22en-US%22%3ERe%3A%20Looping%20through%20watchlists%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-3186336%22%20slang%3D%22en-US%22%3EThanks%20for%20the%20suggestions%2C%3CBR%20%2F%3EThe%20csv%20file%20is%20nothing%20more%20than%20a%20single%20column%20of%20email%20addresses.%3CBR%20%2F%3E%3CBR%20%2F%3EAs%20for%20why%20I%20chose%20this%20route%2C%20it%E2%80%99s%20more%20that%20I%20couldn%E2%80%99t%20figure%20out%20the%20best%20way%20to%20do%20it.%20I%20started%20with%20the%20external%20data%2C%20moved%20to%20a%20watchlist%2C%20read%20lots%20of%20blogs%20but%20nothing%20is%20quite%20giving%20me%20the%20data%20I%E2%80%99m%20expecting.%3CBR%20%2F%3E%3CBR%20%2F%3EThe%20goal%20here%20is%20to%20take%20the%20list%20of%20300%20users%20in%20the%20csv%2C%20query%20the%20IPs%20they%E2%80%99ve%20logged%20in%20from%20so%20that%20I%20can%20build%20a%20conditional%20access%20policy%20around%20those%20IPs.%3CBR%20%2F%3E%3CBR%20%2F%3EThe%20problem%20I%E2%80%99m%20having%20is%20I%E2%80%99m%20not%20getting%20back%20anywhere%20near%20the%20level%20of%20data%20I%E2%80%99m%20expecting.%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-3198365%22%20slang%3D%22en-US%22%3ERe%3A%20Looping%20through%20watchlists%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-3198365%22%20slang%3D%22en-US%22%3ETry%20using%20a%20join%20instead%20of%20a%20lookup%20and%20see%20if%20that%20works%20better%20since%20the%20data%20coming%20from%20your%20Watchlist%20is%20a%20table.%3C%2FLINGO-BODY%3E
New Contributor

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 kind=inner  _GetWatchlist('Userlist') on $left.UserPrincipalName == $right.SearchKey 
| summarize count() by IPAddress, Location

This returns a number of IP addresses for the time range but it does not seem to return enough data.

I have a group of 300 users in the watchlist that I need to pull their IP address sign in details for. I don't need to know which user has signed in from where, I just need to know the addresses that this group of users connect from.

 

This has been cobbled together from multiple attempts to return some data, so any pointers/guidance on how I can get this to do what I need would really help!

 

 

3 Replies

@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, Location

 Without 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.
Try using a join instead of a lookup and see if that works better since the data coming from your Watchlist is a table.