Home

Intro to querying Azure AD sign-in and audit logs held in Azure Monitor from PowerShell

%3CLINGO-SUB%20id%3D%22lingo-sub-798199%22%20slang%3D%22en-US%22%3EIntro%20to%20querying%20Azure%20AD%20sign-in%20and%20audit%20logs%20held%20in%20Azure%20Monitor%20from%20PowerShell%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-798199%22%20slang%3D%22en-US%22%3E%3CP%3ESome%20questions%20I'm%20asked%20frequently%20about%20Azure%20AD%20-%20how%20can%20I%20see%20and%20retain%20more%20than%2030%20days%20of%20audit%20events%20from%20Azure%20AD%20features%3F%20And%20how%20can%20I%20get%20that%20audit%20history%20programmatically%2C%20without%20needing%20to%20sign%20in%20as%20a%20highly-privileged%20Azure%20AD%20administrator%2C%20in%20order%20to%20download%20records%20for%20a%20report%20to%20or%20answer%20an%20auditor%E2%80%99s%20inquiry%3F%3C%2FP%3E%0A%3CP%20style%3D%22font-style%3A%20inherit%3B%20font-weight%3A%20inherit%3B%22%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%3ELast%20year%20we%20%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2FAzure-Active-Directory-Identity%2FAzure-Active-Directory-Activity-logs-in-Azure-Log-Analytics-now%2Fba-p%2F274843%22%20target%3D%22_blank%22%3Eannounced%3C%2FA%3Ethat%20organizations%20with%20Azure%20AD%20Premium%20and%20an%20Azure%20subscription%20could%20start%20to%20build%20custom%20reports%20on%20their%20Azure%20AD%20audit%20and%20sign%20in%20logs%2C%20by%20configuring%20Azure%20AD%20to%20send%20those%20logs%20to%20Azure%20Monitor.%26nbsp%3B%26nbsp%3B%20We%20also%20built%20several%20reports%20for%20sign%20in%20analysis%20as%20Azure%20AD%20workbooks%2C%20and%20showed%20to%20set%20triggers%20for%20alert%20notifications.%3C%2FP%3E%0A%3CP%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%3EOnce%20you've%20configured%20Azure%20AD%20to%20send%20logs%20to%20Azure%20Monitor%2C%20you%20can%20also%20access%20those%20logs%20through%20PowerShell%2C%20sending%20queries%20from%20scripts%20or%20from%20the%20PowerShell%20command%20line%2C%20without%20needing%20to%20be%20a%20Global%20Admin%20in%20the%20tenant.%20Because%20Azure%20services%20have%20changed%20their%20name%20in%20the%20past%20few%20years%2C%20it's%20sometimes%20challenging%20to%20figure%20out%20which%20PowerShell%20command%20to%20use.%20So%20I've%20written%20down%20a%20few%20steps%20I%20used%20when%20learning%20how%20to%20query%20Azure%20AD%20logs%20that%20have%20been%20sent%20to%20Azure%20Monitor.%3C%2FP%3E%0A%3CP%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%3EBefore%20you%20begin%2C%20if%20you%20haven't%20already%20configured%20this%20integration%20between%20Azure%20AD%20and%20Azure%20Monitor%2C%20you'll%20need%20to%20follow%20the%20steps%20to%20%3CA%20style%3D%22font-style%3A%20inherit%3B%20font-weight%3A%20inherit%3B%22%20href%3D%22https%3A%2F%2Fdocs.microsoft.com%2Fen-us%2Fazure%2Factive-directory%2Freports-monitoring%2Fhowto-integrate-activity-logs-with-log-analytics%22%20target%3D%22_blank%22%20rel%3D%22noopener%20noreferrer%20noopener%20noreferrer%22%3EIntegrate%20Azure%20AD%20logs%20with%20Azure%20Monitor%20logs%3C%2FA%3E.%20%26nbsp%3B%3C%2FP%3E%0A%3CP%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%3ENext%2C%20you'll%20want%20to%20ensure%20you%20(or%20the%20user%20or%20service%20principal%20who%20will%20be%20authenticating%20to%20Azure%20AD)%20are%20in%20the%20appropriate%20Azure%20role%20in%20the%20in%20the%20Log%20Analytics%20workspace%2C%20either%20the%20Log%20Analytics%20Reader%20role%2C%20or%20the%20Log%20Analytics%20Contributor%20role.%3C%2FP%3E%0A%3CP%3E%3CSPAN%20class%3D%22lia-inline-image-display-wrapper%20lia-image-align-center%22%20style%3D%22width%3A%20999px%3B%22%3E%3CIMG%20src%3D%22https%3A%2F%2Fgxcuf89792.i.lithium.com%2Ft5%2Fimage%2Fserverpage%2Fimage-id%2F126444iA4DA3362DE5A4EF2%2Fimage-size%2Flarge%3Fv%3D1.0%26amp%3Bpx%3D999%22%20alt%3D%22LA%20role%20check.png%22%20title%3D%22LA%20role%20check.png%22%20%2F%3E%3CSPAN%20class%3D%22lia-inline-image-caption%22%20onclick%3D%22event.preventDefault()%3B%22%3EAzure%20portal%20-%20Log%20Analytics%20role%20assignments%3C%2FSPAN%3E%3C%2FSPAN%3E%3C%2FP%3E%0A%3CP%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%3EYou%20can%20set%20this%20role%20assignment%20in%20the%20Azure%20Portal%20by%20locating%20the%20%3CA%20href%3D%22https%3A%2F%2Fportal.azure.com%2F%23blade%2FHubsExtension%2FBrowseResourceBlade%2FresourceType%2FMicrosoft.OperationalInsights%252Fworkspaces%22%20target%3D%22_blank%22%20rel%3D%22nofollow%20noopener%20noreferrer%20noopener%20noreferrer%22%3ELog%20Analytics%20workspace%3C%2FA%3E%2C%20clicking%20on%20%22Access%20Control%20(IAM)%22%20and%20clicking%20Add%20to%20add%20a%20role%20assignment.%3C%2FP%3E%0A%3CP%20style%3D%22font-style%3A%20inherit%3B%20font-weight%3A%20inherit%3B%22%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%3EThen%2C%20launch%20PowerShell%2C%20and%20then%20%3CA%20href%3D%22https%3A%2F%2Fdocs.microsoft.com%2Fen-us%2Fpowershell%2Fazure%2Finstall-az-ps%3Fview%3Dazps-2.5.0%22%20target%3D%22_blank%22%20rel%3D%22noopener%20noreferrer%20noopener%20noreferrer%22%3Einstall%20the%20Azure%20PowerShell%20module%3C%2FA%3E%2C%20if%20you%20haven%E2%80%99t%20already%2C%20by%20typing%3C%2FP%3E%0A%3CP%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%3Einstall-module%20-Name%20az%20-allowClobber%20-Scope%20CurrentUser%3C%2FP%3E%0A%3CP%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%3ENow%20you're%20ready%20to%20authenticate%20to%20Azure%20AD%2C%20and%20retrieve%20the%20id%20of%20the%20Log%20Analytics%20workspace%20you%E2%80%99ll%20be%20querying.%20If%20you%20have%20only%20a%20single%20Azure%20subscription%2C%20and%20a%20single%20Log%20Analytics%20workspace%2C%20then%20authenticate%20to%20Azure%20AD%2C%20connecting%20to%20that%20subscription%20and%20retrieving%20that%20workspace%2C%20by%20typing%3C%2FP%3E%0A%3CP%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%3EConnect-AzAccount%3C%2FP%3E%0A%3CP%3E%24wks%20%3D%20Get-AzOperationalInsightsWorkspace%3C%2FP%3E%0A%3CP%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%3EIt's%20important%20to%20note%20that%20Get-AzOperationalInsightsWorkspace%20operates%20in%20one%20subscription%20at%20a%20time.%20If%20you%20have%20multiple%20Azure%20subscriptions%2C%20then%20you'll%20want%20to%20ensure%20you%20connect%20to%20the%20one%20which%20has%20the%20Log%20Analytics%20workspace%20with%20the%20Azure%20AD%20logs.%20The%20cmdlets%3C%2FP%3E%0A%3CP%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%3EConnect-AzAccount%3C%2FP%3E%0A%3CP%3E%24subs%20%3D%20Get-AzSubscription%3C%2FP%3E%0A%3CP%3E%24subs%20%7C%20ft%3C%2FP%3E%0A%3CP%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%3Edisplays%20a%20list%20of%20subscriptions%2C%20and%20then%20find%20the%20id%20of%20the%20subscription%20which%20has%20the%20Log%20Analytics%20workspace.%20You%20can%20re-authenticate%20and%20associate%20your%20PowerShell%20session%20to%20that%20subscription%20using%20a%20command%20such%20as%20Connect-AzAccount%20%E2%80%93Subscription%20%24subs%5B0%5D.id%20.%20(And%20to%20learn%20more%20about%20how%20to%20authenticate%20to%20Azure%20from%20PowerShell%2C%20including%20non-interactively%2C%20see%20%3CA%20href%3D%22https%3A%2F%2Fdocs.microsoft.com%2Fen-us%2Fpowershell%2Fazure%2Fauthenticate-azureps%3Fview%3Dazps-2.5.0%22%20target%3D%22_blank%22%20rel%3D%22noopener%20noreferrer%20noopener%20noreferrer%22%3ESign%20in%20with%20Azure%20PowerShell%3C%2FA%3E.%20)%3C%2FP%3E%0A%3CP%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%3EIf%20you%20have%20multiple%20Log%20Analytics%20workspaces%20in%20that%20subscription%2C%20then%20the%20cmdlet%20Get-AzOperationalInsightsWorkspace%20returns%20the%20list%20of%20workspaces%2C%20so%20you%20can%20find%20the%20one%20which%20has%20the%20Azure%20AD%20logs.%20The%20CustomerId%20field%20returned%20by%20this%20cmdlet%20is%20the%20same%20as%20the%20value%20of%20the%20%22Workspace%20id%22%20displayed%20in%20the%20Azure%20Portal%20in%20the%20Log%20Analytics%20workspace%20overview.%3C%2FP%3E%0A%3CP%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%3E%24wks%20%3D%20Get-AzOperationalInsightsWorkspace%3C%2FP%3E%0A%3CP%3E%24wks%20%7C%20ft%20CustomerId%2C%20Name%3C%2FP%3E%0A%3CP%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%3EFinally%2C%20once%20you%20have%20a%20workspace%20identified%2C%20you%20can%20use%20%3CA%20href%3D%22https%3A%2F%2Fdocs.microsoft.com%2Fen-us%2Fpowershell%2Fmodule%2Faz.operationalinsights%2FInvoke-AzOperationalInsightsQuery%3Fview%3Dazps-2.5.0%22%20target%3D%22_blank%22%20rel%3D%22noopener%20noreferrer%20noopener%20noreferrer%22%3EInvoke-AzOperationalInsightsQuery%3C%2FA%3Eto%20send%20a%20Kusto%20query%20to%20that%20workspace.%20These%20queries%20are%20written%20%3CA%20style%3D%22font-style%3A%20inherit%3B%20font-weight%3A%20inherit%3B%22%20href%3D%22https%3A%2F%2Fdocs.microsoft.com%2Fen-us%2Fazure%2Fkusto%2Fquery%2F%22%20target%3D%22_blank%22%20rel%3D%22noopener%20noreferrer%20noopener%20noreferrer%22%3EKusto%20query%20language%3C%2FA%3E.%3C%2FP%3E%0A%3CP%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%3EFor%20example%2C%20you%20can%20retrieve%20old%20sign%20in%20records%20from%20the%20Log%20Analytics%20workspace%2C%20with%20a%20query%20like%3C%2FP%3E%0A%3CP%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%3E%24sQuery%20%3D%20%22SigninLogs%20%7C%20where%20TimeGenerated%20%26gt%3B%20ago(3653d)%20%7C%20order%20by%20TimeGenerated%20asc%20%7C%20limit%2010%22%3C%2FP%3E%0A%3CP%3E%24sResponse%20%3D%20Invoke-AzOperationalInsightsQuery%20-WorkspaceId%20%24wks%5B0%5D.CustomerId%20-Query%20%24sQuery%3C%2FP%3E%0A%3CP%3E%24sResponse.Results%20%7C%20ft%20TimeGenerated%2CuserDisplayName%2CUserPrincipalName%3C%2FP%3E%0A%3CP%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%3EOr%20audit%20records%20with%3C%2FP%3E%0A%3CP%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%3E%24aQuery%20%3D%20%22AuditLogs%20%7C%20where%20TimeGenerated%20%26gt%3B%20ago(3653d)%20%7C%20order%20by%20TimeGenerated%20asc%20%7C%20limit%2010%22%3C%2FP%3E%0A%3CP%3E%24aResponse%20%3D%20Invoke-AzOperationalInsightsQuery%20-WorkspaceId%20%24wks%5B0%5D.CustomerId%20-Query%20%24aQuery%3C%2FP%3E%0A%3CP%3E%24aResponse.Results%20%7Cft%20TimeGenerated%2CCategory%2COperationName%2CResult%3C%2FP%3E%0A%3CP%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%3EI%20hope%20to%20find%20more%20interesting%20examples%20of%20Kusto%20queries%20for%20the%20Sign%20in%20and%20Audit%20logs%20in%20future.%20Thanks!%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-LABS%20id%3D%22lingo-labs-798199%22%20slang%3D%22en-US%22%3E%3CLINGO-LABEL%3EAzure%20AD%3C%2FLINGO-LABEL%3E%3CLINGO-LABEL%3EIdentity%20Management%3C%2FLINGO-LABEL%3E%3C%2FLINGO-LABS%3E
Mark Wahl
Microsoft

