CODEWITHSUNDEEP
powershellscriptingbiztalk
medinibster
medinibster

Reputation: 57

How to get list of Biztalk applications from another server remotely

i have the below code to get the Biztalk applications in ServerB. works fine if i run it in Server B. How can i run the same script from ServerA and get the Biztalk applications from ServerB. Invoke-command remoting doesnt seem to work.

# Get local BizTalk DBName and DB Server from WMI
$btsSettings = get-wmiobject MSBTS_GroupSetting -namespace 'root\MicrosoftBizTalkServer'
$dbInstance = $btsSettings.MgmtDbServerName
$dbName = $btsSettings.MgmtDbName

# Load BizTalk ExplorerOM
[void] [System.reflection.Assembly]::LoadWithPartialName("Microsoft.BizTalk.ExplorerOM")
$BizTalkOM = New-Object Microsoft.BizTalk.ExplorerOM.BtsCatalogExplorer
$BizTalkOM.ConnectionString = "SERVER=$dbInstance;DATABASE=$dbName;Integrated Security=SSPI"

$apps = $BizTalkOM.Applications | Select-Object Name,Status | Where-Object {$_.name -in 'BT.Order.CustomerOrder','BT.Order.FControl'}
$apps

Upvotes: 2

Views: 1894

Answers (3)

EvertWS
EvertWS

Reputation: 1

Remoting should work. Are you sure you don't run into the "double hop" issue? You will if your SQL Server is on another machine than ServerB (you will get a login failed for an anonymous logon instead of the user account you are working with). Look at https://technet.microsoft.com/en-us/library/hh849872.aspx to make sure you delegate your credentials to the SQL server. You should allow your client to sent the credentials and allow the server to receive the credentials. By adding your credentials to the Invoke-Command, it should work.

Upvotes: 0

Dan Field
Dan Field

Reputation: 21641

If you don't have the BizTalk WMI/ExplorerOM installed or can't (think about it - you're trying to explicitly load the Microsoft.BizTalk.ExplorerOM dll in there), you could get by with just an Invoke-Sqlcmd - but note that this is more fragile than using the OM, could break with new versions, should be used with extreme caution in production, etc... It also might not work if there are dynamic send ports at play (I don't have any deployed currently to test with). 

$sql = "SELECT app.nvcName as Name,
    CASE
        WHEN SUM(o.nOrchestrationStatus) IS NULL AND SUM(r.Disabled) IS NULL AND SUM(sprt.nPortStatus) IS NULL  THEN 'N/A'
        WHEN SUM(CASE WHEN o.nOrchestrationStatus IS NULL THEN 3 ELSE o.nOrchestrationStatus END) = COUNT(*) * 3 AND SUM(CASE WHEN r.Disabled IS NULL THEN 0 ELSE r.Disabled END) = 0          AND sum(CASE WHEN sprt.nPortStatus IS NULL THEN 3 ELSE sprt.nPortStatus END) = count(sprt.nPortStatus) * 3  THEN 'Started' 
        WHEN SUM(CASE WHEN o.nOrchestrationStatus IS NULL THEN 1 ELSE o.nOrchestrationStatus END) = count(*)     AND SUM(CASE WHEN r.Disabled IS NULL THEN -1 ELSE r.Disabled END) = -COUNT(*) AND sum(CASE WHEN sprt.nPortStatus IS NULL THEN 1 ELSE sprt.nPortStatus END) = count(*) THEN 'Stopped'       
        ELSE 'Partially Started' 
    END as Status
FROM bts_application app WITH(NOLOCK)
LEFT JOIN (
    SELECT asm.nApplicationID, orch.nvcName, orch.nOrchestrationStatus FROM bts_assembly asm WITH(NOLOCK)
    inner join bts_orchestration orch WITH(NOLOCK)
    on asm.nid = orch.nAssemblyID) o
ON o.nApplicationID = app.nID

LEFT JOIN bts_sendport sprt with(nolock)
ON sprt.nApplicationID = app.nID

LEFT JOIN (
    select rprt.nApplicationID, rprt.nvcName as rprtName, rloc.Name as rlocName, rloc.Disabled from bts_receiveport rprt with(nolock)
    INNER JOIN  adm_ReceiveLocation rloc with(nolock)
    on rprt.nID = rloc.ReceivePortId) r
on r.nApplicationID = app.nid

--WHERE app.nvcName IN ('BT.Order.CustomerOrder','BT.Order.FControl')
GROUP BY app.nvcName"

Invoke-Sqlcmd -ServerInstance 'localhost' -Database 'BizTalkMgmtDb' -Query $sql | Where-Object { $_.Name -in 'BT.Order.CustomerOrder','BT.Order.FControl'}

Note that you could limit the results at the SQL level as well by uncommenting the WHERE clause in the sql string.

And note that this query can be vastly simplified if you really just want applications and don't care about their statuses - it'd just be something like Select nvcName from bts_application.

Upvotes: 2

DTRT
DTRT

Reputation: 11040

To run that script on any computer, the BizTalk WMI components must be installed. The WMI components are installed with the BizTalk Server Administration Tools so you would have at least install those.

Then, just change the connection string to point to whatever Management Database you need.

Upvotes: 1

Related Questions