Created by: Kristinn Magnusson
In exMon, Exception Queries fetch data from a single data provider and return all rows that are considered a violation of a business rule. For example sales orders in a point of sales machine (POS) with an amount of $0 or a 100% discount.
This tutorial contains the following sections:
- Create a basic Query
- Trigger Condition
- Query Output
- Summary Email
- Preview Mode
- Column Formatting
- Exception Management
- Related Articles
Create a basic Query
To create the Query, right-click the Tests folder and select New > Query. Give it a name and press OK.
In this tutorial, we will create a Query that finds all sales orders in a sales system for $0. The cause is most likely a missing price on the product when the sales took place.
We have a SQL query against our sales data provider that will give us these sales. We select a data provider for the Query, paste the SQL into the query window and test if it runs ok.
In the properties pane, on the right, we can configure common properties like the description and timeout. It is important to set a good description to help people solve issues found with exMon.
A timeout can be helpful with larger queries or queries that are sometimes deadlocked. When the timeout is reached, exMon stops the Query and sends an error email to the Query Owner.
In query properties, we can set the query to return an error if the query returns a result or returns empty. In most cases, queries should be set to "If the query returns rows". For example, if a Query returns rows with sales orders with an amount of $0 - we want to know about them!
Sometimes, you might need the Query to be unsuccessful (return error code) if it does not return any rows. In most cases, this is used to control process flow, explained in detail below.
Query Results can be used in three ways in exMon:
- Queries can be configured to send out a Summary email with an Excel document containing the resulting rows. Often used when exceptions need to be fixed right away.
- The failure of a Query can be used to stop a process flow in exMon, for example stopping a process if some data does not reconcile or is missing.
- If a Query is configured to use Exception management, then exMon will create exceptions for each row from the Query results and only send out notifications when new exceptions are found. Visit Configuring Exception Management (Tutorial) for more information.
Summary emails are used to send a complete set of Query results to a predefined list of recipients, including an Excel document as an attachment.
You configure Queries to send summary emails by adding a new Email task under the Tasks tab as shown below. You can configure multiple recipients by separating them with a semi-colon.
For example, you can allow exMon to auto-generate the email or create the email text manually.
When configuring a Query, you can always use Preview mode to simulate an execution and show detailed information about what will happen when the Query is executed. Preview mode allows you to see results from the Query, all emails sent and how exceptions will be handled.
When previewing our Query, we can see the email generated in the Query Emails tab:
In the email preview, the results from the query can be improved. To make the data easier to understand and work with, we configure the SQL statement to include customer account information and salesperson names instead of their ids.
We can use Column Formatting to format the output.
With column formatting, you can reorder columns, hide columns, change column captions, sort columns and set formatting for number and date columns.
We give the columns more friendly captions and configure the format for all columns. We can now see in Preview Mode that the results are more presentable.
Basic Queries have no memory and in each execution, it sends the full results of the Query.
The Query can be made more intelligent by enabling Exception Management. Exception Management tracks each row from the results as an exception, keeping track of its lifespan and only notifying users of new exceptions.
You can read more about Exception Management in Configuring Exception Management (Tutorial) and Using the exMon Continuous Monitoring Portal (Tutorial).