Find Orphans / unused Web API connection and Gateway name using Azure Resource Graph

[Mohammed_Barqawi]

Day by day the API connections will keep growing up

As clean up, I will show below how to use the Azure Resource Graph  to make query against the API and logic app to find all the unused API 

resources
| where type =~ 'Microsoft.Web/connections'
| extend GatewayName=iff(isnotempty(todynamic(properties)["parameterValues"]),todynamic(properties)["parameterValues"]["gateway"]["name"] ,todynamic(properties)["parameterValueSet"]["values"]["gateway"]["value"]["name"])
|project API_id=id ,API_Name=name,GatewayName,API_resourceGroup=resourceGroup
|join kind= leftouter
(
resources
| where type == 'microsoft.logic/workflows'
|extend propertiesJson=parse_json(properties)
|extend ConJson=propertiesJson["parameters"]["$connections"]["value"]
|mvexpand Conn=ConJson
|where   notnull(Conn)
|extend connectionId=extract("\"connectionId\":\"(.*?)\"",1,tostring(Conn))
|project connectionId,LogicApp_id=id,LogicApp_resourceGroup=resourceGroup,LogicApp_Name=name
)   on  $left.API_id==$right.connectionId
|project API_Name ,API_resourceGroup,GatewayName,Used_in_LogicApp =iff(isnotempty(LogicApp_id), "Yes","No")  ,LogicApp_resourceGroup,LogicApp_Name

the query above join 'microsoft.logic/workflows' with 'Microsoft.Web/connections' and find all the unused connections 

More information 

• Learn more about Kusto link
• Azure Resource Graph Deep Dive - YouTube
• mv-expand operator - Azure Data Explorer | Microsoft Docs
• todynamic(), parse_json() functions - Azure Data Explorer | Microsoft Docs