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 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

  • GaryBushey's avatar
    GaryBushey
    Bronze Contributor

    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.

    • Alexander_Ceyran's avatar
      Alexander_Ceyran
      Copper Contributor

      GaryBushey 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) 

       

       

      • 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

Resources