In this chapter of the exMon walkthrough we go through how a user creates an exMon query that monitors all sales without a salesprice in Adventureworks.
First thing we do is create the query task in exMon
- Open exMon Administrator
- On the left side you see a treelist. Right click the node Tests, select New... and then Query
- In the popup type Sales without price
Basic query configuration
Next we want to create a SQL query that retrieves all sales orders without a price from the database Adventureworks.
- Select the data provider AdventureWorks. If the data provider has not been created, follow the Data Provider Walkthrough and then select it
- Paste the following SQL query into the textbox
,[Status],t2.AccountNumber as CustomerAccount,
t3.FirstName + ' ' + t3.LastName [SalesPerson]
FROM [Sales].[SalesOrderHeader] t1
inner join sales.customer t2 on t1.CustomerID = t2.CustomerID
inner join person.person t3 on t1.SalesPersonID = t3.BusinessEntityID
WHERE ISNULL(TotalDue, 0) <= 0
Now we have a very basic exMon query set up. Next thing we want to do is format the columns to make the query‘s output look good.
- Start by running the query by pressing F5
- In the Properties window on the right, press the Edit... button next to Column Formatting
- On the left panel, select the column OrderDate
- In the Caption textbox type Order Date
- In the Format As dropdown, select Date and Time
- On the left panel, select the column TotalDue
- In the Format As dropdown, select Number and check Use 1000 seperator
When this is done, our query‘s output will format our columns, so the OrderDate and TotalDue columns will have space in their caption and their value‘s will be formatted as a date/number.
Query description and action
Next we‘re going to write a description and an action for the query. We do this to make the process of fixing failures easier. The description field should as the name indicates, describe what the query does. The action field should give a detailed description of what actions need to be done, to resolve failures should they arise
- In the Description field, paste: Gives a list of sales that are missing a sales price in the database
- In the Action field, paste: Check the sales order and make sure it doesn‘t have a price. Send an email to the IT department containing the SalesOrderID and ask them to review the sale.
Our next job is to enable and configure the exception manager. When a query is ran and it returns results, the exception manager creates an exception for each row and keeps track of the exception until query results no longer contain the row.
Our first step in the exception manager is to enable it and decide the owner of each exception. The query owner is the default owner of all exceptions.
If you select user mapping, the owner of each exception depends on the row‘s user mapping column, where exMon tries to map the column‘s value to an exMon user. If the mapping fails, exMon by default makes query owner the exception owner. This can be overridden to automatically create a new exMon user and make it the owner of the new exception.
- Open the Exception Manager popup with the Common tab open
- Check the Enable Exception Manager
- In the Query owner dropdown, select your own exMon user
- In the User Mapping Type dropdown, select AdentureWorks Sales. If the type is not in the dropdown, please follow the External System walkthrough to create it
- In the User Mapping Column, select SalesPersonID
We‘ve now enabled user mapping, so the exception manager always tries to map the SalesPersonID column to an exMon user, using the AdventureWorks Sales mapping type. If the mapping fails, we will be the owner of the exception, since we‘re the query owner.
Next we‘re going to configure emails that the exception manager will send.
- Open the Exception Manager popup with the Emails tab open
- In the line Send exceptions email on, check New
- In the line Include these eceptions in email, check Open. New should be checked and disabled
- In the Email subject field, type Exceptions – Sales without price
- In the email template field, select Exception Manager Query
We‘ve configured the query‘s exception manager to send emails only when query creates a new exception that the user is the owner of. The email sent will contain all new and open exceptions the user is the owner of.
Notice: Similar to the user mapping to map who is the owner of an exception, you can also map who should receive emails as a CC using the CC mapping.
User and CC mapping are not the only ways to map a column. A column can also be mapped to Amount, Category, Unique Id, Start Date and Dimension. Each of these mappings serves a different purpose, so lets take a quick look at what those purposes are, before mapping using the mappings that we need for our query.
Amount – This mapping gives each exception a monetary value derived from the mapped column.
Unique Id –
Start Date – The column mapped to start date should contain a date that the datasource row was created.
Dimension – Dimension mapping behaves a lot differently than the other mappings. For the query itself it provides a lot more readability, where a column containing an userid can be replaced by there user‘s name instead. Mapping multiple queries on the same dimension can also provide a different view of exceptions created, from the perspective of the dimension, rather than the default look, which is from the perspective of a data type.
- Open the Exception Manager popup with the Mapping tab open
- For Amount mapping, select the column Total Due
- For Unique ID mapping, select the column Sales Order ID
- For Start Date mapping, select the column Order Date
- For the Customers dimension mapping, select the column CustomerID
- For the Sales Persons dimension mapping, select the column SalesPersonID
Exception’s primary key
exMon uses a unique primary key to differentiate exceptions from each other. The first time a primary key is returned in results, an exception is created. If the primary key comes up again, the exception is marked as open.
By default, primary key is a composition key made up from all the columns of the exception. It is strongly recommended to change from the default to either a single unique column, or a unique combination of columns.
- Open the Exception Manager popup with the Primary Key tab open
- Select the Custom radio box
- Check the SalesOrderID column
There are a few advanced exception manager features found in the Advanced tab, but we‘ll leave those for now and continue with configuring the query itself.
Next we want to take a look at the data type configuration. Data types are essentially groups that queries and compare queries register their exceptions as.
By setting up a detailed data type schema in exMon, the portal‘s overview will use the schema to create a easily readable treeview in the portal which groups queries and compare queries by data types. That way if you have fx. Data Integrity data type, all queries and compare queries that are data integrity checks will be found under that node in the portal‘s overview.
- Find the Data type field in the query‘s properties panel
- Set it‘s field to Sales.
- If Data Type dropdown doesn‘t contain Sales, please check the Data Type walkthrough to create it.
We‘re now going to create a query task that sends an email anytime a query is ran.
- Click the Tasks tab
- Press the New button in the toolbar
- Select the task type Email for the query task, and give it an appropriate name
- Type your own email address into the Email To field
- Make the Email subject be Sales without price results
- Select the style template Exception Manager Query
Trigger condition and return error
Our next job is to configure the conditions in which the query executes it‘s query tasks and how it finishes it‘s run successfully. There are 2 properties that control that behavior, the property Trigger Condition and Return Error.
If the trigger condition is met, the query tasks are executed. Furthermore, if the trigger condition is met and the Return Error property is set to Query Returns Results, the execution results in failure.
If a trigger condition is not met, the return error property is irrelevant and query finishes successfully. If a trigger condition is met, it depends on the return error property if the query finishes successfully or not.
- Find the Trigger Condition field in the query‘s properties panel
- Set Trigger Condition as If query returns rows
- Find the Return Error field in the query‘s properties panel
- Set Return Error as On Open Exceptions
Now anytime we execute the query, the only time the execution will be successful is when there are no open exceptions for the query, and it does not fail to finish execution(such as due to power outage).
Now the final step before our query is in production is to deploy it.
- Press the deploy button
- If Test enviroment is enabled, select the correct enviroment deployment
- Write a comment, detailing the changes being deployed
- Make sure to check Sales without price in the popup and press deploy