Using BizTalk360 for alarm notification statistics
It is nothing new that BizTalk360 enables you to set up automated monitoring for BizTalk. This includes the deployed BizTalk application artifacts and different kind of endpoints. You can even track the processing which is taking place in your BizTalk environment.
For each unexpected situation for which you have set up monitoring, you can receive notifications via for example email, SMS/Text message or the Event Log. Besides that, BizTalk360 also provides Notification channels to connect to products like Slack, Microsoft Teams and HP Operations Manager. In case you are using ServiceNow, you can even have BizTalk360 generate incidents into that product! If that still does not meet your requirements, you can use a Web Hook, or the easy-to-use API provided by the product to integrate with your systems.
The BizTalk360 database contains information about the transmitted notifications.
Collecting Alarm statistics
Recently, we had a call with a consultancy company which manages several BizTalk environments for their clients. These clients are using BizTalk360 for operating and monitoring the BizTalk environments.
The consultancy company was interested in knowing whether BizTalk360 provides any statistics about all the alarms which were triggered over time. This kind of statistics serves different goals. On one side, this kind of information gives insight in components which frequently have outage, enabling to take counter measures. On the other side, this can also be used to provide such information to the client of the consultancy company as a kind of reporting.
In this blog post we describe how you can use the information in the BizTalk360 database, to show statistics about which alarms have been transmitting which kind of notifications over time.
Showing SQL data in BizTalk360
As already mentioned, the information about the transmitted notifications is stored in the BizTalk360 database. BizTalk360 provides different capabilities to show data which is stored in SQL Server databases. These capabilities are:
• Secure SQL Queries – see https://docs.biztalk360.com/docs/secure-sql-queries
• Custom Widgets – see https://docs.biztalk360.com/docs/creating-a-custom-widget-for-executing-secure-sql-queries
Depending on the requirements how you want to be able to view that data, you can use either or both features, but the main characteristics are that Secure SQL Queries are mainly used manually, where Custom Widgets can become shown on a dashboard and the output will be refreshed automatically.
The information we will be using in this blog post is stored in a table called b360_alert_notify_ToBeNotified. The content of that table is specific to Threshold monitoring. If you need more detailed information about Data Monitoring statistics, you should have a look at a different table. Based on the b360_alert_notify_ToBeNotified table, you can write a SQL query which looks like below:
SELECT aa.Name AS 'Alarm Name',
an.NotificationRequiredType AS 'Notification Type',
COUNT(an.AlarmId) AS Count,
MAX(CreatedDateTime) AS 'Most recent'
FROM b360_alert_notify_ToBeNotified an
RIGHT OUTER JOIN b360_alert_Alarm aa ON an.AlarmId = aa.Id
WHERE (an.NotificationRequiredType <> 'Regular')
GROUP BY aa.Name, an.NotificationRequiredType
order by [Alarm Name]
In above query, notifications of type ‘Regular’ have been left out. Regular notifications refer to the Health alarms which can be received at a specified time, to show the state of all the mapped artifacts of an alarm. For this scenario, that information is considered as not relevant.
Retention period
It is good to know, that without any filters, the data will be shown according the Purging policy for Alert & Maintenance History. This policy can be viewed and reconfigured in the Settings area, under BizTalk360 Health, in the Purging Policy screen.
Obviously, in case you do want to see a bit more limited data set, you can add WHERE conditions based on the CreatedDateTime field.
Showing Alarm statistics as a Secure SQL Query
When you create a Secure SQL Query for this, the output of the query can look like below.
As you can see, the query shows information about the name of the alarm, which kind of notification is involved, the count of notifications and the most recent date/time a notification has been transmitted, for that alarm/notification type combination.
If you want to know more about how to create Secure SQL Queries, you can check out the Documentation portal about this feature.
Having this kind of information as a Secure SQL Query can be very handy. You have the information easily at hand by accessing that query and run it. The results can also be exported to a .CSV file.
Showing Alarm statistics as a Custom Widget
If you want to have an even better experience, you can consider creating a Custom Widget for this. Doing so, enables you to have this information on a dashboard and because of the behavior of these dashboards, the widget will be automatically refreshed. Besides that, by a simple click, you can export the entire dashboard, which could contain more statistical widgets, to a PDF file and provide it to your clients.
Based on the query we have seen earlier in this article such a widget will look like below.
Once the widget has been added to a dashboard, the query will automatically be executed, and the data will be refreshed each minute. If needed, you can change the refresh interval in the script of the widget.
If you want to read more about creating such Custom Widgets, check the Documentation portal for it. It contains some detailed articles about this topic.
Conclusion
We have shown another way on how BizTalk360 can be useful. So, if you have any questions about this capability, or anything else related to BizTalk360 feel free to contact us. Why not give BizTalk360 a try! You can download a free trial which contains all the features for a limited time.
