Microsoft has announced that Version 1 Actions/Triggers of the SQL Connector for Logic Apps will be deprecated by the end of March 2024. In response, we have decided to develop an Azure Resource Graph Explorer query to identify which Logic Apps are still using these soon-to-be-deprecated actions and triggers.
We have crafted a specific query for use within the Azure Resource Graph Explorer to facilitate this process. To utilize this tool, navigate to the Azure portal and search for Resource Graph Explorer in the Azure Search Bar. Once there, copy and paste the following query into the query panel and execute it:
(resources
| where type == 'microsoft.logic/workflows'
| project name, location, resourceGroup, subscriptionId, createdTime=properties.createdTime, actions=properties.definition.actions
| mv-expand action=actions
| extend actionName = tostring(bag_keys(action)[0])
| extend actionDetails = todynamic(tostring(action[actionName]))
| mv-expand innerActionDetails = actionDetails.cases
| extend innerActionName = tostring(bag_keys(innerActionDetails)[0])
| where innerActionName != "" // Filter out empty inner action names
| where innerActionDetails contains "sql" // Filter for innerActionDetails containing "sql"
| where innerActionDetails contains "/datasets/default/" // Filter for innerActionDetails containing "sql"
| project name, location, resourceGroup, actionName, innerActionName, innerActionDetails, subscriptionId, createdTime
)
| union (
resources
| where type == 'microsoft.logic/workflows'
| project name, location, resourceGroup, subscriptionId, createdTime=properties.createdTime, actions=properties.definition.actions
| mv-expand action=actions
| extend actionName = tostring(bag_keys(action)[0])
| mv-expand actions
| extend innerActionName = tostring(bag_keys(actions)[0])
| where innerActionName != "" // Filter out empty inner action names
| where actions[innerActionName].type == "ApiConnection" // Filter for actions of type "ApiConnection"
| where tostring(actions[innerActionName].inputs) contains "/datasets/default/"
//| where tostring(actions[innerActionName].inputs) contains "/datasets/default"
| where tostring(actions[innerActionName].inputs.host.connection.name) contains "sql"
| project name, location, resourceGroup, actionName, innerActionName, actions, subscriptionId, createdTime
| summarize actions = makeset(actions), count() by name, innerActionName, location, resourceGroup, subscriptionId
)
| order by name, resourceGroup asc
This query identifies Logic Apps within your Azure environment linked to the Version 1 SQL Connector. It does so by filtering for specific action types and examining the connections used within those actions to determine if they correspond with the soon-to-be-deprecated version. The output will include details such as the name, location, resource group, and subscription ID of each affected Logic App, along with the actions that utilize the SQL Connector.
The query was designed to identify only Logic Apps using the SQL Connector version 1 actions and triggers but can be changed to find any other types. We do that by filtering actions where “/datasets/default/” is found inside the Logic Apps. And you might ask, why the “/datasets/default/“?
This is present in the path of the API Connection version 1 actions and triggers, which the SQL Connectors were part of. Be aware that it is common to find “/datasets/default/” inside other connectors.
The path of the version 2 actions and triggers of the SQL Connectors differs from the V1, as you can see: “/v2/datasets/“
Employing this query allows teams to proactively address potential disruptions by updating their Logic Apps to use the latest version of the SQL Connector before the deprecation deadline.
You can also explore the Microsoft approach here: Identify Logic Apps Consumption that are using deprecated V1 Actions and Triggers for SQL Connector (microsoft.com)
Download
THIS COMPONENT IS PROVIDED “AS IS” WITHOUT WARRANTY OF ANY KIND.
You can download the Logic Apps Consumption using SQL Connector V1 actions and triggers Kusto query from GitHub here:
Hope you find this helpful! So, if you liked the content or found it useful and want to help me write more, you can help us buy a Star Wars Lego for Sandro’s son!
