Forum Discussion

Alexander_Ceyran's avatar
Alexander_Ceyran
Copper Contributor
Mar 22, 2020

Sign-in logs and Azure AD groups

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...
  • GaryBushey's avatar
    GaryBushey
    Mar 22, 2020

    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-sentinel/ba-p/1167546

     

    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.

Secureskydev's avatar
Secureskydev
Copper Contributor
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
Clive_Watson's avatar
Clive_Watson
Bronze Contributor
Apr 30, 2024

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

  

  • Secureskydev's avatar
    Secureskydev
    Copper Contributor
    Apr 30, 2024
    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

Resources