SOLVED

Sign-in logs and Azure AD groups

%3CLINGO-SUB%20id%3D%22lingo-sub-1244996%22%20slang%3D%22en-US%22%3ESign-in%20logs%20and%20Azure%20AD%20groups%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-1244996%22%20slang%3D%22en-US%22%3E%3CDIV%20class%3D%22lia-message-body%20lia-component-message-view-widget-body%20lia-component-body-signature-highlight-escalation%20lia-component-message-view-widget-body-signature-highlight-escalation%22%3E%3CDIV%20class%3D%22lia-message-body-content%22%3E%3CP%3EHello%20everyone%2C%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EI'm%20still%20new%20to%20Sentinel%2C%20my%20aim%20is%20to%20use%20a%20KQL%20query%20to%20retrieve%20some%20sign-in%20logs%20and%20filter%20them%20by%20displaying%20sign-ins%20for%20members%20of%20a%20specific%20Azure%20AD%20Group%20only.%3C%2FP%3E%3CP%3EWhen%20using%20%22SigninLogs%22%20I%20can't%20identify%20a%20field%20for%20group%20membership.%20I'm%20thinking%20about%20using%20the%20%22identity%22%20field%20to%20correlate%20users%20with%20groups%20but%20I'm%20still%20not%20able%20to%20find%20a%20way%20to%20that.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EDo%20you%20have%20some%20similar%20experience%20to%20share%3F%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EThanks%20for%20your%20help%3C%2FP%3E%3CP%3EAlex%3C%2FP%3E%3C%2FDIV%3E%3C%2FDIV%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-1245125%22%20slang%3D%22en-US%22%3ERe%3A%20Sign-in%20logs%20and%20Azure%20AD%20groups%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-1245125%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F588497%22%20target%3D%22_blank%22%3E%40Alexander_Ceyran%3C%2FA%3E%26nbsp%3BThere%20is%20nothing%20that%20you%20can%20access%20directly%20in%20Azure%20Sentinel%20although%20the%20information%20is%20available%20in%20the%20Graph%20API.%26nbsp%3B%20You%20may%20be%20able%20to%20write%20a%20PowerApp%20that%20will%20copy%20that%20data%20into%20an%20Azure%20Blog%20and%20then%20you%20can%20use%20the%20externaldata%20command%20to%20read%20that.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EThis%20blog%20post%20also%20talks%20a%20bit%20about%20using%20the%20Graph%20API%20so%20it%20may%20be%20of%20use%3A%26nbsp%3B%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fazure-sentinel%2Fbring-your-threat-intelligence-to-azure-sentinel%2Fba-p%2F1167546%22%20target%3D%22_blank%22%3Ehttps%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fazure-sentinel%2Fbring-your-threat-intelligence-to-azure-sentinel%2Fba-p%2F1167546%3C%2FA%3E%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3ENot%20the%20best%20solution%20but%20it%20should%20work.%26nbsp%3B%20BTW%2C%20you%20can%20use%20the%20KQL%20command%20%3CSTRONG%3Esearch%26nbsp%3B%3C%2FSTRONG%3Eto%20search%20all%20the%20tables%20for%20a%20specific%26nbsp%3B%20value%20like%20an%20AAD%20group%20to%20see%20if%20you%20can%20find%20it.%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-1245253%22%20slang%3D%22en-US%22%3ERe%3A%20Sign-in%20logs%20and%20Azure%20AD%20groups%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-1245253%22%20slang%3D%22en-US%22%3E%3CP%3EAnother%20useful%20blog%20post%3A%26nbsp%3B%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fazure-sentinel%2Fingesting-office-365-alerts-with-graph-security-api%2Fba-p%2F984888%22%20target%3D%22_blank%22%3Ehttps%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fazure-sentinel%2Fingesting-office-365-alerts-with-graph-security-api%2Fba-p%2F984888%3C%2FA%3E%26nbsp%3B%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-1248167%22%20slang%3D%22en-US%22%3ERe%3A%20Sign-in%20logs%20and%20Azure%20AD%20groups%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-1248167%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F46875%22%20target%3D%22_blank%22%3E%40Gary%20Bushey%3C%2FA%3E%26nbsp%3BThanks%20for%20your%20help%20%2C%20I%20used%20externaldata%20with%20a%20csv%20file%20(The%20file%20is%20stored%20in%20a%20blob%20container)%20containing%20the%20UPN%20of%20all%20members%20of%20the%20group%2C%20just%20to%20share%20my%20solution%20with%20others%3A%3C%2FP%3E%3CP%3E%3CBR%20%2F%3Elet%20grouplist%20%3D%20externaldata%20(Members%3A%20string)%20%5Bh%22https%3A%2F%2F...file.csv%22%5D%3B%3CBR%20%2F%3ESigninLogs%3CBR%20%2F%3E%7C%20where%20UserPrincipalName%20!in~%20(grouplist)%26nbsp%3B%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3C%2FLINGO-BODY%3E
Occasional Contributor

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 Replies
best response confirmed by rodtrent (Microsoft)
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.

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