Secure SQL Query Reports

Secure SQL Query Reports

At the moment we are working on release v9.0.2 of BizTalk360. As always, we have many exciting features and enhancements coming up with this release.

One of the main sections in BizTalk360 is the Analytics section. BizTalk360 Analytics offers a visual display of the most important performance counters that are consolidated and arranged on a single screen so that the information can be monitored in a glance. In addition to that, we have the Reporting feature in BizTalk360 where users are able to generate PDF documents of critical performance metrics and send emails to the user at specific time periods, depending on the requirement. This feature is available only to the platinum licensed BizTalk360 environments.

In the new version v9.0.2 of BizTalk360, you can add Secure SQL Queries which can be mapped to a schedule to get the SQL query output as a report.

Note: BizTalk360 consists of 6 default queries which the user can only execute. In addition to that, the user can create new custom SQL queries as per their business need.

Secure SQL Reports in Analytics Reporting

Scenario 1:

Consider the scenario where you have a list of messages that ware not received during the past few hours/days and you want to know the details of these unprocessed messages. Usually you will be checking this by executing the query manually, however, this can be difficult on your busy schedule as you need to switch between different tools. Luckily, this can now be done simply with the new “SQL Reports” feature under Reporting. Just create a query, map it to the schedule according to your needs and then you will start receiving the query output as a report.

Scenario 2:

Consider another scenario: in BizTalk360, you have many users using the application. It’s important that an administrator should be aware of who did what. This can be seen in the Governance and Auditing section manually, but you might need to filter the records every time. Instead of filtering the records manually each time, you can create a Custom SQL Query and map the query to the Reporting Schedule which will send a periodic report to the configured admin email address. With this, the admin can easily get a report with regards to the actions performed by the other users. You can schedule this report and get the query output as a PDF document in the Analytics Reporting.

User permissions required to Process the Secure SQL Query Reports in BizTalk360

By default, the Superuser will have access to all the queries and also to the BizTalk reporting feature.

In case of a normal user, as a first step of processing the Secure SQL Query Reports, the user should ensure that the below permissions are provided.

The user should have permissions to:

  • BizTalk Reporting
  • Secure SQL Queries

Note: whenever a new query is being created, the user should ensure that, the normal user will be provided access to the newly created query (BizTalk360-> Settings-> User Access Policy-> Manage Users-> Secure SQL Queries-> Advanced).

Once all permissions set, the next step is to create a Reporting Schedule.



From the schedule configuration screen, you can define when you want to generate a report. The available options are Daily, Weekly and Monthly. Say, for instance, if the user wants to get a report on a daily basis, the user can choose Daily and set the time as per their need. This way the user will get the report on the scheduled time on a daily

Once you have created a schedule, the next step is creating a report and map that report to the reporting schedule.


Once all is done, add a SQL Query widget by clicking ‘Secure SQL Query Results’  in the right panel.


When you click on ‘Secure SQL Query Results’, it opens the blade where you can enter the widget title and select the query from the list (List of Saved queries created in BizTalk360-> Operation-> Data Access -> Secure SQL Queries section).


Once you click on ‘Save’, the widget will be added in a reporting dashboard.

When the widget is added to the dashboard, it gets added with the maximum width and height. In the case of having a bigger number of records, space might not be enough to view all the records. For that, we have provided the option to customize the widget. You can expand the widget as much as you want. For now, we are allowing a maximum of 100 records (top 100 records) in the widget.



When you click on the ‘PDF’ button, the downloaded PDF document will look like below. The same will be sent to the configured email ids.



SQL Reports makes your work easier, you will get in time report summaries of what you want to know about your BizTalk environment.

The post Secure SQL Query Reports appeared first on BizTalk360.

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

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

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.


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">
<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>
<!-- ko if: (ServiceInstanceDetails()) -->
<!-- ko foreach: ServiceInstanceDetails() -->
<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>
<!-- /ko -->
<!-- /ko -->
// 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)){

