Identify unused API connections a little easier

A while back I blogged about a script I did which can be used to identify any API connections in our resource groups which are not being used by a Logic App.

If you combine azure resource graph and powershell then you can do this in a bit of an easier way than I did last time.

The below script will create 2 csv files listing the connections which arent used and the ones that are used and which logic app uses them

#This will check for SAP API Connections and look at where they are pointing to
param (
    [string]$resourceGroupName = "mikes-rg",
    
    [string]$subscriptionId = '',

    [string]$outputPath = 'C:\Temp'
)

#use the collection to build up objects for the table
$usedConnectionList = New-Object "System.Collections.Generic.List[System.Object]" 
$unusedConnectionList = New-Object "System.Collections.Generic.List[System.Object]" 

$resourceGroupToLower = $resourceGroupName.ToLower()

Write-Host "Inputs" -ForegroundColor Green
Write-Host "======" -ForegroundColor Green
Write-Host 'Resource Group (to lower for kusto query): '  $resourceGroupToLower -ForegroundColor White
Write-Host 'Subscription: '  $subscriptionId -ForegroundColor White
Write-Host 'Output Path: '  $outputPath -ForegroundColor White

# Lookup API Connections
$apiConnectionsQuery = "resources
| where type == 'microsoft.web/connections'
| where resourceGroup == '$resourceGroupToLower'
| where subscriptionId == '$subscriptionId'
| project id, name"

Write-Host $apiConnectionsQuery

Write-Host "Query API Connections" -ForegroundColor Green
$apiConnectionsQueryResponse = Search-AzGraph $apiConnectionsQuery -First 1000
Write-Host $apiConnectionsQueryResponse.Count " items returned" -ForegroundColor White

foreach($apiConnection in $apiConnectionsQueryResponse){
    $isConnectionUsed = $false

    Write-Host 'Processing API Connection' -ForegroundColor Green
    Write-Host '=========================' -ForegroundColor Green
    Write-Host 'Connection Name:' $apiConnection.Name
    Write-Host 'Connection Id:' $apiConnection.Id
    Write-Host ''

    $connectionId = $apiConnection.Id
    $connectionName = $apiConnection.Name
    
    $logicAppQuery = "resources
        | where type == 'microsoft.logic/workflows'
        | where resourceGroup == '$resourceGroupToLower'
        | where subscriptionId == '$subscriptionId'
        | where properties contains '$connectionId'
        | project id, name"

    $logicAppQueryResponse = Search-AzGraph $logicAppQuery -First 1000
    Write-Host $logicAppQueryResponse.Count " logic apps found with this api connection"
    foreach($logicApp in $logicAppQueryResponse){
        Write-Host $logicApp.Name ' uses: ' $connectionName

        $isConnectionUsed = $true

        #Add to dictionary for output files
        $usedConnectionItem = New-Object -TypeName psobject
        $usedConnectionItem | Add-Member -MemberType NoteProperty -Name 'IsUsed' -Value 'TRUE'        
        $usedConnectionItem | Add-Member -MemberType NoteProperty -Name 'ConnectionName' -Value $connectionName
        $usedConnectionItem | Add-Member -MemberType NoteProperty -Name 'LogicAppName' -Value $logicApp.Name
        $usedConnectionItem | Add-Member -MemberType NoteProperty -Name 'ConnectionId' -Value $connectionId
        $usedConnectionItem | Add-Member -MemberType NoteProperty -Name 'LogicAppId' -Value $logicApp.Id
        

        $usedConnectionList.Add($usedConnectionItem)  
    }

    if($isConnectionUsed -eq $false){

        #Add to dictionary for output files
        $unUsedConnectionItem = New-Object -TypeName psobject
        $unUsedConnectionItem | Add-Member -MemberType NoteProperty -Name 'IsUsed' -Value 'FALSE'
        $unUsedConnectionItem | Add-Member -MemberType NoteProperty -Name 'ConnectionName' -Value $connectionName
        $unUsedConnectionItem | Add-Member -MemberType NoteProperty -Name 'ConnectionId' -Value $connectionId
        
        $unusedConnectionList.Add($unUsedConnectionItem)  

    }

    Write-Host ''
    Write-Host ''
}

Write-Host 'Saving Files' -ForegroundColor Green
$outputFilePath = $outputPath + '\API-Connections-Used-' + $resourceGroupName + '.csv'
$usedConnectionList | Export-Csv -Path $outputFilePath

$outputFilePath = $outputPath + '\API-Connections-Unused-' + $resourceGroupName + '.csv'
$unusedConnectionList | Export-Csv -Path $outputFilePath

Write-Host 'Complete - Files produced with output reports'  -ForegroundColor Green