Create a Query (Tutorial)

 

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

To create the Query, right click the Tests folder and select New > Query. Give it a name and press OK.

 

1_create_a_query

 

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.

2_with_sql

 

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.

 

Trigger Condition

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 Output

Query Results can be used in three ways in exMon:

  1. 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.
  2. 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.
  3. 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 Email

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: person1@example.com;person2@example.com
You can allow exMon to auto generate the email or create the email text manually.

 

3_trigger_email

 

 

Preview Mode

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:

4_preview

 

 

Column Formatting

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.

5_sql_review

 

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.

6_column_formatting

 

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.

7_column_formatted

 

 

Exception Management

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).

 

 

Related articles

Configuring Exception Management (Tutorial)

Scheduling Objects (Tutorial)

Configuring Data Providers (Tutorial)

exMon Contact Us