This post was originally published here
A few days ago, Luis Rigueira created a Kusto query to identify all Logic Apps Consumptions that use SQL V1 actions and triggers that will soon be deprecated (end of March 2024). The query also tries to identify all actions that are using those actions/triggers. This query actually works decently if a Logic App has actions or triggers in the first level (not inside if, switch, scope’s, and do on), but if we have Logic App with nested actions, which is quite common, then that query tries as a best effort to identify those actions – it will identify all Logic Apps, but it will not provide the name of the actions. The reason why is because it is quite difficult to loop to all the logic app definitions (JSON) with a Kusto query.
Don’t get me wrong. That query is awesome for you to identify all Logic Apps that we need to address to fix those actions. Now, if we need to identify all actions with that “problem” to estimate the work involved better, then that Kusto query will not be the best option. To solve this problem, we decided to create a PowerShell script that not only identifies all Logic Apps Consumption using SQL V1 actions and triggers but also identifies the names of those actions and triggers for you, providing a good report that you can use to plan and estimate your work.
# Function to extract actions recursively
function Get-ActionsAndTriggers {
param (
$node
)
$actionsAndTriggers = @()
foreach ($key in $node.psobject.Properties.Name) {
if ($node.$key.type -eq "ApiConnection") {
if ($node.$key.inputs.path -like "*/datasets/default*" -and $node.$key.inputs.host.connection.name -like "*sql*") {
$actionsAndTriggers += $key
}
} elseif ($node.$key -is [System.Management.Automation.PSCustomObject]) {
$actionsAndTriggers += Get-ActionsAndTriggers -node $node.$key
}
}
return $actionsAndTriggers
}
# Retrieve all Logic Apps within the subscription
$logicApps = Get-AzResource -ResourceType Microsoft.Logic/workflows
# Iterate through each Logic App and extract actions and triggers
foreach ($logicApp in $logicApps) {
# Retrieve Logic App definition
$logicAppDefinition = Get-AzResource -ResourceId $logicApp.ResourceId -ExpandProperties
# Extract actions and triggers from the Logic App definition
$allActionsAndTriggers = Get-ActionsAndTriggers -node $logicAppDefinition.Properties.Definition.triggers
$allActionsAndTriggers += Get-ActionsAndTriggers -node $logicAppDefinition.Properties.Definition.actions
# Display the Logic App name if filtered actions and triggers were found
if ($allActionsAndTriggers.Count -gt 0) {
Write-Host "Logic App: $($logicApp.Name) - RG: $($logicApp.ResourceGroupName)" -ForegroundColor Red
# Display the list of filtered actions and triggers
Write-Host "Filtered Actions and Triggers:"
$allActionsAndTriggers
Write-Host ""
}
}
The great thing about this script is that it also identifies all nested actions (actions inside other actions like If, Scopes, Switch, and so on)
If you are wondering, can we do the same and identify all using SQL V2 actions and triggers? Don’t worry, we have your back covered, check the download section. In fact, with small changes to this script you can use for all types of connectors.
Download
THESE COMPONENTS ARE PROVIDED “AS IS” WITHOUT WARRANTY OF ANY KIND.
You can download the PowerShell script to identify all SQL V1 actions and triggers inside Logic Apps Consumption from GitHub here:
If you want to identify SQL V2 actions and triggers, then download this script from GitHub:
Huge thanks to Luis Rigueira for working with me on these scripts.
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 my son!
