Give Your BizTalk Audit Log More Visibility

|  Posted: September 29, 2020  |  Categories: BizTalk360
Tags: BizTalk360

Introduction

The series about making smart use of BizTalk auditing information continues. This series consists of the following parts:

  • Part 1: Receiving automatic auditing reports – How to audit your logs to get auditing reports and share them with team members in PDF format
  • Part 2: Monitor your audit logs and get notified – How to audit your logs and get notified when something unexpected is happening
  • Part 3: Give your BizTalk Audit Log more visibility – Add Auditing related widgets to the home page of BizTalk360

In the first article, we have seen how to make smart use of information that is available in BizTalk360. We customized BizTalk360 by using Secure SQL Queries and BizTalk Reporting to receive reports about Audit trail automatically.

In this blog, we show you how to view the same BizTalk Auditing information in BizTalk360’s Operations dashboard. To do so, we will use the same Secure SQL Query and, in the Operations Dashboard, create a custom widget. Let’s get started!

What needs to be done

Before really getting started, let’s make an inventory of what needs to be done.

  1. Choose the required Secure SQL Query
  2. Collect required information
  3. Create and view the Custom widget in the dashboard

Note: In this blog, we only give an overview of all steps that need to be taken to create the Custom widget. For a detailed description of these steps, check this article in the BizTalk360 Documentation portal.

Ad 1. – Choose the required query

That is an easy one; for our widget, we will use the same Secure SQL Query as we have used before. The query looks like below:

SELECT TOP (1000)
      FORMAT ([ActivityDateTime], ‘dd MMM yyyy – hh:mm:ss’) as [DateTime]
      ,[DomainName] + ‘\’ + [UserName] as [User]
      ,[ServerName]
      ,[HostName]
      ,[Operation]
FROM [BizTalk360].[dbo].[b360_governance_HostInstance]
order by [ActivityDateTime] desc

The above query is about the audit trail of Host Instances. It just serves as an example to show what can be achieved. The BizTalk360 database contains different tables for other kinds of audit trail. You can explore for yourself which kind of audit trail you would like to show up in a custom widget.

You can take the above query and store it in your own BizTalk360 environment as a Secure SQL Query under the name ‘Auditing’.

Note: The Custom widget will contain an HTML table that needs to be aware of the fields in the above query. This is both for the column headers of the table and for the actual values. In the article in the Documentation, the portal is described how this should be done.

Ad 2. – Collect required information

For the widget to work properly, all kind of information needs to be collected. This concerns information like:

  • The BizTalk360 service account and password
  • The BizTalk360 Environment ID (take it from SSMS or API Documentation)
  • The Id of the Secure SQL Query (take it from SSMS)
  • The name of the Secure SQL Query as it is stored under Operations/Secure SQL Query
  • The SQL Instance against which the SQL Query must be executed
  • The database against which the SQL Query must be executed
  • The SQL Query which needs to be executed from the custom Widget
  • The name of the BizTalk360 server

Now you have this information, you can take a template Custom widget, as shown in the article in the Documentation portal, and apply all the above values in their designated fields as shown below.

BizTalk audit log

Once we have applied these values, we can move on and create the custom widget.

Ad 3. – Create and view the Custom widget in the dashboard

Creating the Custom widget can be done by navigating to the Operations Dashboard and, in the upper right corner, click Add Widget.

Custom widget

After that, in the lower right corner, click the Add Custom widget button. In the pane that appears, you need to provide the name of the widget, an optional description of it, and you can paste the code of the widget from the previous step.

Add/remove widget
BizTalk audit log

Once done with providing the required information, you click the Save & Pin to Dashboard button. This saves the Custom widget in the BizTalk360 database and shows the widget in the Operations Dashboard.

BizTalk360 database

Do smart things with available data sources

In this series of articles about exposing audit trail, we wanted to emphasize that BizTalk360 allows you to do smart things with the data you can access from the product. In our scenario, the basis for this is the Secure SQL Query feature which allows you to perform queries against SQL Server databases. So, if BizTalk360 has access to such a data source, you will be able to do smart things with it, such as:

  • Receiving automated reports
  • Monitoring against the data source
  • Exposing the information via widgets

The funny thing, in this case, is that it is not even required to set up monitoring against the Host instances, while still be able to know when somebody has turned on/off a host instance! Of course, the same would be true when you would use another table that contains an audit trail, like the b360_governance_Application table which contains the audit trail about BizTalk Application artifacts.

Exposing BizTalk Server 2020 Audit Trail

Until BizTalk Server 2020, the product did not have any audit trail capabilities. This made it hard to understand who performed which actions in the BizTalk Server Administration console. Luckily, this changed with the introduction of BizTalk Server 2020 in January 2020. With that version, below operations are audited.

BizTalk Server 2020

The audit trail is stored in the BizTalk Management database, in a table called bts_auditlog.

In case you are using BizTalk Server 2020, similarly to the audit trail in BizTalk360, you could also decide to expose the BizTalk Server 2020 audit trail. This will result in maximizing the overview of all that is happening in your BizTalk environment.

If you want to read more about BizTalk Server 2020 Auditing, continue reading with this article.

Conclusion

In the series about Auditing information, we have shown how you can do smart things with data sources that can be accessed via BizTalk360. Creating a custom widget to expose the audit trail directly in the Operations Dashboard, is probably a fairly complicated step. In case, you are struggling with it yet want to have such custom widgets, we have good news for you!

You might be aware that, since the beginning of this year, we are working hard on BizTalk360 v10. Besides a complete revamp of the User Interface and improving the user experience, we will also bring a set of new features to the product. One of these features will be the ability to pin the results of a Secure SQL Query, directly into the Operations Dashboard. So, without creating a custom widget, you will be able to view the output of the underlying Secure SQL Query.
You can read more about what all is coming in v10 in this blog post from the product owner of BizTalk360.

Author: Lex Hegt

Lex Hegt works in the IT sector for more than 30 years, mainly in roles as developer and administrator. He works with BizTalk since BizTalk Server 2004. Currently, he is a Technical Lead at BizTalk360.

Back to Top