Get sign in information for app registrations and service principals through EntraID/Log Analytics

appRegistrationSignIns.ps1

<#
.SYNOPSIS
This script retrieves Azure Active Directory (AD) applications with expired secrets and checks their sign-in logs.

.DESCRIPTION
The script first retrieves all Azure AD applications using the Get-AzADApplication cmdlet. It then filters these applications to only include those where the password credential has expired more than 30 days ago and where there is only one password credential.
For each of these applications, the script retrieves the AppId and constructs three queries to check the sign-in logs for the last 30 days. The queries are for service principal sign-ins, non-interactive user sign-ins, and interactive sign-ins.
The script then executes these queries using the Invoke-AzOperationalInsightsQuery cmdlet and stores the results in three separate variables: $servicePrincipalSignins, $nonInteractiveSignins, and $interactiveSignins.

Finally, the script outputs the results of these queries.

.NOTES
You need the Az module to run this script. If you don't have the Az module installed, you can install it using the Install-Module cmdlet.
#>

$workspaceId = "30aa75a1-60e8-4009-9bbb-704eb12796e4"
$passwordAge = (Get-Date).AddYears(-1)
$appAge = (Get-Date).AddYears(-2)

$appRegs = get-azadapplication | Where-Object {
    $_.CreatedDateTime -lt $appAge -and ($_.PasswordCredentials | ForEach-Object {
        $credentialEndDate = $_.EndDateTime
        $credentialEndDate -lt $passwordAge
    } | Measure-Object -Sum | Where-Object { $_.Sum -eq $_.Count })
}

$appIds = $appRegs.AppId
$servicePrincipalSignInQuery = "AADServicePrincipalSignInLogs | where AppId in ('" + ($appIds -join "','") + "') and TimeGenerated between (ago(30d) .. now() ) | summarize count() by ServicePrincipalName, AppId"
$nonInteractiveSignInQuery = "AADNonInteractiveUserSignInLogs | where AppId in ('" + ($appIds -join "','") + "') and TimeGenerated between (ago(30d) .. now() ) | summarize count() by ServicePrincipalId, AppId"
$interactiveSignInQuery = "SigninLogs | where AppId in ('" + ($appIds -join "','") + "') and TimeGenerated between (ago(30d) .. now() ) | summarize count() by AppDisplayName, AppId"

$servicePrincipalSignins = (Invoke-AzOperationalInsightsQuery -WorkspaceId $workspaceId -Query $servicePrincipalSignInQuery)
$nonInteractiveSignins = (Invoke-AzOperationalInsightsQuery -WorkspaceId $workspaceId -Query $nonInteractiveSignInQuery)
$interactiveSignins = (Invoke-AzOperationalInsightsQuery -WorkspaceId $workspaceId -Query $interactiveSignInQuery)

$appregsToDelete = $appregs | Where-Object { 
    $appId = $_.AppId
    $servicePrincipalSignins.Results.AppId -notcontains $appId -and 
    $nonInteractiveSignins.Results.AppId -notcontains $appId -and 
    $interactiveSignins.Results.AppId -notcontains $appId
}

$appregsToDelete