BizTalk Server 2020 – Audit Capabilities and View Auditing Activities in BizTalk360

by Senthil Kumar Palanisamy | Apr 24, 2020 | BizTalk Community Blogs via Syndication

Introduction

Integrate is the premier event in the Microsoft Integration space. Every year it has happened in London, focusing on BizTalk & Azure. Due to Covid-19 pandemic uncertainty, Integrate 2020 is planned as a virtual conference.  In Integrate 2019, the Microsoft product group was announced the new version release of BizTalk Server 2020. Microsoft has announced the release of BizTalk Server 2020 early this year (January 15, 2020).

BizTalk Server 2020 has bundled with a lot of exciting features for BizTalk Administrators, operators, Developers, and Business users. Some of the Important features in BizTalk Server 2020 are

• Analytics
• Management APIS, API Management
• Advanced Scheduling
• Hybrid Adapters (Azure Blob, Service Bus v2, Event Hubs & Office 365)
• Audit Log
• XSLT 3.0

In this blog, we are focusing on the BizTalk Audit capabilities in BizTalk Server 2020. Different operations, that are performed in the BizTalk Admin console, are being audited. It could be useful to BizTalk Administrators and Deployment Teams to know what changes have happened in the BizTalk Group.

BizTalk Audit Activities

Auditing can be handy to backtrack changes that have happened in, among other things, BizTalk Applications. With this new auditing feature, the following activities are captured in the BizTalk Management Database (BizTalkMgmtDb). They are being stored in the table ‘bts_auditlog’.

Configure Audit Log Settings

Users can get started with audit capabilities, by enabling the Audit Management Operations property in the BizTalk group settings.

By default, 10.000 activities are audited. Users can increase or decrease the number of audit entries.

Audit Operations

In BizTalk Server 2020, operation metrics on different Artifacts are audited. Check the table below, for the operations which are audited with BizTalk Server 2020. 

View Audit Logs

To be able to view Audit data, users must install the Operational Data Service in IIS. After that, users can retrieve Audit log data with data range, as shown below;

http://localhost/BizTalkOperationalDataService/AuditLogs?fromDate=2019-12-25T01:00:00&toDate=2020-01-10

Supported date formats are yyyy-MM-dd or yyyy-MM-ddThh:mm:ss.

So, in summary, you have two capabilities to view the Audit Log:

• Via the Operational Data Services
• Via the bts_auditlog table in the BizTalkMgmtDb

View BizTalk Audit Log in BizTalk360

BizTalk360 users can take advantage of custom widgets to visualize the audit logs that are captured in BizTalk Server 2020.  

Secure SQL Queries

BizTalk360 offers the Secure SQL Queries functionality as a secure platform to store and execute predefined queries. BizTalk360 comes pre-loaded with a set of queries that are of use for BizTalk administrators.

In addition, BizTalk360 allows you to manage the existing SQL queries and add/edit/delete your own queries.

Custom Widgets

The ability to create custom widgets is a powerful feature in BizTalk360 which enables you to integrate/visualize data from third-party sources using API methods or pull the data from databases through Secure SQL Queries.

We will have a look at how users can visualize the audit data from BizTalkMgmtDb in BizTalk360. The steps that are involved to create custom widgets are:

1. Create the Secure SQL Query to pull the audit log from the “bts_auditlog” table. In this custom widget script, we will show Artifact Name, Artifact Type, User, Machine, Operation, and CreatedDate.

 If you want to see the payload in the table, you can include the SQL script to visualize it in the operational dashboard.

SELECT [UserPrincipal],[Machine],[ArtifactType],[ArtifactName],[OperationName],[Payload],[CreatedDate] FROM BTS_AUDITLOG  ORDER BY [CreatedDate] Desc 



2. The next step is to create a custom widget. If you are new to custom widgets,
   follow this article which explains how to create custom widgets.



Custom Widgets Script

<div id="WidgetScroll" style="top:30px;" data-bind="addScrollBar: WidgetScroll, scrollCallback: 'false'">
          <table class="table table-lists">
        <thead>
            <tr>
                <th style="width:30%">Artifact Name</th>
                <th>Artifact Type</th>
                <th>User</th>
                <th>Machine</th>
                <th>Operation</th>            
              <th>Audit Date</th>              
            </tr>
        </thead>
        <tbody>
          <!-- ko if: (auditActivities()) -->
            <!-- ko foreach: auditActivities().root.records.record -->
            <tr>
                <td data-bind="text: ArtifactName"></td>               
              <td data-bind="text: ArtifactType"></td>
               <td data-bind="text: UserPrincipal"></td>
               <td data-bind="text: Machine"></td>
               <td data-bind="text: OperationName"></td>              
               <td data-bind="text: CreatedDate"></td>
            </tr>
            <!-- /ko -->
          <!-- /ko -->
        </tbody>
    </table>
</div>
  <script>
	username = "`username_119251`";
	password =  "`password_702990`";
	environmentId = "`environmentid_346562`";
	queryId = "408e4e00-dc6a-4d3b-a2dd-032ddd232ffe";
	queryName = "BizTalk Server 2020 Operations";
	sqlInstance = "KOVLTP013DEV";
	database = "BizTalkMgmtDb";
	sqlQuery = "SELECT [UserPrincipal],[Machine],[ArtifactType],[ArtifactName],[OperationName],[Payload],[CreatedDate] FROM BTS_AUDITLOG  ORDER BY [CreatedDate] Desc";
	url = 'http://localhost/BizTalk360/Services.REST/BizTalkGroupService.svc/ExecuteCustomSQLQuery';

	auditActivities = ko.observable();
	x2js = new X2JS({ attributePrefix: '' }); 

	auditActivitiesList = function () {
            var _this = this;			
            _this.getAuditActivities(function (data) {                
            _this.auditActivities(x2js.xml_str2json(data.queryResult));          
           });

        };
    getAuditActivities = 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);
	},
});
        };
  auditActivitiesList();
</script>

3. Associate the widgets to the Operational dashboard to visualize the custom widgets. After this configuration, we can see the audit activities, in the tabular format, in a BizTalk360 dashboard

Conclusion

BizTalk360 has capabilities to audit the various operations (Applications Artifacts – Start/Stop, Host Instances – Enable/Disable, Service Instances – Resume/Terminate/Suspended, View Message Content, User Management- Add/Edit/Delete) which is performed in BizTalk360 web application. Thereby, this is more complete than the current features in BizTalk Server 2020. BizTalk360 users can take advantage of both auditing capabilities in a single tool.

Users can write more custom scenarios through Custom Widgets. This makes users more collaborative by using BizTalk360. Happy Integration!

The post BizTalk Server 2020 – Audit Capabilities and View Auditing Activities in BizTalk360 appeared first on BizTalk360.