Sign-in logs and Azure AD groups

[Alexander_Ceyran]

Hello everyone,

I'm still new to Sentinel, my aim is to use a KQL query to retrieve some sign-in logs and filter them by displaying sign-ins for members of a specific Azure AD Group only.

When using "SigninLogs" I can't identify a field for group membership. I'm thinking about using the "identity" field to correlate users with groups but I'm still not able to find a way to that.

Do you have some similar experience to share?

Thanks for your help

Alex

[Alexander_Ceyran]

Hello everyone,

I'm still new to Sentinel, my aim is to use a KQL query to retrieve some sign-in logs and filter them by displaying sign-ins for members of a specific Azure AD Group only.

When using "SigninLogs" I can't identify a field for group membership. I'm thinking about using the "identity" field to correlate users with groups but I'm still not able to find a way to that.

Do you have some similar experience to share?

Thanks for your help

Alex

[#3 · Accepted solution]

@Alexander_Ceyran There is nothing that you can access directly in Azure Sentinel although the information is available in the Graph API.  You may be able to write a PowerApp that will copy that data into an Azure Blog and then you can use the externaldata command to read that.

This blog post also talks a bit about using the Graph API so it may be of use: https://techcommunity.microsoft.com/t5/azure-sentinel/bring-your-threat-intelligence-to-azure-sentin...

Not the best solution but it should work.  BTW, you can use the KQL command search to search all the tables for a specific  value like an AAD group to see if you can find it.

[#4]

Another useful blog post: https://techcommunity.microsoft.com/t5/azure-sentinel/ingesting-office-365-alerts-with-graph-securit... 

[#5]

@Gary Bushey Thanks for your help , I used externaldata with a csv file (The file is stored in a blob container) containing the UPN of all members of the group, just to share my solution with others:

let grouplist = externaldata (Members: string) [h"https://...file.csv"];
SigninLogs
| where UserPrincipalName !in~ (grouplist) 

[#6]

I saw a kql (below) that is accessing the graph API directly, but I get a generic error. Is there a permission or workspace setting?

SigninLogs
| where TimeGenerated > ago(30d)
| where ClientAppUsed in ("Browser", "Exchange ActiveSync", "IMAP4", "Mobile Apps and Desktop clients", "Other clients", "POP3", "SMTP")
| summarize arg_max(TimeGenerated, *) by UserPrincipalName
| project UserPrincipalName, TimeGenerated
| join kind=leftouter (
externaldata(displayName:string,lastSignInDateTime:datetime)
[@"https://graph.microsoft.com/v1.0/users?$select=displayName,signInActivity"]
with(format="json", ingestionMapping=[{"column":"displayName","path":"displayName"},{"column":"lastSignInDateTime","path":"signInActivity/lastSignInDateTime"}])
on $left.UserPrincipalName == $right.displayName
)
on UserPrincipalName
| project UserPrincipalName, TimeGenerated, lastSignInDateTime
| where lastSignInDateTime < ago(90d)
| extend AccountCustomEntity = UserPrincipalName

[#7]

@Secureskydev 

IdentityInfo was released in 2021 What's new: IdentityInfo table is now in public preview! - Microsoft Community Hub

So you can do things like this very basic example, with the UEBA data:

SigninLogs
| where TimeGenerated > ago(30d)
| where ClientAppUsed in ("Browser", "Exchange ActiveSync", "IMAP4", "Mobile Apps and Desktop clients", "Other clients", "POP3", "SMTP")
| summarize arg_max(TimeGenerated, *) by UserPrincipalName
| project UserPrincipalName, TimeGenerated
| join kind=leftouter 
(
    IdentityInfo
    | where TimeGenerated > ago(30d)
    | summarize arg_max(TimeGenerated, *) by AccountUPN
    | project GroupMembership, AccountUPN
) on $left.UserPrincipalName == $right.AccountUPN

[#8]

We tried using identityInfo, but we found it didn't always have the current state (isAccountEnabled) and, in some cases, didn't have all the "old" enabled accounts (material differences on some clients) depending on lookback. Also, it nice having additional enrichment data last signing, registration, etc.

We are using a series of Log apps to push different types of enrichment data into the workspace. It would be nice to have the option to grab it directly, like Rod Trent's blog. It seem it was working for some folks