Find Orphans / unused Web API connection and Gateway name using Azure Resource Graph
Published Jun 22 2021 11:08 AM 2,278 Views

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

 

 

 

 

 

 

Mohammed_Barqawi_0-1624176261155.png

 

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

 

More information 

 

 

 

Co-Authors
Version history
Last update:
‎Sep 23 2021 02:02 AM
Updated by: