Creating a Custom Widget for executing Secure SQL Queries

Introduction

Using Custom Widgets on the Operations dashboard can be a very powerful capability, which can help BizTalk Administrators work efficiently. You could use Custom Widgets for amongst others the following purposes:

• Embed third-party portals like Power BI or internal portals
• Show information based on API calls to BizTalk360 or other APIs
• View information based on SQL Server queries

In this article, we describe in detail how you can use Custom Widgets to query SQL Server databases and show the results in a Custom Widget. To get you started, we provide a template which can be used as a good starting point. The source code of Custom Widgets might contain confidential information like the credentials of a service account. The last section of this article describes how you can hide such information, by using placeholders.

To create a Custom Widget, you need to perform the following steps:

1. Create a Secure SQL Query
2. Collect multiple settings
3. Adapt the layout according to the SQL Query
4. Provide the collected settings
5. Replace variables for uniqueness across all the Custom Widgets
6. Create a new Custom Widget
7. Add the Custom Widget to an Operations Dashboard

In this article, all the above-mentioned steps are described in detail. The last section of this article shows a template of a Custom Widget, which can be used for executing Secure SQL Queries from a Custom Widget.

Create a Secure SQL Query

We firstly need to create the query, we would like to have as a widget, in the Secure SQL Queries feature. You can find this feature under Operations and then Data Access. For sake of simplicity, in this case, let’s use a query which already exists, being ‘All Disabled Receive Locations’.

In the next step, we will collect some data about, amongst others, this query. We’ll use this data to set up the source code of the Custom Widget.

Collect multiple settings

Next, we need to collect some configuration data, which we need later in the source code of the Custom Widget. Write down the following information about the Secure SQL Query: • Friendly Name • SQL Instance • Database • SQL Query

We also need the value of the Id field of the Secure SQL Query we decided to use in step 1. Therefore, we need to access SQL Server Management Studio (SSMS) and query a table. Perform the following steps:

1. Start SQL Server Management Studio (SSMS) and point to the SQL Server Instance where the BizTalk360 database resides

2. In the button bar, click ‘New Query’ (see screenshot), enter below query and execute it

SELECT *
  FROM [BizTalk360].[dbo].[b360_data_CustomSQLQuery]
  WHERE QueryName='All Disabled Receive Locations'

3. From the result, copy the GUID in the field ‘Id’ and save it for later use in the source code of the Custom Widget

Next, we also need to have the name of the BizTalk360 server. This is needed, as we must execute an API call to execute that SQL Query. You can easily take this from the Address bar of your browser, as shown in below screenshot.

Note: this must be the real name of the BizTalk360 server; ‘localhost’ will not work!

The last value we need to provide to the source code of the Custom Widget is the BizTalk360 EnvironmentId. This Id reflects the BizTalk environment in BizTalk360, in which we want to use the Custom Widget. You can retrieve that value from either:

• The API Documentation (under Settings)
• A table in the BizTalk360 database

As we already are in SQL Server Management Studio, let’s get that value from there. Perform the following steps:

1. In the button bar, click ‘New Query’, enter below query and execute it

SELECT *
  FROM [BizTalk360].[dbo].[b360_admin_BizTalkEnvironment]

2. From the result, copy the GUID in the field ‘EnvironmentId’ and save it for later use in the source code of the Custom Widget

Adapt the layout according to the SQL Query

Now that we have all the information we need, we can focus on the source code of the Custom Widget. The first thing we’ll do is aligning the layout of the Custom Widget with the fields which are returned by the SQL query.

Note: Before creating the actual Custom Widget in BizTalk360, it probably is the easiest, to create a copy of the provided template for Custom Widgets. You can do all the modifications of the source code in a text editor like Notepad or Notepad++.

As depicted in below screenshot, the SQL query we are using returns 4 fields.

For each field, we will create a header and a body in an HTML table. The header will show the name of the column and can be used for formatting (for example: setting the column width). The body will contain the binding between the table and the fields which are returned by the query. After making the needed changes, the layout part of the Custom Widget will be as follows:

<table class="table table-lists">
  <thead>
      <tr>
          <th>Receive Port </th>
          <th>Receive Location </th>
          <th>Application </th>
          <th>URI </th>
      </tr>
  </thead>
  <tbody>
      <!-- ko if: (bizTalkSendPorts()) -->
         <!-- ko foreach: bizTalkSendPorts() -->
         <tr>
             <td data-bind="text: ReceivePortName"></td>
             <td data-bind="text: ReceiveLocationName"></td>
             <td data-bind="text: ApplicationName"></td>
             <td data-bind="text: InboundTransportURL"></td>
         </tr>
         <!-- /ko -->
      <!-- /ko -->
  </tbody>
</table>

Provide the collected settings

