Identify Logic Apps Consumption that are using deprecated V1 Actions and Triggers for SQL Connector

[Omar_Abu_Arisheh]

It has been announced [Announcement] that the V1 Actions/Triggers of the SQL Connector for Logic Apps will be deprecated by end of March 2024.

In this article we will be using a PowerShell Script to identify the Logic Apps still using the deprecated SQL Connector's Actions/Triggers so you can manually change them to use their V2 equivalent.

The script can be altered to check if the returned action is a SQL one as some other connectors use a similar path to the one we are relying on to identify the V1 deprecated actions.

Steps:

1. Open CloudShell: My Dashboard - Microsoft Azure

2. Wait until it logs in.

3. Replace the SubscriptionId with yours.

4. Replace the ResourceGroupName with yours (optional).

5. Paste the script into the command window and hit enter when it finishes loading.

6. Take the output and then check the Logic Apps if they are actually using the SQL Connector's deprecated V1 Actions or if it is a different connector.

7. Make sure you take a backup of your Logic Apps before making any changes, always make necessary changes on your Dev/Test environment and validate the changes before moving them to Production/Live environments.

For the code below, we rely on the path parameter of the action/trigger to see if it uses "datasets/default/" for V1 or if it uses "v2/datasets/" for V2 actions. You can test either of these to check if the script is working for you.

You can search in a Subscription scope or you can specify a Resource Group to search. Uncomment the part for the ResourceGroupName to allow the script to search only that Resource Group.

Disclaimer: The code below is provided as is with no liability on the author, please review and use at your own risk.

# Replace with your Azure subscription and resource group details
$subscriptionId = "YOUR SUBSCRIPTION ID"
$resourceGroupName = "YOUR RESOURCE GROUP"
$pathToFind = "datasets/default/"
#$pathToFind = "v2/datasets/"

#Set the specific subscription scope
$null = Set-AzContext -Subscription "$subscriptionId"

# Get all Logic Apps - uncomment last part to check specific Resource Group
$logicApps = Get-AzResource -ResourceType "Microsoft.Logic/workflows" #-ResourceGroupName $resourceGroupName

# Iterate through each Logic App
foreach ($app in $logicApps) {
    $appName = $app.Name
    #Write-Host "Checking Logic App: $appName"

    # Get the Logic App definition (code view)
    $appDefinition = Get-AzResource -ResourceId $app.ResourceId -ExpandProperties

    # Get the actions list
    $actions = $appDefinition.Properties.definition.actions
    #Write-Host "Actions: $actions"
  
    # Iterate the list
    $null = $actions.PSObject.Properties | ForEach-Object {
       $_.Name
       $actionType = $_.Value.type

       $ActionName = $_.Name
       #Write-Host "ActionType: $actionType"

       # Check the Action Type then check the path if it contains the substring
       if($actionType -eq "ApiConnection") {
	     # Check the path parameter for your specific value
	     $path = $_.Value.inputs.path   
	     #Write-Host "Path: $path"
	     #Write-Host "ActionName: $ActionName"
	     if($path -like "*$pathToFind*") {
	            Write-Host "Found Action: [$ActionName] - with the specific value: [$pathToFind] -  for Logic App: [$appName]"}
       }
    }

    # Get the triggers list
    $triggers = $appDefinition.Properties.definition.triggers
    #Write-Host "Triggers: $triggers"

    # Iterate the list
    $null = $triggers.PSObject.Properties | ForEach-Object {
       $_.Name
       $triggerType = $_.Value.type

       $triggerName = $_.Name
       #Write-Host "TriggerType: $triggerType"

       # Check the Trigger Type then check the path if it contains the substring
       if($triggerType -eq "ApiConnection") {
	     # Check the path parameter for your specific value
	     $pathTrigger = $_.Value.inputs.path   
	     #Write-Host "Path: $pathTrigger"
	     #Write-Host "TriggerName: $triggerName"
	     if($pathTrigger -like "*$pathToFind*") {
	            Write-Host "Found Trigger: [$triggerName] - with the specific value: [$pathToFind] -  for Logic App: [$appName]"}
        }
     }
}

Another option is to run the below script in Azure Resource Graph Explorer then explore the properties of each result to see if the action/trigger is a SQL one or another kind:

In Azure Resource Graph Explorer:

Resources 
| where type == "microsoft.logic/workflows"
| where properties contains "/datasets/default/"
| project properties

References:

• SQL Server connector’s V1 actions and triggers will be retired on 31 March 2024 – transition to SQL ...
• SQL Server - Connectors | Microsoft Learn
• Listing Connectors by Logic App - Microsoft Community Hub
• PS-Scripts---Logic-Apps-Connectors/Local_ListLogicAppsConsumptionConnectors.ps1 at main · PedroAlmei...
• Use PowerShell Script to Manage Your API Connection of Logic App (Consumption) Resources - Microsoft...