switch (item.nState){
case "1":
item.nState="Ready to Run";
case "2":
else {
getServiceInstanceDetails = function (callback) {
var _this = this;
dataType: "json",
url: _this.url,
type: "POST",
contentType: "application/json",
username: _this.username,
password: _this.password,
'{"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 +
cache: false,
success: function (data) {
error: function (xhr, ajaxOptions, thrownError) {

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:


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

Analytics Widgets – What more can be done

Analytics Widgets – What more can be done


In a data-driven business setup, there will be scenarios where critical executive decisions are made based on the performance reports of various components of the listed servers in their environment. BizTalk360 aims to offer an out of the box tool with similar capabilities as the Performance Monitor tool in Windows servers.

BizTalk360 Analytics offers visual display of the most important performance counters that are consolidated and arranged on a single screen, so that the information can be monitored in a glance. Custom reports can be built in minutes with the metrics that really count for your business, and they’re easy to share with other users in the system. In addition, dashboards can give you a summary of many reports on a single page, using drag-and-drop widgets for fast, easy customization.

The Analytics feature is available only to the Platinum licensed BizTalk360 environments. By default, the Analytics service is installed with BizTalk360 and in non-Platinum environments, users can only view sample analytics data.

The Analytics Widgets

It is with the Dashboard widgets that all the performance related information can be seen in a nice graphical display. In the Analytics section, there is also the reporting capability added, where the reports can be sent as attachment in emails.

In this blog I am going to explain a few scenarios where the Analytics widgets are more helpful. Let’s move on.

Customer Scenarios

The Analytics Dashboard contains lot of widgets that provide you detailed information of the performance of your BizTalk environment.

For example, the BizTalk Host Performance widget will help you to get the information of the top performing hosts in the BizTalk environment. The BizTalk messaging performance gives you the picture of the message count and average execution time of the total messages transferred during the configured datetime range.

Date Range – Select the date range for chart. The options are Last 24 hrs, Last 7 days, Last 30 days and Custom:

  • Last 24 Hrs – Displays the data from last 24 hours
  • Last 7 days – Displays the data from last 7 days
  • Last 30 days – Displays the data from last 30 days
  • Custom – To add a specific number of days, use the scroller to select the day

Based on the selected date range, the performance data gets collected from the BizTalk servers.

The other basic widgets include the Message volume and the Transmission failure rate widgets.

Lot more to do with Custom widgets

Don’t just stop with the basic widgets in the Analytics section; there is lot more to do with the Custom widgets.

Once the Analytics components, for which the data needs to be collected, are enabled, the different artifacts for the Custom widgets will get enabled. With the Custom widgets you can chose different metrics for which you want to view the data.

Let’s take a few scenarios to understand what we can achieve with the Custom widgets.

Comparing two BizTalk servers’ performances

There is a scenario where you would like to compare the performance of two BizTalk servers and see which one consumes more memory and CPU. This can be done by selecting the servers in the artifact section and corresponding metrics.

Get the message count for specific port

Of course, the count of messages passing through the port can be determined through process monitoring. But it will be checked with specific time intervals either in hours or minutes. But, what if you want to see the count instantly within the specific datetime range. Here comes the Custom widget, with which you can get the count of specific ports.

But to get these data, Tracking needs to be enabled for the ports. Based on the enabled tracking data collection counters, the data gets collected by the BizTalk360 Analytics service using the performance counters.

Failure rate of a port

Also, the failure rate of a particular port, running a specific host instance, can be determined with the help of a Custom widget. This would include the failure rate for the port and the number of instances running for that particular host. With the combination of these two metrics, the failure rate of a port can be determined and displayed in a nice graphical view.

Checking the MessageBoxDb

There are also counters for checking the MessageBoxDB. The key performance indicators of the message box database which includes the spool size and the status of the pending jobs, can be visualised with the widgets.

BizTalk Host Details

We do have certain metrics like Host Queue Length, number of instances and suspended instances for the particular hosts, which can be obtained from the custom widgets.

Above mentioned are the very few scenarios that can be achieved using the widgets. With the different combinations of the metrics, all performance related information can be obtained through the Analytics widgets.


Do you wish to see more information on the widgets or from the product itself, please put in your suggestions and feedback in our user voice portal The existing ideas can also be voted for. We at BizTalk360 aim at providing the features that fulfill the customer requirements. It is from this feedback portal that the features get picked up for every release.

For more information about how these widgets work, check our Documentation portal.

Happy monitoring with BizTalk360 😊

Author: Praveena Jayanarayanan

I am working as Senior Support Engineer at BizTalk360. I always believe in team work leading to success because “We all cannot do everything or solve every issue. ‘It’s impossible’. However, if we each simply do our part, make our own contribution, regardless of how small we may think it is…. together it adds up and great things get accomplished.”

Can we create Custom widgets with cross domain URL?

Can we create Custom widgets with cross domain URL?

In my previous blog, I spoke about one of the issues we encountered during our support. In this blog, I will specifically be talking about the custom widgets in BizTalk360, which a lot of our customers use to display data that is important to them. Users are able to create custom widgets and associate them with a dashboard. Custom widgets allow integration with BizTalk360’s own API’s, as well as third-party API’s.

The below code in a custom widget shows analytical data in graphical form. In recent times, we received a support case where the customer was trying to create a custom widget referencing the blog. Following should be the result of the custom widget in the dashboard.

Can we create Custom widgets with cross domain URL?

Can we create Custom widgets with cross domain URL?

But the customer ended up with the below error after integrating the custom widget.

Can we create Custom widgets with cross domain URL?

A script for the custom widget:

//URL used to get JSON Data for the Charts
this.URL = '';

//Refresh interval in milliseconds 60000 milliseconds = 60 seconds
this.REFRESH_INTERVAL = 60000;
//flag to check if widget should auto Refresh
//Highcharts Options
            chart: {
                type: 'line',
                zoomType: 'x'
            exporting: {
                enabled: false
            credits: {
                enabled: false,
                title: '',
                style: {
                    display: 'none'
            title: {
                text: 'Sales of 2016'
            xAxis: {
                type: 'datetime'
            yAxis: {
                title: {
                text: 'Units'
            tooltip: {
                crosshairs: true,
                shared: true,
                valueSuffix: 'Units'
            legend: {
                enabled: false
            series: [{
                name: 'Sales',
                data: []
this.widgetDetails = ko.observable();
this.error = ko.observable(null);
var _this = this;
var getdata = function() 
$.getJSON(_this.URL, function (data) 
                _this.HIGHCHART_OPTIONS.series[0].data = data;
//loading data for the first time
//handles auto refresh
    setInterval(getdata, this.REFRESH_INTERVAL);
<!-- ko if: error() == null -->
<div data-bind="highCharts: widgetDetails()" style="height:380px; width:700px"></div>
<!-- /ko -->
<!-- ko if: error() != null -->
<div class="row">
  <div class="col-md-offset-4 col-md-4 bg-danger">
      <p>Error occured while trying to fetch data </p>
        <span>Status : </span><span data-bind="text:error().status"></span><br>
      <span>Status Text : </span><span data-bind="text:error().statusText"></span>
<!-- /ko -->

Script Explained

This code consists of 4 configuration variables.


URL variable will allow you configure the API URL from where you fetch the JSON data. Based on the High charts options, the formatting for the data may also change. Here in the above example, the API returns the data as JSON array and date-time stamp as a Unix Timestamp.


This flag determines if your widget should be auto-refreshed or not. For instance, if the service call that feeds your chart is very expensive and you don’t want to call that every now and then, then you can probably disable this flag or set the refresh interval to a higher value.


Refresh interval lets you configure the interval after which the widget data should be refreshed. Note that the interval is in milliseconds. So, if you want it to refresh every one minute then you should set the refresh interval to 60000. Note that for an auto-refresh to work, AUTO_REFRESH_ENABLED flag must be set to true.


BizTalk360 uses High charts for all data analytics. We already have the underlying binding handler framework to apply the options and this makes analytic widget creation a lot easier. To modify the charts, you simply need to update the HIGHCHART_OPTIONS. In this example,

the “data” property inside series array (where the data is supposed to be) is left as an empty array intentionally. It will be filled with the data that is retrieved from the URL that you have specified. High charts support a variety of charts and you can follow this link to get the type of chart that you want to bring into your custom widget.

The line chart that we have created here can be converted into an Area chart or a Column chart by simply changing “type” under “charts” options in HIGHCHART_OPTIONS.

Investigation of the Issue

Initially, we suspected that the customer might not be able to fetch the JSON data using the URL. When we asked to browse the URL and they could browse and view the results. So, the next step was to isolate the case at the customer end. Whenever such a situation arises we require more information about the customer’s environment, we would go for a web meeting with a screen sharing session. We went for the screen sharing session and we started with the basic troubleshooting steps like checking the configuration, environment etc.

At last, we found that the customer is using https://localhot/biztalk360 and he is trying to monitor

Using HTTPS, there is a security code being generated and shared to accept the information between computers. (Say, in client and server architecture). This keeps the information safe from the hackers.

They use the “code” on a Secure Sockets Layer (SSL), sometimes called Transport Layer Security (TLS) to send the information back and forth.

Resolution Provided

When the HTTP is used inside the HTTPS URL, the HTTPS expected a “code” from HTTP. When the response from the widget URL was coming without the code it threw the error message “Access is denied” on the widget.

Hence, It is not possible to create custom widgets with the cross-domain URL. If the HTTPS is used, all the related URL must use the HTTPS.

If you have any questions, contact us at Also, feel free to leave your feedback in our forum.

Author: Sivaramakrishnan Arumugam

Sivaramakrishnan is our Support Engineer with quite a few certifications under his belt. He has been instrumental in handling the customer support area. He believes Travelling makes happy of anyone.

EDI Dashboard widgets for reporting MDN status in BizTalk360

EDI Dashboard widgets for reporting MDN status in BizTalk360

Electronic Data Interchange, provides a common-Industrial standard to communicate between different enterprises with a predefined format to reduce the manual efforts between trading partners. AS2(ApplicationStatement2) standard is a specification used in the EDI messages to transport messages securely over the wire with certificate/encryption algorithms.

EDI Dashboard widgets

MDN, Message Disposition Notification is a Technical Acknowledgement, which indicates whether the AS2 messages was successfully received by the recipient. With millions of EDI transactions happens each day, it is always important to track and report the success/failures on the consolidated interface. Even though BizTalk Admin console provides a message level AS2-MDN status on the EDI reporting, BizTalk360 takes this to the next level of consolidating the MDN status or other EDI status on a chart view for business understanding.

EDI Reports in BizTalk

EDI Reports in BizTalk- Prerequisites

To Enable EDI reports in BizTalk, the prerequisite is to enable BAM. As the EDI reports utilize the BAM infrastructure, the EDI tracking data is being organized within the BAMPrimaryImport tables. Make sure to enable the BAM and EDI Reporting in the BizTalk Admin configuration pane to kick start with EDI Reporting!

EDI Reports in BizTalk

EDI Reporting in BizTalk Admin console

BizTalk Admin console displays the AS2-MDN status on a group level and the status is displayed on a per-message basis. Various filters are being available to segregate the party/status based messages.

EDI Reporting in BizTalk Admin console

Below is the list of tables responsible for determining the AS2-MDN status/ AS2 interchange status.

AS2-MDN status/ AS2 interchange status

List of MDN Status

Below is the list of MDN status tracked in BizTalk Admin console
1: Acknowledged – Processed
2: Acknowledged – Rejected
3: Processed – MDN Pending
4: Processed – MDN not Expected
5: Processed – Not Acknowledged – Resend attempts exceeded
6: ‘Processed – Not Acknowledged – Resend duration exceeded

EDI Reporting in BizTalk360

EDI Reporting in BizTalk360 provides rich-dashboard widgets to get known on the consolidated EDI details from Interchange level to MDN status level.

Navigation: Operations>>Electronic Data Interchange>>EDI Dashboard>>Add Widget

EDI Reporting in BizTalk360

Below are the widgets available in BizTalk360 for EDI Reporting, customize it according to the Dashboard requirement.

AS2MDN-status based widgets

As this blog covers more on the side of MDN status, there are two different AS2-MDN status widgets are available in the EDI dashboard of BizTalk360. MDN Status in BizTalk is determined by various parameters like

  1. MdnDisposition Type
  2. DispositionModifier ExtType
  3. DispositionModifier ExtDescription
  4. MDN Status number

The permutation/combination of all the above four parameters varies the MDN enumerated value at BizTalk Admin console

AS2MDN-status based widgets

  1. Number of AS2 messages by partner and MDN status

This widget reports the MDN status value filtered based on partners.

MDN status value filtered based on partners

2. Number of AS2 messages by partner and MDN status(All)

This widget represents the aggregated count of received or send messages per partner and MDN status.

count of received or send messages per partner and MDN status

3. Number of AS2 messages by MDN status

This widget represents the Number of AS2 partners by MDN status in a chart for all the partners in one chart view also indicates the segregation of receive/send.

Number of AS2 partners by MDN status

As this gives the comprehensive report on all the AS2/EDI events, BizTalk360 also provides options to set the date range from which the results should be displayed. This date range segregated as

Last24hrs, Last7Days, and Last30Days.

AS2/EDI events

This widget can also be added in the Home screen of BizTalk360

biztalk360 home

Custom Widget

BizTalk360 also provides the facility to create a Custom widget, which can be tailor made to your requirement. As the BAM tables are been responsible for EDI reporting, utilize this data source to create your own Widget. Follow this link for step by step guide to create a custom widget.


As there were around 12 EDI widgets, based on the Business/Transaction requirements you can easily customize and have an eagle’s view of all the EDI transactions with no recurrence of manual work. Explore more of our inbuilt widgets and enjoy EDI reporting with just a click away from BizTalk360!

Author: Vignesh Sukumar

Vignesh, A Senior BizTalk Developer @BizTalk360 has crossed half a decade of BizTalk Experience. He is passionate about evolving Integration Technologies. Vignesh has worked for several BizTalk Projects on various Integration Patterns and has an expertise on BAM. His Hobbies includes Training, Mentoring and Travelling