Used and unused hosts sql script

Listen with webReader
Published 22 December 11 08:34 PM | Johan Hedberg

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

Filed under: , ,

Comments

No Comments

This Blog

News

    Messenger

    Twitter Updates

      Follow me on twitter

      Visitors

      Feedburner Subscribers

      Locations of visitors to this page

      Disclaimer

      All material is provided AS IS voiding any thinkable or unthinkable effect it might have for any use whatsoever. There... is that clear enough ;)

      Pages

    Syndication