Monitoring SQL Jobs using BizTalk360 Custom Widgets

September 8, 2016

monitoring sql jobs with biztalk360

In the BizTalk360 8.0 release, a new feature dashboard was introduced. Dashboard integration is a useful capability to show the important activities in a single view. It can be utilized in the Operations and Analytic’s sections. Team members can take advantage by creating separate dashboards for different purposes. A global dashboard is created by default, it is shared to all the users in the BizTalk environment.  The dashboard framework is loaded with a few basic widgets like Suspended Services, Environment Artifacts Count, Messages activities, etc.

Custom Widgets

Users are also able to create custom widgets and associate them with a dashboard. Custom widgets allows integration with BizTalk360 own API’s, as well as third-party API’s (like Sales Force, Power BI and Azure, etc.) and database queries by using the Secure SQL Queries in BizTalk360.

Widget Size

When you pin the widget to the dashboard there is an option to edit the size of the custom widget.  Users can adjust the size of the widgets to align the content on the dashboard. Different sizes are available ranging from the smallest unit mini (100 * 100), small (200*100),  normal (200*200), wide (400*200),  large (400*400),  hero wide (600*400); in centimeters.

Monitoring SQL Jobs

In a real case scenario, monitoring the SQL jobs can be fairly tedious and it is important factor in monitoring the health of the environment. Multiple SQL jobs that are configured in environment like BizTalk Server Management Database jobs, purging jobs, Message Box and DTA instances jobs, etc.  We can create a custom widget to monitor the SQL jobs and pin it to the operational dashboard, so that the operational team are able to monitor all the activities in single view. On the operations dashboard, using Add Widget – Add Custom Widget to create a new custom widget, you will need to provide a name, description and the script for the widget.

BizTalk360 Custom Widget

Styles

The styles of BizTalk360 can be used in custom widgets by inspecting the element in the BizTalk360 Application.  Take advantage of the listed styles and controls while creating the custom widgets.

BizTalk360 Styles

WidgetScroll and table table-lists are styles of BizTalk360

<div id=”WidgetScroll” style=”top:30px;” data-bind=”addScrollBar: WidgetScroll, scrollCallback: ‘false'”>
<table class=”table table-lists”>
</tbody>
</table>
</div>

Custom Styles

There is possibility to write your own CSS styles

<style>
.widget-unknown-color { color:#ccc; }
</style>

Data Binding

Knockoutjs is used for the data binding. The below code snippet is used to bind data for each SQL job status

<!– ko foreach: sqlJobs –> <tr> <td data-bind=”text: name”>
</td>
<td data-bind=”css:cssEnum[lastRunOutcome]”>
<i class=”fa” data-bind=”css:iconEnum[lastRunOutcome]”></i>&nbsp;&nbsp;
<span data-bind=”text: monitorEnum[lastRunOutcome]”></span>
</td>
</tr>
<!– /ko –>

Placeholder

Placeholder is used to provide dynamic content and also protect sensitive information like username, password, etc. It can also be used to maintain repetitive data and allow us to maintain the script in clean structure. Create a placeholder “productionenvironment” and insert into the code. We also create different placeholders for Alarm and Monitor Group Name.
Biztalk PlaceHolder

API Documentation

API Documentation (Settings-> API Documentation) provides all the BizTalk360 REST services and its API methods in swagger. The users can utilize these REST API Methods to create BizTalk360 custom widgets.
API Documentation To get the status of  SQL jobs use the following BizTalk360 API Call (AlertService.svc/GetAlertMonitorSerializedConfig)

getSQLJob = function (callback) {
var url = ‘http://localhost/BizTalk360.Web.v8.0//Services.REST/AlertService.svc/GetAlertMonitorSerializedConfig’;
$.ajax({
dataType: “json”,
url: url,
type:”GET”,
username: “BizTalk360”,
password: “Pa$$wordAzure”,
data: { environmentId: ‘ca4ef22a-1564-4d05-af01-d079124fad09′,alarmName:’AEV Alarm’,monitorGroupType:’SqlServerInstance’,monitorGroupName:’BT360DEV02′,monitorName:’SqlJobs’ },
cache: false,
success: function (data) {
callback(data);
},
error: function (xhr, ajaxOptions, thrownError) { //Add these parameters to display the required response
alert(xhr.status);
alert(xhr.responseText);
},
});
};

Reuse the same script in staging environment by create another placeholder “stagingenvironment” and replace with “productionenvironment” in new widget.  You can see the similar widget in the dashboard.
SQLJobsList The code used for this custom widget is available here: Full Source Code 

Hopefully, this article will help you create your own custom widgets connecting to either BizTalk360 API‘s or other third-party API’s.

Check out our documentation on How to upgrade BizTalk360 to its latest version 8.0?

Author: Senthil Palanisamy

Senthil Palanisamy is the Technical Lead at BizTalk360 having 12 years of experience in Microsoft Technologies. Worked various products across domains like Health Care, Energy and Retail.

Get full control of your BizTalk Environments

Over 500 customers across 30+ countries depend on BizTalk360

Start your 14 days Free Trial

Back to Top