Jul 16 2021 10:04 AM
Jul 16 2021 10:04 AM
I am working on a rule that uses a watchlist of elevated accounts. What I am trying to create is a rule that will tell me if one of these elevated accounts has not been used in over 60 days so we can mark it for removal from the list. I am looking at the SecurityEvent table for any events these users generate. I can not seem to find a way to a "last event" type comparison. I can get a list of things they have done with starttime and endtime, but nothing I do seems to show a user that has not done any activity in 60 days or more. If anyone has done this, could you please share your rule/wisdom?
Jul 16 2021 12:34 PM
@Marc_Jacquard I would use the SignInLogs to determine if the user has signed in during your time frame in question. You could use the UserPrincipalName or UserDisplayName fields to compare against your Watchlist.
Jul 16 2021 12:39 PM
Jul 16 2021 04:23 PM - edited Jul 16 2021 04:24 PM
Something like this should work for you. My data isn't an exact match for your formatting but you should be able to make it work in your environment. I created a test WatchList with a single column of Account, then a few rows of DOMAIN\username fields that had been active, and then some fake accounts that hadn't (fake inactive admins)
let adminlist = (_GetWatchlist("TestWatchList")|project Account);
| where TimeGenerated > ago(10d)
| where Account in (adminlist)
| distinct Account
| join kind=rightanti adminlist on Account
Join operator - https://docs.microsoft.com/en-us/azure/data-explorer/kusto/query/joinoperator?pivots=azuredataexplor... rightanti should return results from the right (your watchlist) that aren't in the left (your query for active users)
Jul 17 2021 09:29 AM
@Marc_Jacquard I would recommend at least combining the two tables if you want to get the best picture of user activity. My admin account has logged into my Azure Sentinel instance quite a bit in the last week but has not performed any activity that would show up in SecurityEvent (which shows information from the Windows machines so I am not sure that is the correct table. AzureActivity may be better)
Jul 19 2021 04:12 AM
Thank you. I will give this a try and see how it goes.
Jul 19 2021 06:01 AM
If I use SignIn logs it seems to work fine. Then I look up the user the query said has been inactive in SecurityEvent table and it shows activity within the last 7 days.
Jul 19 2021 01:51 PM
Jul 19 2021 02:01 PM
Jul 19 2021 05:57 PM - edited Jul 19 2021 06:23 PM
Sure no worries, and what format are your accounts, so UserPrincipalName = email@example.com and then account is just bobsmith? And is your watchlist just a list of userprincipalnames?
Jul 20 2021 12:32 AMSolution