BizTalk360 – BRE Policies not in a deployed State

Posted: August 23, 2019  |  Categories: BizTalk SQL Uncategorized

This article shows how to use BizTalk360 to alert when there are no BRE policies in a deployed state.

Use Case

Sometimes after a deployment to BizTalk Server none of the Business rules engine policies are set to deployed. If this is not noticed many messages suspend. An alert was required in BizTalk 360 to warn if this situation occurs.

BizTalk360 Monitoring Solution

Sometime back I ask Sandro Pereira “How would you detect when there is no rules policy in a deployed state?”. He kindly wrote a powershell script for me and wrote this article about it. I gave this script to my co-worker to set up a powershell notification alarm in BizTalk360 but he came up with another solution.

Instead using a database query instead.

Simply this database query tells us if any policies don’t have a policy in a deployed state and the alarm fires.

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 COUNT(*) from ctetDeployed d
where nStatus = 0
or exists (select 1 from cteHist h where h.strName=d.strname and bDeployedInd=0)

Conclusion

This article shows that BizTalk360 is a flexible monitoring tool that can be set up to run custom SQL queries that can then be used an as alarms. This case here allows you to monitor the state of your BRE policies. Furthermore it is possible to use PowerShell scripts in similar way . You will have to wait for another post to see an example of how to do this.

BizTalk360
BizTalk Server

Over 500+ customers across
30+ countries depend on BizTalk360

Learn More
Serverless360
Azure

Manage and monitor serverless
components effortlessly

Learn More
Atomicscope
Business Users

Monitor your Business Activity in iPaaS
or Hybrid integration solutions

Learn More

Back to Top