In this tutorial we will create a Query in the exMon Administration Client.
Queries in exMon fetch data from a single data provider and all rows returned from the Query are considered a violation of a business rule; for example sales orders in a POS with amount of $0.
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 systems with amount of $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 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.
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 configure whether the Query should be viewed as resulting in error if the results from the Query contains rows or if it does not contain any rows.
In most cases queries should be set to "If query returns rows". For example if a Query returns rows with sales orders with amount of $0.
In few cases you 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:
- A Query can be configured to send out a Summary email with an Excel document containing resulting rows. Often used when exceptions needs 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 notification 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 ;. For example: firstname.lastname@example.org;email@example.com
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.
With preview you can see results from the Query, all emails sent and how exceptions are handled.
When previewing our Query, we can see the email generated in the Query Emails tab:
In the email preview we saw that we can improve the results from the Query. To make the data easier to understand and work with, we configure the SQL to include customer account information and sales person 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 a 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 it's lifespan and only notifies users of new exceptions.
You can read more about Exception Management in Configuring Exception Management (Tutorial) and Using the exMon Continuous Monitoring Portal (Tutorial).