The next step, is to take all the earlier collected settings (see Step 2) and provide them to the source code of the Custom Widget. To make this a bit easier, the Custom Widget template contains a section where all the variables must be entered. The section looks like below:

 // 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 (take from 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 SQL Query which needs to be executed from the custom Widget
 bt360server = "";   // Name of the BizTalk360 server (needed to execute the SQL query)
 // END User variables

Enter all the earlier collected settings, including the credentials of the BizTalk360 Service Account.

Note: The credentials of the BizTalk360 Service Account are shown in clear text. Of course, you don’t want to reveal these credentials; you can use Placeholders to hide these details. Using Placeholders is subject of the last section of this article.

Replace variables for uniqueness

In case you have multiple Custom Widgets within your BizTalk360 environment, it is important that the variables in the scripts of the Custom Widgets are unique across all the Custom Widgets in the environment. The script we created so far, contains a few variables which might lead to conflicts with other Custom Widgets. Rename the following variables:

• Change all occurrences of ‘bizTalkSendPorts’ to ‘bizTalkReceiveLocations’
• Change all occurrences of ‘bizTalkSendPortsList’ to ‘bizTalkReceiveLocationsList’
• Change all occurrences of ‘getbizTalkSendPorts’ to ‘getbizTalkReceiveLocations’

After that has been done, we have completed the needed modifications to the source code for the Custom Widget.

Create a new Custom Widget

Next, we are going to add the source code to a new Custom Widget. Follow below steps:

1. Open BizTalk360

2. Navigate to the Settings section (only allowed for Super Users). See the screenshot above, to be able to find this option
3. In the Settings section of BizTalk360, click the menu item called ‘Custom Widgets’. It is one of the last menu items in the left-hand side menu
4. The Custom Widgets section shows all the earlier created Custom Widgets. From here, you can maintain existing Custom Widgets and/or create new Custom Widgets

5. Create a new Custom Widget by clicking the ‘Create One’ button
6. A blade appears, in which you can enter the details about the Custom Widget

7. Enter the following values:

   • Name - All Disabled Receive Locations
   • Description - Shows a list of all the currently Disabled Receive Locations
   • Script - Copy/Paste the source code for this Custom Widget we created earlier

8. Now, click ‘Save’ to store the Custom Widget in the BizTalk360 database

Add to an Operations Dashboard

The Custom Widget has been created. The only remaining step is to add the widget to a dashboard. The kind of widget we created, can be added to the Operations Dashboard. Follow below steps, to add the Custom Widget to such a dashboard:

1. In BizTalk360, navigate to the Operations section
2. Once there, you need to decide to which dashboard want to add the Custom Widget, as you might have already created multiple dashboards. In this scenario, a dashboard called ’Widget tests’ has been created earlier. We will add the Custom Widget to that dashboard

3. Click the ‘Add Widget’ button. A pane is shown, which shows all the different categories for which widgets can be added to the dashboard. Also, the following items are shown with regards to Custom Widgets:

• Custom Widgets – Used to add earlier created Custom Widgets to the dashboard
• Add Custom Widget – Create a new Custom Widget and add to the dashboard

We created the Custom Widget in the previous step, so in this case, we can click ‘Custom Widget’ to add that widget to the dashboard.

4. Besides the Custom Widgets which have been added to the dashboard earlier, also the just created ‘All Disabled Receive Locations’ shows up. Click the blue plus sign to add the Custom Widget to the dashboard

The new Custom Widget immediately becomes shown on the dashboard

5. Click ‘Save’ to save the changes to the dashboard

With the ‘Customize’ button, you can customize the dashboard, for example, to change the size of the Custom Widget, or move it to another part of the dashboard.

Template for Custom Widgets

To help you get started with creating Custom Widgets, you can use below template.

<div id="WidgetScroll" style="top:30px;" data-bind="addScrollBar: WidgetScroll, scrollCallback: 'false'">
   <table class="table table-lists">
      <thead>
         <tr>
            <th style="width:30%">Send Port Name </th>
            <th style="width:30%">Sending URI </th>
            <th style="width:30%">Application </th>
         </tr>
      </thead>
      <tbody>
         <!-- ko if: (bizTalkSendPorts()) -->
            <!-- ko foreach: bizTalkSendPorts() -->
               <tr>
                  <td data-bind="text: SendPortName"></td>
                  <td data-bind="text: SendingURI"></td>
                  <td data-bind="text: ApplicationName"></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 SQL Query which needs to be executed from the custom Widget
   bt360server = "";   // Name of the BizTalk360 server (needed to do an API call to execute the SQL query
   // END User variables

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

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

   bizTalkSendPortsList = function () {
      var _this = this;			
      _this.getbizTalkSendPorts(function (data) {

      var results = x2js.xml_str2json(data.queryResult);
      if (Array.isArray(results.root.records.record))
         _this.bizTalkSendPorts(results.root.records.record);
      else {
         _this.bizTalkSendPorts([results.root.records.record]);
         }
      });
   };
   getbizTalkSendPorts = 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);
         },
      });
   };
   bizTalkSendPortsList();
</script>