Secure-SQL-Query-Reports

Secure SQL Query Reports

Published on : Jul 18, 2019

Category : BizTalk360 Update

yuvaranjani

Author

At the moment we are working on release v9.0.2 of BizTalk360. As always, we have many exciting features and enhancements coming up with this release.

One of the main sections in BizTalk360 is the Analytics section. BizTalk360 Analytics offers a visual display of the most important performance counters that are consolidated and arranged on a single screen so that the information can be monitored in a glance. In addition to that, we have the Reporting feature in BizTalk360 where users are able to generate PDF documents of critical performance metrics and send emails to the user at specific time periods, depending on the requirement. This feature is available only to the platinum licensed BizTalk360 environments.

In the new version v9.0.2 of BizTalk360, you can add Secure SQL Queries which can be mapped to a schedule to get the SQL query output as a report.

Note: BizTalk360 consists of 6 default queries which the user can only execute. In addition to that, the user can create new custom SQL queries as per their business need.

Secure SQL Reports in Analytics Reporting

Scenario 1:

Consider the scenario where you have a list of messages that ware not received during the past few hours/days and you want to know the details of these unprocessed messages. Usually you will be checking this by executing the query manually, however, this can be difficult on your busy schedule as you need to switch between different tools. Luckily, this can now be done simply with the new “SQL Reports” feature under Reporting. Just create a query, map it to the schedule according to your needs and then you will start receiving the query output as a report.

Scenario 2:

Consider another scenario: in BizTalk360, you have many users using the application. It’s important that an administrator should be aware of who did what. This can be seen in the Governance and Auditing section manually, but you might need to filter the records every time. Instead of filtering the records manually each time, you can create a Custom SQL Query and map the query to the Reporting Schedule which will send a periodic report to the configured admin email address. With this, the admin can easily get a report with regards to the actions performed by the other users. You can schedule this report and get the query output as a PDF document in the Analytics Reporting.

User permissions required to Process the Secure SQL Query Reports in BizTalk360

By default, the Superuser will have access to all the queries and also to the BizTalk reporting feature.

In case of a normal user, as a first step of processing the Secure SQL Query Reports, the user should ensure that the below permissions are provided.

The user should have permissions to:

  • BizTalk Reporting
  • Secure SQL Queries

Note: whenever a new query is being created, the user should ensure that, the normal user will be provided access to the newly created query (BizTalk360-> Settings-> User Access Policy-> Manage Users-> Secure SQL Queries-> Advanced).

Once all permissions set, the next step is to create a Reporting Schedule.

report-schedule-basic

report-schedule-advanced

From the schedule configuration screen, you can define when you want to generate a report. The available options are Daily, Weekly and Monthly. Say, for instance, if the user wants to get a report on a daily basis, the user can choose Daily and set the time as per their need. This way the user will get the report on the scheduled time on a daily basis.report-schedule

Once you have created a schedule, the next step is creating a report and map that report to the reporting schedule.

map-report-schedule

Once all is done, add a SQL Query widget by clicking ‘Secure SQL Query Results’  in the right panel.

secure-sql-query-report

When you click on ‘Secure SQL Query Results’, it opens the blade where you can enter the widget title and select the query from the list (List of Saved queries created in BizTalk360-> Operation-> Data Access -> Secure SQL Queries section).

sql-query-widget

Once you click on ‘Save’, the widget will be added in a reporting dashboard.

When the widget is added to the dashboard, it gets added with the maximum width and height. In the case of having a bigger number of records, space might not be enough to view all the records. For that, we have provided the option to customize the widget. You can expand the widget as much as you want. For now, we are allowing a maximum of 100 records (top 100 records) in the widget.

widget-numbers

user-activity-report

When you click on the ‘PDF’ button, the downloaded PDF document will look like below. The same will be sent to the configured email ids.

user-activity-report-pdf

Conclusion

SQL Reports makes your work easier, you will get in time report summaries of what you want to know about your BizTalk environment.