Publish Secure SQL Query as a Custom Widget in BizTalk360

Issue at Hand

The topic at hand is trying to create a widget in BizTalk360 that will display the results of SQL query. The below was posted by our customer in our feedback portal.

https://feedback.biztalk360.com/forums/331695-biztalk360-operations/suggestions/13873914-publish-secure-sql-query-as-widget


Secure SQL Query

BizTalk360 offers the Secure SQL Queries functionality as a secure platform to store predefined queries and provide access to BizTalk members to execute the queries. BizTalk360, by default, comes pre-loaded with a set of queries and allows the users (with permissions) to build secure SQL queries that are more appropriate to the organization's business.


Solution Work-Around

You will first need to create a Secure SQL query in BizTalk360 and then create a widget which will display the results of the SQL Query. I have chosen a simple query to retrieve data from [BizTalkMgmtDb].[dbo].[adm_Host] table.




Once that is done we then need to create a custom widget to retrieve the results from the SQL query by calling it via an API and display on the Operations dashboard.




The below code is just an example, and you will need to provide certain parameters like Environment ID and Primary key ID for the Custom SQL Queries and of course Username, password along with server names. The main values for custom SQL queries can be obtained from the BizTalk360 table - [BizTalk36087].[dbo].[b360_data_CustomSQLQuery]



<div id="WidgetScroll" style="top:30px;" data-bind="addScrollBar: WidgetScroll, scrollCallback: 'false'">
    <table class="table table-lists">
        <thead>
            <tr>
                <th style="width:80%">Host Name </th>
                <th>Type</th>
            </tr>
        </thead>
        <tbody>
            <!-- ko foreach: bizTalkHosts -->
            <tr>
                <td data-bind="text: Name"></td>
                <td data-bind="text: HostType"></td>
            </tr>
            <!-- /ko -->
        </tbody>
    </table>
</div>
<script>
    bizTalkHosts = ko.observableArray();
    x2js = new X2JS({
        attributePrefix: ''
    });

    executeBizTalkHostsQuery = function () {
        var _this = this;
        _this.getBizTalkHosts(function (data) {
            _this.bizTalkHosts(x2js.xml_str2json(data.queryResult).root.records.record);
        });
    };

    getBizTalkHosts = function (callback) {
        var url = 'http://localhost/BizTalk360/Services.REST/BizTalkGroupService.svc/ExecuteCustomSQLQuery';
        $.ajax({
            dataType: "json",
            url: url,
            type: "POST",
            contentType: "application/json",
            username: "DESKTOP\username",
            password: "password",
            data: '{"context":{"environmentSettings":{"id":"6c3456ff-a434-45ad-8ce9-7346c4e5487a","licenseEdition":0},"callerReference":"REST-SAMPLE"},"query":{"id":"93345dc1-218d-4cc3-a328-3d1031ec7c52","name":"Host Name","sqlInstance":"DESKTOP","database":"BizTalkMgmtDb","sqlQuery":"SELECT [Name] , [HostType] FROM [BizTalkMgmtDb].[dbo].[adm_Host]","isGlobal":false}}',
            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);
            },
        });
    };
    executeBizTalkHostsQuery();
</script>




Dashboard Custom widget

The Custom widget should now look like this, displaying the results from your SQL table in BizTalk360 Operations dashboard.




I hope you found this article helpful.

Special thanks to Daniel Szweda for his immense help in getting the API calls working correctly.


Login or Signup to post a comment