Feb 18 2022 08:56 AM
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!
Feb 18 2022 12:35 PM
@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?
Feb 18 2022 01:43 PM
Feb 22 2022 03:38 AM