Forum Discussion
Sign-in logs and Azure AD groups
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 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.
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)- 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
