SQL Query

Secure SQL Queries functionality in BizTalk360 acts as a secure platform to store predefined queries and provide access to BizTalk members to execute the queries. It's pre-loaded with a set of queries and also allows the users (with permissions) to build their own queries that are more appropriate to the organization's business. Also, These query results can be monitored and alerted to the user at the scheduled time.

One of the biggest advantages of using this feature is you do not want to switch between multiple external tools. The SQL Query can be centrally stored in your BizTalk environment and shared among the eligible team members.

A few scenarios you can use this feature are to monitor the spool or host tables in Message Box, Message In-out table in the tracking database, look for some values in BAM database tables, , check for large messages, monitor send port URI and get notified if it changes. 

Setting up an SQL Query Data Monitor

Scenario(BizTalk Audit) - Say, you want to monitor your BizTalk environment and receive audit details at the end of the business day to know more about the changes made in the BizTalk environment.

For the above scenario, Auditing must be enabled in the BizTalk admin console and you can use the default secure SQL query "BizTalk Audit ".

Create a new schedule by navigating to Monitoring -> Data Monitoring -> More->SQL Query-> Schedule. Click "Add Schedule" and follow the below steps. 

1. Choose Alarm - The first step to set up SQL Query data monitoring is to create an alarm. Once you have created the alarm, the alarm name will be displayed in the schedule configuration drop-down. Select the alarm that should be used for SQL Query data monitoring
2. Schedule Name - A meaningful name for the Query monitor. As per the above scenario, we are creating the schedule to monitor the BizTalk audit so the schedule name can be provided as "BizTalk Audit"
3. Enable the schedule for monitoring. you can disable this to stop monitoring.
4. Query- The query options list all the secure SQL queries created on the Administration side, select the query for monitoring from the list. To monitor the BizTalk auditing you already have a predefined query "BizTalk Audit" select that.
   (Refer to this doc to create a secure SQL query in BizTalk360)
5. Define the condition for monitoring by clicking add filter option, which lists the available columns in the selected query. 
6. How do you want to check - Choose whether you want to monitor all the data from the beginning or only the past few sets of data based on the date-time range. This date-time range is listed based on the date-time columns available in the chosen query. In the BizTalk audit query "Activity Date Time " column is there so you can select that.
7. Set the Threshold Condition 
   1. Warning Threshold - Set the value when you want to be notified when there is a warning. First, select the Operator (> or =) and then set the warning value.
   2. Error Threshold - Set the value when you want to be notified when there is an error. First, select the Operator (> or =) and then set the error value.
8. Send Query Result - Enable this to receive the configured query result as an attachment in the mail
   
9. Monitoring Schedule
   1. Frequency (How often you want to monitor) -Select Daily to monitor the daily activities of the environment and send the audit details at the end of the day
   2. When during the day - Select when the data monitor must execute on a particular day during business hours. You can customize your business hours by selecting the values from the Business Day Start and Business Day End options.
   3. At what time? - 
      1. End Of the Business day -  For e.g, if the business end time specified is 5 PM (default value), the data monitor will execute exactly at 5 PM
      2.  Frequency - Select the number of minutes/hours after which the data monitor should execute. E.g., For 1 hour means the data monitor will execute once every 1 hour 
   4. Start Date / End Date - When you want to start/stop the scheduled execution. If you want the schedule to execute all the time choose No end date
      
      
      What's New in v10.7 ?

      The monthly Data Monitoring schedule configuration now includes an additional option called "End-of-Month." By selecting this option, the schedule will be executed at the end of the month, considering the complete month's data for monitoring purposes.

10.  Summary - The summary information will automatically be listed based on the information selected in the previous steps. Click the Save button to store the schedule configuration.

Monitoring and Notification 

On successful schedule configuration, you should be able to see the Next Run Time of the scheduled execution in the Manage schedule section. The results will be notified to the configured email address or any other notification channel, and they will also be updated in the dashboard.

Access policy

To use this feature user must be a super user of BizTalk360. The custom user must have access to data monitoring and also must have access to all the secure SQL queries.