Some questions I'm asked frequently about Azure AD - how can I see and retain more than 30 days of audit events from Azure AD features? And how can I get that audit history programmatically, without needing to sign in as a highly-privileged Azure AD administrator, in order to download records for a report to or answer an auditor’s inquiry?

 

Last year we announced that organizations with Azure AD Premium and an Azure subscription could start to build custom reports on their Azure AD audit and sign in logs, by configuring Azure AD to send those logs to Azure Monitor.   We also built several reports for sign in analysis as Azure AD workbooks, and showed to set triggers for alert notifications.

 

Once you've configured Azure AD to send logs to Azure Monitor, you can also access those logs through PowerShell, sending queries from scripts or from the PowerShell command line, without needing to be a Global Admin in the tenant. Because Azure services have changed their name in the past few years, it's sometimes challenging to figure out which PowerShell command to use. So I've written down a few steps I used when learning how to query Azure AD logs that have been sent to Azure Monitor.

 

Before you begin, if you haven't already configured this integration between Azure AD and Azure Monitor, you'll need to follow the steps to Integrate Azure AD logs with Azure Monitor logs.  

 

Next, you'll want to ensure you (or the user or service principal who will be authenticating to Azure AD) are in the appropriate Azure role in the in the Log Analytics workspace, either the Log Analytics Reader role, or the Log Analytics Contributor role.

