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.
