Using Azure Log Analytics to retrieve logs for Report-Only Conditional Access Policies

I’ve recently been working on reviewing conditional access policies in Azure AD. Thankfully this process has become much easier than the early days with the introduction of Azure Monitor and Report-Only mode conditional access policies which allow you to properly pilot a configuration before going live.

I needed to grab an export of all sign-ins that were failing a particular report-only policy that was set up to block legacy authentication. This led me down the path of Azure Monitor and writing my first KQL query.

Note that this process depends on having set up streaming of Azure AD logs into Azure Monitor.

This KQL query grabs all sign-ins that have failed a report-only conditional access policy, and outputs the sign-in data alongside information about the policy in question:



Here’s the KQL query code:

// Get Sign-in logs for any Report-Only Conditional Access policies where the result = ReportOnlyFailure
| mvexpand ConditionalAccessPolicies
| where ConditionalAccessPolicies["result"] == "reportOnlyFailure"
| project TimeGenerated, Identity, UserPrincipalName, AzureADApplication = AppDisplayName, ClientApplication = ClientAppUsed, ClientBrowser = DeviceDetail.browser, ClientOperatingSystem = DeviceDetail.operatingSystem, ClientIPAddress = IPAddress , ClientUserAgent = UserAgent , ConditionalAccessPolicyName = ConditionalAccessPolicies["displayName"], ConditionalAccessPolicyID = ConditionalAccessPolicies["id"]

To explain what the query does:

  1. Retrieves all sign-in logs
  2. Uses mvexpand to expand the ConditionalAccessPolicies collection that’s included along with each sign-in’s data. The collection contains one object per conditional access policy in the Azure AD environment
  3. Narrows down the list to only sign-ins where the result of a policy was a “reportOnlyFailure”
  4. Uses the ‘project’ operator to retrieve only the data we’re interested in

From here, you can export the data to CSV and work your magic with it.

Azure: Generate RDP files for multiple VMs

Today I needed to pass on the RDP files for a bunch of Azure VMs to an external developer.

Rather than manually click on the download links in the Azure Portal each time these dev VMs are re-generated, I took the PowerShell route. This was simplified by the fact that all of the VMs in question had a similar Service Name.

Here’s the two lines it took to generate the RDP files, assuming that the Azure Service name for these VMs started with ‘dev-‘, and also assuming that I’m already connected to Azure PowerShell and have the correct subscription selected:

$devVms = Get-AzureVM | Where-Object {$_.ServiceName -like 'dev-*'} 
$devVms | ForEach-Object {Get-AzureRemoteDesktopFile -Name $_.Name -LocalPath "C:\temp\dev_rdp\$($_.Name).rdp" -ServiceName $_.ServiceName}

Office 365/Azure AD: Find all users with an email address that matches a specific string

We have a lot of shared mailboxes in Office 365. Today, I needed to find a subset of those,  change their UPN, and set the FirstName and LastName attributes.

I came across this lengthy PowerShell script that someone had created years ago to find users, but the simplest way to do this is via this one line of PowerShell, once you’ve connected to Azure AD:

Get-MsolUser -MaxResults 1000 | Where-Object {($_.ProxyAddresses -like '*texttomatch*')}

An alternative method, if you’re not connected to Azure PowerShell, but are connected to Exchange Online via PowerShell, is to use Get-Mailbox:

Get-Mailbox -ResultSize 1000 | ? {$_.EmailAddresses -like '*texttomatch*'}