Determine Ready to Run / Active Service Instance Details with Custom Widgets

by Saranya Ramakrishnan | Mar 5, 2019 | BizTalk Community Blogs via Syndication

When messages flow into the BizTalk Server, the messages may get persisted in BizTalk Server’s MessageBox database. For a healthy BizTalk environment, it’s important to keep an eye on the number of service instances in the environment.For example, having a large number of suspended service instances will bloat your message box database and adversely affect the overall performance of your environment.

Administrator should always  keep an eye on the service instances count via the BizTalk Administrator group hub page. The person who is monitoring this, need to be a BizTalk expert and understand the importance of each state. The group hub page only displays the instance count and it won’t tell you whether this is still at a healthy level. Whereas with BizTalk360 you can set the Warning and Error threshold levels (instance counts) for each states at application level. Once the number of instances count increases above the threshold, the system will send notification alert.

Also, the administrator can set up an alarm like “If there are >20 Suspended Service Instances between 09:00 AM and 05:00 PM, resume all the instances”. He can simply log in to the BizTalk360 Data Monitoring Dashboard to see the status of the message box data for the day. He can also set up email notifications for the alarm. By doing so, the administrator eliminates the need to often log in to the BizTalk360 application and check for the status of the service instances.

Custom widget to list all service instance details which are active for a long period

Custom Widget is one of the interesting and powerful features available in BizTalk360. With a custom widget, users can easily integrate third-party portals like Power BI, Sales Force or internal portals. You can also easily display Secure SQL Queries query results , monitor BizTalk Artefact statuses etc.

For instance, if a host instance is too busy to process all its associated service instances , then those instances will be in “Ready to Run” state until the host instance has available resources. When this situation remains for a longer timeframe, the service instances are going to get accumulated, thereby bloating the message box.

With the below script you can quickly create a custom widget to look up the number of service instances which are in the active state for a particular period of time. Say, the administrator can easily check service instance details which are in the Ready to Run or Active state for more than 15 minutes.

Creating such a widget, consists of the following steps:

1. Create a Secure SQL Query
2. Bind the SQL Query result to the custom widget

Both steps are described below.

1) Create Secure SQL Query

The below query retrieves the Service Instances which are in the Active state for more than 30 minutes.

DECLARE @dt DATETIME = ( DATEADD(MINUTES,-30,GETUTCDATE()))

exec ops_OperateOnInstances @snOperation=0 ,@fMultiMessagebox=0 ,@uidInstanceID='00000000-0000-0000-0000-000000000000', @nvcApplication=N'', @snApplicationOperator=0, @nvcHost=N'' ,@snHostOperator=0,@nServiceClass=111,@snServiceClassOperator=0,@uidServiceType='00000000-0000-0000-0000-000000000000', @snServiceTypeOperator=0, @nStatus=2, @snStatusOperator=1 ,@nPendingOperation=1 ,@snPendingOperationOperator=0,@dtPendingOperationTimeFrom='1753-01-01 00:00:00', @dtPendingOperationTimeUntil='9999-12-31 23:59:59.997', @dtStartFrom='1753-01-01 00:00:00', @dtStartUntil=@dt ,@nvcErrorCode=N'',  @snErrorCodeOperator=0 ,@nvcErrorDescription=N'' ,@snErrorDescriptionOperator=0,@nvcURI=N'',@snURIOperator=0,@dtStartSuspend='1753-01-01 00:00:00', @dtEndSuspend='9999-12-31 23:59:59.997', @nvcAdapter=N'', @snAdapterOperator=0, @nGroupingCriteria=0, @nGroupingMinCount=0,@nMaxMatches=10,@uidAccessorID='*******',@nIsMasterMsgBox=0;

2)Bind the SQL Query result to a custom widget 

You can create the custom widget and use below code. Don’t forget to include your environment details like the credentials of the BizTalk360 service account, etc.

<div id="WidgetScroll" style="top:30px;" data-bind="addScrollBar: WidgetScroll, scrollCallback: 'false'">
<table class="table table-lists">
<thead>
<tr>
<th style="width:30%">Application Name</th>
<th style="width:30%">Instance Id </th>
<th style="width:30%"> Service ID</th>
<th style="width:30%">Created Date</th>
<th style="width:30%">State</th>
</tr>
</thead>
<tbody>
<!-- ko if: (ServiceInstanceDetails()) -->
<!-- ko foreach: ServiceInstanceDetails() -->
<tr>
<td data-bind="text: nvcName"></td>
<td data-bind="text: uidInstanceID"></td>
<td data-bind="text: uidServiceID"></td>
<td data-bind="text: dtCreated"></td>
<td data-bind="text: nState"></td>
</tr>
<!-- /ko -->
<!-- /ko -->
</tbody>
</table>
</div>
<script>
// BEGIN User variables
username = ""; // BizTalk360 service account
password = ""; // Password of BizTalk360 service account
environmentId = ""; // BizTalk360 Environment ID (take from SSMS or API Documentation)
queryId = ""; // Id of the Secure SQL Query (take from SSMS)
queryName = ""; // Name of the Secure SQL Query as it is stored under Operations/Secure SQL Query
sqlInstance = ""; // SQL Instance against which the SQL Query must be executed
database = ""; // Database against which the SQL Query must be executed
sqlQuery = " " // The Secure SQL Query created in step1
bt360server = ""; // Name of the Server where biztalk360 is hosted
// END User variables

url = 'http://' + bt360server + '/BizTalk360/Services.REST/BizTalkGroupService.svc/ExecuteCustomSQLQuery';
ServiceInstanceDetails = ko.observable();

x2js = new X2JS({ attributePrefix: '', arrayAccessForm: "property", arrayAccessFormPaths: ["root.records.record"] });

ServiceInstanceDetailsList = function () {
var _this = this;
_this.getServiceInstanceDetails(function (data) {

var results = x2js.xml_str2json(data.queryResult);
if (Array.isArray(results.root.records.record)){
ko.utils.arrayForEach(results.root.records.record,function(item){

switch (item.nState){
case "1":
item.nState="Ready to Run";
break;
case "2":
item.nState="Active";
break;
}
});
_this.ServiceInstanceDetails(results.root.records.record);
}
else {
_this.ServiceInstanceDetails([results.root.records.record]);
}
});
};
getServiceInstanceDetails = function (callback) {
var _this = this;
$.ajax({
dataType: "json",
url: _this.url,
type: "POST",
contentType: "application/json",
username: _this.username,
password: _this.password,
data:
'{"context":{"environmentSettings":{"id":"' +
_this.environmentId +
'","licenseEdition":0},"callerReference":"REST-SAMPLE"},"query":{"id":"' +
_this.queryId +
'","name":"' +
_this.queryName +
'","sqlInstance":"' +
_this.sqlInstance +
'","database":"' +
_this.database +
'","sqlQuery":"' +
_this.sqlQuery +
'","isGlobal":false}}',
cache: false,
success: function (data) {
callback(data);
},
error: function (xhr, ajaxOptions, thrownError) {
alert(xhr.status);
alert(xhr.responseText);
},
});
};
ServiceInstanceDetailsList();
</script>

After you have created the custom widget and properly provided your environment details, it will look similar to the picture below.

We have written multiple articles about the capabilities of custom widgets, both in our blog, but also in the Documentation portal. You can check them out below:

Conclusion

It has been extremely beneficial to ensure the environment is healthy. With this custom widget you can easily get a clear insight about long running service instance details in a single view. If you have a particular scenario in which custom widgets could be useful, but you don’t know how to set this up, feel free to contact us at support@biztalk360.com.