Report service principal connections

Connect-MgGraph -Scopes AuditLog.Read.All, Directory.Read.All

param(
    [string] $TenantId = "$Tenant.Id"
)
 
Connect-MgGraph -Scopes AuditLog.Read.All, Directory.Read.All
 
# Find what our tenant is
$Tenant = Get-MgOrganization
 
# Get the last 5000 sign-ins for service principals (going back a maximum of 30 days)
Write-Host "Finding service principal sign-ins..."
[array]$AuditRecords = Get-MgBetaAuditLogSignIn -Filter "(signInEventTypes/any(t:t eq 'servicePrincipal'))" -Top 5000 -Sort "createdDateTime DESC"
If (!$AuditRecords) {
    Write-Host "Unable to find audit records - exiting" ; break
}   Else {
    Write-Host "Found $($AuditRecords.Count) audit records"
}
 
Write-Host "Processing audit records..."
$Report = [System.Collections.Generic.List[Object]]::new()
ForEach ($Record in $AuditRecords) {
    # If the record is not generated by a connection from our tenant, find out the tenant name
    If ($Record.additionalProperties.appOwnerTenantId -eq $Tenant.Id) {
        $TenantName = $Tenant.DisplayName
    } Else {
        $Uri = ("https://graph.microsoft.com/V1.0/tenantRelationships/findTenantInformationByTenantId(tenantId='{0}')" -f $Record.additionalProperties.appOwnerTenantId.ToString())
        $ExternalTenantData = Invoke-MgGraphRequest -Uri $Uri -Method Get
        $TenantName = $ExternalTenantData.DisplayName
    }
    # If the sign-in attempt was unsucccessful, report the error
    If ($Record.Status.errorCode -eq 0) {
        $SignInStatus = "Success"
    } Else {
        Switch ($Record.Status.errorCode) {  # See https://learn.microsoft.com/en-us/entra/identity-platform/reference-error-codes
            "70001" {
                $SignInStatus = "Failure (app is disabled)" }
            "700030" {
                $SignInStatus = "Failure (Invalid certificate)" }
            "7000222" {
                $SignInStatus = "Failure (Expired client secret)" }
            "7000215" {
                $SignInStatus = "Failure (Invalid client secret)" }
            "Default" {
                $SignInStatus = ("Failure (Error Code: {0})" -f $Record.Status.errorCode)
            }
        }
    }
 
    # Generate the report line
    $ReportLine = [PSCustomObject][Ordered]@{
        Timestamp           = $Record.CreatedDateTime
        Application         = $Record.AppDisplayName
        Credential          = $Record.ClientCredentialType
        Status              = $SignInStatus
        IPAddress           = $Record.IPAddress
        FromLocation        = $Record.Location.City
        Resource            = $Record.ResourceDisplayName
        AppId               = $Record.AppId
        ServicePrincipalId  = $Record.ServicePrincipalId
        OwningTenantId      = $TenantId
        OwningTenant        = $TenantName
    }
    $Report.Add($ReportLine)
}
 
$ToDate = (Get-Date $AuditRecords[0].CreatedDateTime -format 'dd-MMM-yyyy')
$FromDate = (Get-Date $AuditRecords[-1].CreatedDateTime -format 'dd-MMM-yyyy')
$Title = ("Service Principal Sign-ins from {0} to {1}" -f $FromDate, $ToDate)
 
Write-Host "Generating report..."
If (Get-Module ImportExcel -ListAvailable) {
    $ExcelGenerated = $True
    Import-Module ImportExcel -ErrorAction SilentlyContinue
    $ExcelOutputFile = ((New-Object -ComObject Shell.Application).Namespace('shell:Downloads').Self.Path) + "\Service Principal SignIns.xlsx"
    If (Test-Path $ExcelOutputFile) {
        Remove-Item $ExcelOutputFile -ErrorAction SilentlyContinue
    }
    $Report | Export-Excel -Path $ExcelOutputFile -WorksheetName "Service Principal Sign-in Logs" -Title $Title -TitleBold -TableName "ServicePrincipalSignIns"
} Else {
    $CSVOutputFile = ((New-Object -ComObject Shell.Application).Namespace('shell:Downloads').Self.Path) + "\Service Principal SignIns.CSV"
    $Report | Export-Csv -Path $CSVOutputFile -NoTypeInformation -Encoding Utf8
}
 
If ($ExcelGenerated) {
    Write-Host ("An Excel report detailing Service Principal audit log sign-in records is available in {0}" -f $ExcelOutputFile)
} Else {
    Write-Host ("A CSV report of underused Microsoft 365 Copilot licenses is available in {0}" -f $CSVOutputFile)
}