Find Orphans / unused Web API connection using Azure Resource Graph

Published Jun 22 2021 11:08 AM 1,167 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'
|project id
|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
)   on  $left.id==$right.connectionId

 

 

 

 

 

Mohammed_Barqawi_0-1624176261155.png

 

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

 

More information 

 

 

 

%3CLINGO-SUB%20id%3D%22lingo-sub-2465200%22%20slang%3D%22en-US%22%3EFind%20Orphans%20%2F%20unused%20Web%20API%20connection%20using%20Azure%20Resource%20Graph%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-2465200%22%20slang%3D%22en-US%22%3E%3CP%3EDay%20by%20day%20the%20API%20connections%20will%20keep%20growing%20up%3C%2FP%3E%0A%3CP%3EAs%20clean%20up%2C%20I%20will%20show%20below%20how%20to%20use%20the%26nbsp%3B%3CA%20href%3D%22https%3A%2F%2Fdocs.microsoft.com%2Fen-us%2Fazure%2Fgovernance%2Fresource-graph%2Foverview%22%20target%3D%22_self%22%20rel%3D%22noopener%20noreferrer%22%3EAzure%20Resource%20Graph%26nbsp%3B%3C%2FA%3E%20to%20make%20query%20against%20the%20API%20and%20logic%20app%20to%20find%20all%20the%20unused%20API%26nbsp%3B%3C%2FP%3E%0A%3CP%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%3E%26nbsp%3B%3C%2FP%3E%0A%3CPRE%20class%3D%22lia-code-sample%20language-sql%22%3E%3CCODE%3Eresources%0A%7C%20where%20type%20%3D~%20'Microsoft.Web%2Fconnections'%0A%7Cproject%20id%0A%7Cjoin%20kind%3D%20leftouter%0A(%0Aresources%0A%7C%20where%20type%20%3D%3D%20'microsoft.logic%2Fworkflows'%0A%7Cextend%20propertiesJson%3Dparse_json(properties)%0A%7Cextend%20ConJson%3DpropertiesJson%5B%22parameters%22%5D%5B%22%24connections%22%5D%5B%22value%22%5D%0A%7Cmvexpand%20Conn%3DConJson%0A%7Cwhere%20%20%20notnull(Conn)%0A%7Cextend%20connectionId%3Dextract(%22%5C%22connectionId%5C%22%3A%5C%22(.*)%5C%22%22%2C1%2Ctostring(Conn))%0A%7Cproject%20connectionId%0A)%20%20%20on%20%20%24left.id%3D%3D%24right.connectionId%0A%3C%2FCODE%3E%3C%2FPRE%3E%0A%3CP%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%3E%3CSPAN%20class%3D%22lia-inline-image-display-wrapper%20lia-image-align-inline%22%20image-alt%3D%22Mohammed_Barqawi_0-1624176261155.png%22%20style%3D%22width%3A%20999px%3B%22%3E%3CIMG%20src%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fimage%2Fserverpage%2Fimage-id%2F290117i9D6B99A70840ADD3%2Fimage-size%2Flarge%3Fv%3Dv2%26amp%3Bpx%3D999%22%20role%3D%22button%22%20title%3D%22Mohammed_Barqawi_0-1624176261155.png%22%20alt%3D%22Mohammed_Barqawi_0-1624176261155.png%22%20%2F%3E%3C%2FSPAN%3E%3C%2FP%3E%0A%3CP%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%3Ethe%20query%20above%20join%26nbsp%3B'microsoft.logic%2Fworkflows'%20with%26nbsp%3B'Microsoft.Web%2Fconnections'%20and%20find%20all%20the%20unused%20connections%26nbsp%3B%3C%2FP%3E%0A%3CP%3E%26nbsp%3B%3C%2FP%3E%0A%3CH2%20id%3D%22toc-hId--437383247%22%20id%3D%22toc-hId--436603651%22%3EMore%20information%26nbsp%3B%3C%2FH2%3E%0A%3CUL%3E%0A%3CLI%3ELearn%20more%20about%20Kusto%20%3CA%20href%3D%22https%3A%2F%2Fdocs.microsoft.com%2Fen-us%2Fazure%2Fdata-explorer%2Fkusto%2Fquery%2F%22%20target%3D%22_self%22%20rel%3D%22noopener%20noreferrer%22%3Elink%3C%2FA%3E%3C%2FLI%3E%0A%3CLI%3E%3CA%20href%3D%22https%3A%2F%2Fwww.youtube.com%2Fwatch%3Fv%3DgkOh4MjhxIs%22%20target%3D%22_blank%22%20rel%3D%22noopener%20nofollow%20noreferrer%22%3EAzure%20Resource%20Graph%20Deep%20Dive%20-%20YouTube%3C%2FA%3E%3C%2FLI%3E%0A%3CLI%3E%3CA%20href%3D%22https%3A%2F%2Fdocs.microsoft.com%2Fen-us%2Fazure%2Fdata-explorer%2Fkusto%2Fquery%2Fmvexpandoperator%22%20target%3D%22_blank%22%20rel%3D%22noopener%20noreferrer%22%3Emv-expand%20operator%20-%20Azure%20Data%20Explorer%20%7C%20Microsoft%20Docs%3C%2FA%3E%3C%2FLI%3E%0A%3CLI%3E%3CA%20href%3D%22https%3A%2F%2Fdocs.microsoft.com%2Fen-us%2Fazure%2Fdata-explorer%2Fkusto%2Fquery%2Fparsejsonfunction%22%20target%3D%22_blank%22%20rel%3D%22noopener%20noreferrer%22%3Etodynamic()%2C%20parse_json()%20functions%20-%20Azure%20Data%20Explorer%20%7C%20Microsoft%20Docs%3C%2FA%3E%3C%2FLI%3E%0A%3C%2FUL%3E%0A%3CP%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%3E%26nbsp%3B%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-TEASER%20id%3D%22lingo-teaser-2465200%22%20slang%3D%22en-US%22%3E%3CP%3E%3CSPAN%20class%3D%22lia-inline-image-display-wrapper%20lia-image-align-inline%22%20image-alt%3D%22path44.png%22%20style%3D%22width%3A%20450px%3B%22%3E%3CIMG%20src%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fimage%2Fserverpage%2Fimage-id%2F290114iB3165922A6B47B72%2Fimage-size%2Flarge%3Fv%3Dv2%26amp%3Bpx%3D999%22%20role%3D%22button%22%20title%3D%22path44.png%22%20alt%3D%22path44.png%22%20%2F%3E%3C%2FSPAN%3E%3C%2FP%3E%0A%3CP%3EUnleash%20the%20power%20of%20Azure%20Resource%20Graph%20to%20query%20API%20and%20Logic%20app%26nbsp%3B%3C%2FP%3E%3C%2FLINGO-TEASER%3E%3CLINGO-LABS%20id%3D%22lingo-labs-2465200%22%20slang%3D%22en-US%22%3E%3CLINGO-LABEL%3ELogic%20Apps%3C%2FLINGO-LABEL%3E%3C%2FLINGO-LABS%3E
Co-Authors
Version history
Last update:
‎Jun 20 2021 03:04 AM
Updated by: