Entra ID application permission report with usage

Enterprise applications and app registrations are two of the most dangerous things in Entra ID if managed incorrectly. They grant access to your data and resources, and if compromised, can lead to data breaches and other security incidents. Do you have control over all permissions granted over the course of your tenants lifetime? When was the last time you deleted stale permissions?

Application permission report

My collegue Sridhar linked me a PowerShell module called MSIdentity Tools, which contains a command called Export-MsIdAppConsentGrantReport. This command exports a report of all application permissions granted in the tenant, together with some additional helpful information related to risk.

As I have previously created a script for extracting application usage information from the audit logs, I decided to combine the two to easily see which applications are stale.

Pre-requisites: You need to have set up export of audit logs to a Log Analytics workspace. You can follow the guide here. Two main reasons are longer history, and the ability to query the logs using Kusto Query Language (KQL) which is so so much faster for large tenants. Most companies already export this data for usage in Sentinel or other monitoring tools, so check with your security team if you want access. In this example I have hardcoded 180d usage logs, but you can change this to whatever you want.

$tenantId = <tenantId>
$subscription = <subscription>
$resourceGroupName = <resourceGroupName>
$logAnalyticsWorkspace = <logAnalyticsWorkspace>

Connect-AzAccount -Tenant $tenantId
set-AzContext $subscription
$workspace = Get-AzOperationalInsightsWorkspace -ResourceGroupName $resourceGroupName -Name $logAnalyticsWorkspace

# Number of sign ins per app by service principals
$kqlQuery = '
AADServicePrincipalSignInLogs
| where TimeGenerated > ago(180d)
| where ResultType == 0
| summarize count() by AppId
'
$AADServicePrincipalSignInLogs = Invoke-AzOperationalInsightsQuery -Workspace $Workspace -Query $kqlQuery

# Number of sign ins by service principals themselves.
# This will count sign ins by service principals twice, but its the only way I have figured out to get activity for service principals.
$kqlQuery = '
AADServicePrincipalSignInLogs
| where TimeGenerated > ago(180d)
| where ResultType == 0
| summarize count() by ServicePrincipalId
'
$AADServicePrincipalSignInLogsActor = Invoke-AzOperationalInsightsQuery -Workspace $Workspace -Query $kqlQuery

# Non-interactive sign ins. Probably the least valuable data as it is almost always included in the sign in logs.
$kqlQuery = '
AADNonInteractiveUserSignInLogs
| where TimeGenerated > ago(180d)
| where ResultType == 0
| summarize count() by AppId
'
$AADNonInteractiveUserSignInLogs = Invoke-AzOperationalInsightsQuery -Workspace $Workspace -Query $kqlQuery

# Number of sign ins per app by a managed identity
$kqlQuery = '
AADManagedIdentitySignInLogs
| where TimeGenerated > ago(180d)
| where ResultType == 0
| summarize count() by AppId
'
$AADManagedIdentitySignInLogs = Invoke-AzOperationalInsightsQuery -Workspace $Workspace -Query $kqlQuery

# Number of sign ins per app by managed identity themselves.
# This will count sign ins by service principals twice, but its the only way I have figured out to get activity for managed identity.
$kqlQuery = '
AADManagedIdentitySignInLogs
| where TimeGenerated > ago(180d)
| where ResultType == 0
| summarize count() by ServicePrincipalId
'
$AADManagedIdentitySignInLogsActor = Invoke-AzOperationalInsightsQuery -Workspace $Workspace -Query $kqlQuery

# Normal interactive sign ins
$kqlQuery = '
SigninLogs
| where TimeGenerated > ago(180d)
| where ResultType == 0
| summarize count() by AppId
'
$SigninLogs = Invoke-AzOperationalInsightsQuery -Workspace $Workspace -Query $kqlQuery

$signinlogshash = @{}
$signinlogs.results | ForEach-Object {
    if($signinlogshash[$_."AppId"]) {
        $signinlogshash[$_."AppId"] += [int]$_."count_"
    }
    else {
        $signinlogshash.Add($_."AppId",[int]$_."count_")
    }
}

$AADServicePrincipalSignInLogs.results | ForEach-Object {
    if($signinlogshash[$_."AppId"]) {
        $signinlogshash[$_."AppId"] += [int]$_."count_"
    }
    else {
        $signinlogshash.Add($_."AppId",[int]$_."count_")
    }
}

$AADServicePrincipalSignInLogsActor.results | ForEach-Object {
    if($signinlogshash[$_."ServicePrincipalId"]) {
        $signinlogshash[$_."ServicePrincipalId"] += [int]$_."count_"
    }
    else {
        $signinlogshash.Add($_."ServicePrincipalId",[int]$_."count_")
    }
}

$AADNonInteractiveUserSignInLogs.results | ForEach-Object {
    if($signinlogshash[$_."AppId"]) {
        $signinlogshash[$_."AppId"] += [int]$_."count_"
    }
    else {
        $signinlogshash.Add($_."AppId",[int]$_."count_")
    }
}

$AADManagedIdentitySignInLogs.results | ForEach-Object {
    if($signinlogshash[$_."AppId"]) {
        $signinlogshash[$_."AppId"] += [int]$_."count_"
    }
    else {
        $signinlogshash.Add($_."AppId",[int]$_."count_")
    }
}

$AADManagedIdentitySignInLogsActor.results | ForEach-Object {
    if($signinlogshash[$_."ServicePrincipalId"]) {
        $signinlogshash[$_."ServicePrincipalId"] += [int]$_."count_"
    }
    else {
        $signinlogshash.Add($_."ServicePrincipalId",[int]$_."count_")
    }
}

The above code generates a hash table with the AppId as the key and the total number of successful sign-ins as the value.

Next, we will generate the app consent report using Export-MsIdAppConsentGrantReport and add the sign-in count to the report.

# Pre-requisites:
# Install-Module -Name MSIdentityTools
# Install-Module ImportExcel

Connect-MgGraph -Scopes Directory.Read.All

$appConsent = Export-MsIdAppConsentGrantReport -ReportOutputType PowerShellObjects

$appConsent | ForEach-Object {
    if($signinlogshash[$_.AppId]) {
        $_ | Add-Member -MemberType NoteProperty -Name SignInCount -Value $signinlogshash[$_.AppId]
    }
    else {
        $_ | Add-Member -MemberType NoteProperty -Name SignInCount -Value 0
    }
}

$appConsent | Export-Excel '.\appConsentReportWithSignIns.xlsx'

This report is just an example of how you can combine different data sources to get a better understanding of your environment. If you need to understand who the users of the application are, you can either check the sign in logs in the GUI or export the full usage logs using KQL.

Example KQL query for getting the users of an application from the SigninLogs:

SigninLogs
| where TimeGenerated > ago(180d)
| where UserPrincipalName has ""@""
| where ResultType == 0
| where AppId == '$_'
| summarize signInCount = count() by UserPrincipalName
| sort by signInCount desc

Full code can be found here.

Application permission report#

Application permission report