LA role check.pngAzure portal - Log Analytics role assignments

 

You can set this role assignment in the Azure Portal by locating the Log Analytics workspace, clicking on "Access Control (IAM)" and clicking Add to add a role assignment.

 

Then, launch PowerShell, and then install the Azure PowerShell module, if you haven’t already, by typing

 

install-module -Name az -allowClobber -Scope CurrentUser

 

Now you're ready to authenticate to Azure AD, and retrieve the id of the Log Analytics workspace you’ll be querying. If you have only a single Azure subscription, and a single Log Analytics workspace, then authenticate to Azure AD, connecting to that subscription and retrieving that workspace, by typing

 

Connect-AzAccount

$wks = Get-AzOperationalInsightsWorkspace

 

It's important to note that Get-AzOperationalInsightsWorkspace operates in one subscription at a time. If you have multiple Azure subscriptions, then you'll want to ensure you connect to the one which has the Log Analytics workspace with the Azure AD logs. The cmdlets

 

Connect-AzAccount

$subs = Get-AzSubscription

$subs | ft

 

displays a list of subscriptions, and then find the id of the subscription which has the Log Analytics workspace. You can re-authenticate and associate your PowerShell session to that subscription using a command such as Connect-AzAccount –Subscription $subs[0].id . (And to learn more about how to authenticate to Azure from PowerShell, including non-interactively, see Sign in with Azure PowerShell. )

 

