Find out which On-premises Data Gateways are used by which API connection resources

API connections resources work as the bridges for Logic App to communicate with other services. While most of them are used to connect to cloud resources, a few of them leverage On-premises Data Gateways to connect local data sources to Azure. However, we may find that it is not easy to check which On-premises Data Gateway (OPDG) is used by which API connection resource.

Objective:

Find out which On-premises Data Gateways are used by which API connection resources.

Workaround:

The most known way to check OPDG used by a connector is by inspecting the Logic App designer.

• When creating API connections with OPDG, we can find the OPDG used from the connection list in the Logic App designer.

However, this method seems unhandy as we need to go every Logic App’s designer mode and may cause unwanted change. My preferred way is to check the sourced OPDG from the JSON definition of an API connection resource.

• From a Logic App, open the API connection blade, and choose the API connection which is using the OPDG.

• From the API connection resource main page, click on “JSON View” on the far right.

• In the JSON definition of the API connection, you will find the OPDG’s name, resource ID in the “properties” > “parameterValues” > “gateway”.

One more step:

As we have found out that OPDG information can be extracted from the JSON definition of an API connection resource. We can use a PowerShell script to find out all API connections which is using an OPDG.

Prerequisites:

• An active Azure subscription.
• An Azure account, which has the permission to read all resources within the above subscription.
• Before running the script, please install the Azure Az module for your PowerShell. Introducing the Azure Az PowerShell module | Microsoft Docs
• To get the script, you may find it at the bottom of the page or visit my repository here. 

Run the script:

• After downloading the script into your machine, put in the name of your Azure subscription for the subscriptionName variable.

• Then run the script and log into your Azure account in the pop-up window.

• A CSV file named “output.csv” will be created in the same folder where the script resides.

• The CSV file will contain all API connection resources along with the OPDGs they are using.

Columns:

connectionName: name of the API connection resource

connectionId: resource ID of the API connection

gatewayName: name of the On-premises Data Gateway resource

gatewatyId: resource ID of the On-premises Data Gateway

Notes:

• If you encountered the "script not signed" error, you might try the below PowerShell command to bypass the security policy in the current session.

  Set-ExecutionPolicy -Scope Process -ExecutionPolicy Bypass​

• Alternative to run the script locally, you may choose to run this through Azure cloud shell. However, please remember the first command "Connect-AzAccount" as it will no longer be required.

Raw script:

# If you encountered "script is not signed error", please use below comand in the PowerShell first.
# Set-ExecutionPolicy -Scope Process -ExecutionPolicy Bypass

# Login to your Azure Account:
Connect-AzAccount

# Put in your own subscription name:
$subsctionName = ''

Set-AzContext -SubscriptionName $subsctionName

# Get all API connection resources 
$connectionList = Get-AzResource -ResourceType 'Microsoft.Web/connections'


# Loop through connection list
Write-Host '---------------------------------------'
Write-Host '  Start loop connections - Processing'
Write-Host '---------------------------------------'
$result = @()
foreach ($connection in $connectionList){
    $currentConnectionObj = Get-AzResource -ResourceId $connection.ResourceId
    $connectionName = $currentConnectionObj.Name
    $connectionId = $connection.ResourceId 
    Write-Host 'Processing connection:' $connectionName
    # Extract gateway information
    $opdgName = $currentConnectionObj.Properties.parameterValues.gateway.name
    $opdgId = $currentConnectionObj.Properties.parameterValues.gateway.id
    if (!($opdgId -eq $null)){
        Write-Host '------------------------------------------------'
        Write-Host 'OPDG found:'
        Write-Host 'Connection' $connectionName 'is using gateway' $opdgName
        Write-Host '------------------------------------------------'

        $new = [PSCustomObject]@{
            connectionName = $connectionName
            connectionId = $connectionId
            gatewayName = $opdgName
            gatewayId = $opdgId
        }

        $result += $new
    }
}

# Write collected information to output.csv file
$result | Export-Csv -Path .\output.csv -NoTypeInformation