BizTalk DevOps: Monitor your BizTalk environment using PowerShell – Monitoring BRE Policies Pending to be Deployed

by Sandro Pereira | Nov 13, 2018 | BizTalk Community Blogs via Syndication

After a tweet exchange with Mark Brimble a fellow “BizTalker” that I respect and admire, I realized that I had developed a PowerShell script some time ago to monitoring BRE Policies that could help him solve his problem. The initial question or need he was facing was:

• How would you detect when there is no rules policy in a deployed state? I don’t know how many times I import a rule an forget to set it to deployed…

This is a common problem, and to be honest, I sometimes forget what is the correct state of a policy: Deployed or Published. (the correct answer is Deployed). And unfortunately, there isn’t a simple solution to solve this need and the solutions that I found was:

• Using BizTalkFactory PowerShell Provider that nowadays come with BizTalk Server (“SDKUtilitiesPowerShell” folder)
• Create my own monitor script – more work involved

Using BizTalkFactory PowerShell Provider is quite simple, but it has some limitations for what I would like to archive, for example, it only shows the policies that are bound to a particular BizTalk Application.

And I would like to know and have visibility to all of them because you don’t need to bind a policy to a BizTalk Application on the BizTalk Administration Console to use that policy.

And for that reason, I decide to create my own monitor script that I can easily change and optimize for my scenarios.

The problem I faced in the past was in fact quite similar to what Mark Brimble was describing, maybe with some small differences but the end goal is the same, so I decide to help him (at least try) and publish this PowerShell script.

The purpose of this PowerShell script is to:

• Monitor BRE Policies and check if the highest version of a given policy is on Deployed state
  • If not notify someone (your BizTalk administration team);

So how can PowerShell help us?

With this script, you can be able to monitor your BizTalk Server BRE Policies, highest versions, that aren’t in the deployed state. Only if the script finds any non-compliance, an email notification will be sent.

Taking this sample:

The result will be a notification that includes two warnings:

• Policy1 is in a non-compliance state because version 1.2 is not deployed
• Policy2 is in a compliance state
• Policy3 is in a non-compliance state because version 1.2 is neither published nor deployed (this is optional, but I choose to include in my monitoring script)

This script is a combination of a PowerShell script and a SQL Server Script that allows you to set:

• Set your email notification settings:

#Set mail variables
[STRING]$PSEmailServer = "mySMTPServer" #SMTP Server.
[STRING]$SubjectPrefix = "MBV Notification Report -  "
[STRING]$From = "[email protected]"
[array]$EmailTo = ("[email protected]")

• And configure a SQL Server script, that in fact were the magic happens. The SQL Script will have the ability to check what rules are in a non-compliance state:

/****** Sandro Pereira & José Barbosa - DevScope  ******/
;with 
cteHist as (
        select h.* from [BizTalkRuleEngineDb].[dbo].[re_deployment_history] h
join (select strname, max(dttimestamp) as dttimestamp from [BizTalkRuleEngineDb].[dbo].[re_deployment_history] group by strname) q on h.strName=q.strName and h.dtTimeStamp=q.dttimestamp
),
ctetDeployed as (
        SELECT StrName, nMajor, nMinor, nStatus
                                                FROM   (
                                                   SELECT StrName, nMajor, nMinor, nStatus
                                                                , row_number() OVER(PARTITION BY StrName ORDER BY nMajor, nMinor DESC) AS rn
                                                   FROM   [BizTalkRuleEngineDb].[dbo].[re_ruleset]
                                                   ) sub
                                                WHERE  rn = 1
)
select * from ctetDeployed d
where nStatus = 0
or exists (select 1 from cteHist h  where h.strName=d.strname and bDeployedInd=0)

The following Windows PowerShell script is a fragment that will help us demonstrate the monitoring capabilities:

$mydata = invoke-sqlcmd -inputfile $sqlQuery -serverinstance $server

Foreach ($log in $mydata)
{
    #Create mail body content
    $mailBody += "..."
}

Here is example expected report output from running the Windows PowerShell script sample, if any of the BRE Policies are in an unwanted state.

Note: This type of script must be viewed as a complement to the tools mentioned above or used in the absence of them. The script should also be adjusted to your needs.

THIS POWERSHELL & SQL SCRIPT ARE PROVIDED “AS IS”, WITHOUT WARRANTY OF ANY KIND.

Special thanks to my coworker José Barbosa for helping me optimize the SQL Server script!

The script can be found and download on Microsoft TechNet Gallery:
Monitoring BRE Policies in your BizTalk environment with PowerShell (18.0 KB)
Microsoft TechNet Gallery

Author: Sandro Pereira

Sandro Pereira lives in Portugal and works as a consultant at DevScope. In the past years, he has been working on implementing Integration scenarios both on-premises and cloud for various clients, each with different scenarios from a technical point of view, size, and criticality, using Microsoft Azure, Microsoft BizTalk Server and different technologies like AS2, EDI, RosettaNet, SAP, TIBCO etc. He is a regular blogger, international speaker, and technical reviewer of several BizTalk books all focused on Integration. He is also the author of the book “BizTalk Mapping Patterns & Best Practices”. He has been awarded MVP since 2011 for his contributions to the integration community. View all posts by Sandro Pereira