If you have multiple Log Analytics workspaces in that subscription, then the cmdlet Get-AzOperationalInsightsWorkspace returns the list of workspaces, so you can find the one which has the Azure AD logs. The CustomerId field returned by this cmdlet is the same as the value of the "Workspace id" displayed in the Azure Portal in the Log Analytics workspace overview.

 

$wks = Get-AzOperationalInsightsWorkspace

$wks | ft CustomerId, Name

 

Finally, once you have a workspace identified, you can use Invoke-AzOperationalInsightsQuery to send a Kusto query to that workspace. These queries are written Kusto query language.

 

For example, you can retrieve old sign in records from the Log Analytics workspace, with a query like

 

$sQuery = "SigninLogs | where TimeGenerated > ago(3653d) | order by TimeGenerated asc | limit 10"

$sResponse = Invoke-AzOperationalInsightsQuery -WorkspaceId $wks[0].CustomerId -Query $sQuery

$sResponse.Results | ft TimeGenerated,userDisplayName,UserPrincipalName

 

Or audit records with

 

$aQuery = "AuditLogs | where TimeGenerated > ago(3653d) | order by TimeGenerated asc | limit 10"

$aResponse = Invoke-AzOperationalInsightsQuery -WorkspaceId $wks[0].CustomerId -Query $aQuery

$aResponse.Results |ft TimeGenerated,Category,OperationName,Result

 

I hope to find more interesting examples of Kusto queries for the Sign in and Audit logs in future. Thanks!

Related Conversations
Extentions Synchronization
Deleted in Discussions on
3 Replies
Tabs and Dark Mode
cjc2112 in Discussions on
38 Replies
flashing a white screen while open new tab
Deleted in Discussions on
14 Replies
Stable version of Edge insider browser
HotCakeX in Discussions on
35 Replies
Security Community Webinars
Valon_Kolica in Security, Privacy & Compliance on
13 Replies