This is just a log post of a script I put together. When setting up a plattform we are not always certain what hosts and handlers the customer wants. We usually set it up according to common requirements and best practices. In that case it's also interesting to see what hosts are indeed used and not used once the solution is deployed, so you know which ones can be removed if requested. This is a sql script for that. Although this information can be found out using the Administration Console these kind of reports are not easy to get out and there is no one view for it. It's much easier to access the database BizTalkMgmtDb directly for these things.
select h1.Name from adm_Host h1 where h1.Name not in(
select distinct h.Name as Host –, a.Name as Adapter, rp.nvcName as Port
from adm_host h
join adm_receivehandler rh on h.id = rh.HostId
join adm_receivelocation rl on rl.ReceiveHandlerId = rh.Id
join bts_receiveport rp on rp.nID = rl.ReceivePortId
join adm_adapter a on a.id = rh.AdapterId
UNION
select distinct h.Name as Host –, a.Name as Adapter, sp.nvcName as Port
from adm_Host h
join adm_SendHandler sh on h.Id = sh.HostId
join bts_sendport_transport spt on spt.nSendHandlerID = sh.Id
join bts_sendport sp on sp.nID = spt.nSendPortID
join adm_Adapter a on sh.AdapterId = a.Id
UNION
select distinct h.Name as Host –, '*Orchestration' as Adapter, o.nvcName as Port
from adm_Host h
join bts_orchestration o on h.Id = o.nAdminHostID
–UNION
–select distinct h.Name as Host, * –, '*Tracking' as Adapter, NULL as Port
–from adm_Host h
–where h.HostTracking = 1
)
HTH
/Johan
Blog Post by: Johan Hedberg