Today, I was trying to find if a JSON pipeline we had was being used on a given BizTalk system.
I found the SQL command from here: https://gallery.technet.microsoft.com/Show-BizTalk-Applications-8f73d753
and just modified it to add an additional condition to the where clause
and rppl.Name like ‘%json%’
Here is the full command:
SELECT app.nvcName AS [Application], ass.nvcName AS [Assembly], orc.nvcName AS [Orchestration], orc.nOrchestrationStatus AS [Status], orp.nvcName AS [Orchestration Port], pt.nvcName AS [Port Type], pto.nvcName AS [Port Type Operation], rp.nvcName AS [Receive Port], rl.Name AS [Receive Location], rppl.Name AS [Receive Pipeline], sp.nvcName AS [Send Port], sppl.Name AS [Send Pipeline], spg.nvcName AS [Send Port Group] FROM bts_application app WITH(NOLOCK) LEFT OUTER JOIN bts_assembly ass WITH(NOLOCK) ON app.nID = ass.nApplicationId LEFT OUTER JOIN bts_orchestration orc WITH(NOLOCK) ON ass.nID = orc.nAssemblyId LEFT OUTER JOIN bts_orchestration_port orp WITH(NOLOCK) ON orc.nID = orp.nOrchestrationId LEFT OUTER JOIN bts_porttype pt WITH(NOLOCK) ON orp.nPortTypeID = pt.nID LEFT OUTER JOIN bts_porttype_operation pto WITH(NOLOCK) ON pt.nID = pto.nPortTypeID LEFT OUTER JOIN bts_orchestration_port_binding opp WITH(NOLOCK) ON orp.nID = opp.nOrcPortID LEFT OUTER JOIN bts_receiveport rp WITH(NOLOCK) ON opp.nReceivePortID = rp.nID LEFT OUTER JOIN adm_ReceiveLocation rl WITH(NOLOCK) ON rp.nID = rl.receiveportId LEFT OUTER JOIN bts_pipeline rppl WITH(NOLOCK) ON rl.ReceivePipelineId = rppl.Id LEFT OUTER JOIN bts_sendport sp WITH(NOLOCK) ON opp.nSendPortID = sp.nID LEFT OUTER JOIN bts_pipeline sppl WITH(NOLOCK) ON sp.nSendPipelineID = sppl.Id LEFT OUTER JOIN bts_sendportgroup spg WITH(NOLOCK) ON opp.nSpgID = spg.nID WHERE orc.nvcName IS NOT NULL and rppl.Name like '%json%' ORDER BY 1,2,3
— 1 = Unenlisted
— 2 = Stopped
— 3 = Started
NOTE: – above only works with orchestrations
If you don’t have an orchestration involved, I have created another a pair of queries to find send and receive ports that use a given pipeline.