Scheduled Alerts
Definition
Alerts are monitoring agents for key IT activities within the company, e.g. assets, employees, tickets, etc. They can send notification emails and perform actions automatically in the database by running SQL scripts.
EndDefinition
- The configuration to be defined will depend on the type of alert.
- When enabled, it will run based on the frequency defined.
- Alerts can be stored in folders that are organized by function.
Types of scheduled alerts
Standard alerts
- They are built around a parent query.
- A filter enables you to extract only the records you want.
- They are triggered when a condition reaches a specified threshold.
- You can insert a link to a view in the notification email. This will give recipients direct access to the forms of records.
example Trigger an alert when any contract renewal date reaches D-60 ==> Notification sent to managers with a link to the view displaying all contracts where a renewal decision must be made.
Ratio alerts
- They are based on a ratio calculated using two other alerts.
- They are triggered when this ratio reaches a specified threshold.
example Ratio calculated by dividing the number of late incidents (alert 1) by the number of open incidents (alert 2) ==> Notification sent to managers when the ratio reaches 30%
Automatic alerts
- These are simple notifications that do not have any trigger condition or threshold.
- They are defined in the same way as ratio alerts.
example Gap Analysis by License Version ==> Notification sent to managers indicating that the table is updated
Notes
- You can create or modify an alert as long as you are authorized to access the Administration > Alerts > Scheduled Alerts menu.
- The sender of the notification email and the sender's email address must be specified in the A_PARAMETERS table.
- Schema: A_COMPANY
- Table: A_PARAMETERS
- Fields to modify:
- MAILER_SCHEDULER: Specify the sender name, e.g. EVO_SCHEDULER
- MAILER_SCHEDULER_ADDR: Specify the sender's email address, e.g. scheduler@name_of_customer.com
- If the execution of an alert is not triggered, check that the Scheduler is started and correctly configured.
Standard alerts
- Parent Query:
- You can only create it without any specific user rights when creating an alert.
- You can modify it as long as you are authorized to edit display presentations.
See User Profiles > Edit rights and display rights.
- Manually added fields will be available for all reporting tools and purge processing.
- They will not be available for parent queries used to define standard Service Manager objects.
- Access a view via an email link:
Caution
- When an alert performs the update or deletion of records:
- Define a filter to ensure that only the relevant records are processed.
- In the filter, specify the incident numbers to be processed to avoid processing other records inadvertently.
- If there is an error in the SQL script, you cannot undo the action.
- If you deleted records inadvertently, you must restore the database.
- If you modified records inadvertently, you can run another SQL query to revert back to the initial state.
Best Practice
- Before enabling an alert that contains an SQL script, create a report to check that the records displayed in the report are effectively the ones to be processed when the alert is run. This report also provides a history of the state of data before and after the alert is run.
- In the report, define the filter to be applied to the alert.
- Run the report and check that the records displayed are the ones to be processed by the alert.
- Copy the SQL script from the report filter and paste it in your alert. To do this, switch to Advanced Mode in the filter editor.
- Once the alert is run, run the report to check that the script updated the relevant data correctly.
example Control report run prior to the alert that modifies the status of incidents (change Open status to Closed, change On Hold status to Reopen)
- When you create an alert, you should backdate its start date so that it will run immediately. This enables you to check the results immediately without waiting for the first execution date.
example
- Current Date: 10 February.
- First execution date: 1 March.
==> In the Next Compute Date field, enter the first day of the month prior to the current date, i.e. 1 February.
- If the running of alert B depends on the completion of alert A whose processing time is unknown (due to large data volumes, access time, etc.), we recommend that you schedule the tasks in a sequence instead of defining a specific time for running alert B.
- When the alert triggers a notification email, you should associate a view so that recipients can click the link in the email and access the view easily.
Ensuring optimal network performance
- Schedule the running of complex alerts during periods where users do not need to access Service Manager, i.e. at night or on weekends.
- If you work in an international organization with different service hours for each country, you may want to run alerts in a specific environment, instead of in the production environment.
Characteristics specific to versions 2016 and earlier
The alerts in versions 2016 and earlier are different.
- Edit rights are assigned to each user profile in the module associated with the menu item using the alert.
See User Profiles.
Menu access
Administration > Alerts > Scheduled Alerts
Alerts that are enabled appear in Administration > Scheduled Tasks > Scheduled Alerts.
Screens description
Main Information
Name: Name that identifies the alert.
Comment: Comment field for describing the alert.
Folder Name: Name of the alert folder where the alert is saved.
Parent Query (Note: Only for Standard alerts): Name of the parent query used to define the alert.
- You can click
to add a query only when creating a new alert. Do not select the parent query.
- The fields available in an existing parent query are parent query fields and other fields manually added to Service Manager internal reporting tools and purge processing.
Form (Note: Only for Standard alerts): Name of the form to be opened when users click a record in the view accessed via the link in the notification email.
- Only forms linked to the parent query will be available.
- Mode for opening the form:
- Integrated: The record will be displayed in Form mode. Users can modify it based on their rights.
- Pop-up: Information specific to the record will appear in a secondary window. It cannot be modified.
Alert Type: Type of alert. This will determine its configuration and its calculation method.
- Standard: This calculates the alert based on the number of records retrieved by the filter. It can also be the sum or the average of field values for the records retrieved by the filter.
- Ratio: This calculates the alert using the values of two other alerts.
- Automatic: No calculation if performed.
Filter
Note: Only for Standard alerts.
Filter used to extract the data to be included in the alert's trigger condition. See Visual filter editor.
- All fields in the alert's parent query as well as other manually added fields will be available for the filter.
Value to be calculated
Note: Universal Time (UTC): Current date and time expressed in Universal Time (UT).
Frequency: Used to define the frequency for running the alert.
- The frequency will be applicable after the date specified in the Next Compute Date field.
- The parameters depend on the selected frequency.
- Every [N] Months / Day / At (Hour): Define the relevant frequency using Universal Time (UT).
- Depends on: Used to sequence the running of two alerts successively. Select the alert whose processing precedes the execution of your alert from the drop-down list.
Next Compute Date (Note: Only if the frequency is not Depends on): Date and time at which the alert should next run.
- The date initializes the start of the frequency.
example Frequency: Every seven days at 7 am; the next compute date is a Saturday ==> The alert will run every Saturday
- You can also update the date using the Enable wizard.
Enter a date earlier than the current date in order to run the alert immediately.
Alert Condition / Result (Note: Only for Standard or Ratio alerts): Value triggering the alert.
example
- Alert will be triggered once more than 20 PCs are in stock: 20 < Result < ...
- Alert will be triggered once less than 10 PCs are in stock: ... < Result < 10
- Alert will be triggered once there are 5 to 10 open incidents: 5 <= Result <= 10
Dividend / Divisor (Note: Only for Ratio alerts): Alerts used to calculate the percentage value of the current alert.
- Percentage value of the alert = 100 * (Dividend alert / Divisor alert).
- You must first define the alerts.
example More than 30% of unsolved incidents alert
- Dividend alert = Number of late incidents = 18
- Divisor alert = Number of open incidents = 45
> Percentage value of the alert
100 * (18 / 45) = 40% late incidents
Number of Records / Totals / Average (Note: Only for Standard alerts): Used to indicate the calculation method of the alert.
- You can only choose one option.
- The Totals and Average options can only apply to numeric fields in the parent query.
- Number of Records: The result is equal to the number of records that satisfy the filter criteria of the alert.
example Number of incidents created this week alert
- Result = 61 ==> 61 incidents created this week
- Totals: The result is equal to the sum of values of a field for records in the filter.
example Total intervention time for immediate resolution incidents today alert - Total calculated using the Solution Delay field
- Snapshot of today's incidents: Four immediate resolution incidents with an intervention time of 10 minutes; one with an intervention time of 30 minutes
- Result = (4 * 10 min) + (1 * 30 min) = 70 ==> Total intervention time is equal to 70 minutes.
- Average: The result is equal to the average of values of a field for records in the filter.
example Average late time for incidents solved today alert - Average calculated using the Late field
- Snapshot of today's incidents: Four solved with a delay of 1 hour; one solved with a delay of 5 hours; one solved with a delay of 3 hours
- Result = [(4 * 1h) + (1 * 5h) + (1 * 3h)] / 6 incidents = 2 ==> Average late time for incidents solved is equal to 2 hours.
Alert
To (Employees) / To (Groups) / To (Roles): List of recipients of the notification email when the alert reaches the trigger threshold.
- These recipients must be listed in the Employee Directory or Group Directory.
- They can also be defined using system or customized roles.
- You must select one or more recipients or roles.
- Each recipient must be separated using the semicolon, ;.
Title: Subject of the notification email sent to recipients.
Message: Body of the email sent to recipients.
SQL Script: Script describing the operations to be performed on the database when the alert reaches the trigger threshold.
- Caution: If there is an error in the SQL script, you cannot undo the action.
Topic / Description related to the incident or request to create: When an alert triggers the automatic creation of an incident or service request, the topic or description here will automatically be loaded in the ticket.
Add View (Note: Only for Standard alerts): Used to create a view listing the records affected by the alert. See the procedure.
- The view can be accessed via the link included in the notification email.
- Recipients defined using roles will not receive the link to the view.
- Only recipients authorized to access the menu associated with the alert can access this view.
Procedures and Wizards
How to create an alert
Step 1: Create the new alert.
1. Select Administration > Alerts > Scheduled Alerts in the menu.
2. Click + New.
Step 2: Specify the main information on the alert.
1. Enter the main information on the alert.
2. (Note: Only for Standard alerts) Select the parent query.
- You can click
to create a new parent query. You must not select an existing query.
See How to create a parent query.
- You can add fields to a parent query.
- Select the parent query associated with the new alert.
- Click
next to the field.
The list of fields from the parent query associated with the alert and other fields manually added to Service Manager internal reporting tools and purge processing will appear. - Add the fields you want.
See How to modify a parent query.
3. Select the type of alert.
The configuration to be defined will depend on the type of alert.
4. Click Next.
Step 3: Define the conditions for the filter used by the alert.
Note: Only for Standard alerts.
1. Define the filter conditions. See Visual filter editor.
- All fields in the alert's parent query as well as other manually added fields will be available.
2. Click Next.
Step 4: Configure the alert.
Note: The configuration to be defined will depend on the type of alert.
1. Define the frequency.
2. (Note: Only for Standard or Ratio alerts) Specify the trigger condition.
3. (Note: Only for Ratio alerts) Select the two alerts (dividend and divisor) used to calculate the percentage value.
4. (Note: Only for Standard alerts) Specify the calculation method of alert.
- Select Number of Records.
or - Select Totals or Average for one of the numeric fields in the parent query.
6. Click Next.
Before moving on to the next step, test the alert by clicking Calculate to check the trigger condition.
Step 5: Complete the notification email and define the SQL script.
1. Specify the recipients of the notification email.
2. (optional) Define the SQL script to be run when the alert is triggered.
Step 6: Associate a view with the notification email.
Note: Only for Standard alerts.
1. Click Add View.
The view editor will appear.
2. Select the menu associated with the alert in the Theme field.
Only recipients of the notification email authorized to access the menu can see this view.
CreateViewAlertReporting_Procedure
2. Define the order of the columns in the view.
- Select the Order box next to the field to be moved.
- Click Insert Here next to the field above which you want to move the selected field.
3. Specify the options depending on the type of field.
- Only for date fields: Select the way you want to group records from the Group by drop-down list.
example You want a quick view of all equipment whose warranty expires in 2021:
- End of Warranty field = 9/1/2021
- Grouping By Year = 2021
- Only for tree fields that are not in the last level: Indicate the first and last level of the hierarchy to be displayed.
- The value 0 is used to display all grouping levels in the hierarchy.
example Display the tree field, Location = North America/USA/New York/Manhattan/Broadway
- Start = 0, End = 0 ==> North America/USA/New York/Manhattan/Broadway
- Start = 1, End = 3 ==> USA/New York/Manhattan
- Start = 4, End = 5 ==> Manhattan/Broadway
- Location (last level) = Broadway
- The value 0 is used to display all grouping levels in the hierarchy.
- Select the measure type you want, i.e. number of records, totals, average.
4. Select the grouping level of the view using the Breakpoint column.
All fields up to this level are marked as Visible.
5. Select the fields you want to display in the view.
Step 7: Save the alert.
1. Click Finish.
The list of alerts will be refreshed.
Step 8: Enable the new alert.
1. Select Administration > Alerts > Scheduled Alerts in the menu and select the relevant alert.
2. Run the Enable wizard.
3. Enter the first calculation date, click Next and click Finish.
4. Check that the alert is enabled.
- Select Administration > Scheduled Tasks > Scheduled Alerts in the menu.
- Check that the alert appears in the list of scheduled tasks.
How to create an alert using a template
Step 1: Select the template alert.
1. Display the list of alerts.
2. Click next to the template.
The properties window of the alert will appear.
Step 2: Create the new alert.
1. Modify the relevant information in the template alert.
2. Click Save as to avoid overwriting the template alert. Give a name to the new alert.
How to check if a scheduled alert is enabled or disabled
Step 1: Check if the scheduled alert is enabled or disabled.
1. Select Administration > Alerts > Scheduled Alerts in the menu.
2. Display the List view.
3. Look at the Enabled box for the relevant scheduled alert.
- If the box is checked, the scheduled alert is enabled.
- If the box is not checked, the scheduled alert is disabled.
Step 2: Check that the corresponding scheduled task exists.
1. Select Administration > Scheduled Tasks > Scheduled Alerts in the menu.
2. Look for the scheduled alert.
- If the scheduled alert appears in the list, it is scheduled to run based on the frequency defined.
- If the scheduled alert does not appear in the list, it is not scheduled to run.
Wizards
Use case: Creation of a standard alert, Employees due to leave the company
The alert will be triggered when employees are due to leave the company.
- A notification email will be sent to the Stock Manager.
- The email will contain a link to a view displaying the list of all employees due to leave the company and the list of equipment to be returned.
Implementation:
- Select the Employees parent query.
- Extract data using the filter, Departure Date = Next Month.
- Configuration of the alert:
- Select the calculation method of the alert, = Number of Records ==> The result is estimated using the number of employees due to leave the company next month.
- Alert condition: >= 1 ==> The alert will be triggered once the result is greater than or equal to 1, i.e. once one or more employees are due to leave the company.
- Alert execution
- Frequency: Every month, on the first day of the month at 6 am.
- Next Compute Date: 1 February.
==> From 1 February onwards, the alert will run on the first day of every month at 6 am. Once one or more records are returned, an email will be sent to the Stock Manager.
Examples of SQL queries run by an alert
Delete a list of incidents | ||
---|---|---|
Context | SQL Query | |
|
DELETE SD_REQUEST WHERE REQUEST_ID IN (#LIST_ID#) Note: The #LIST_ID# tag refers to a list of records that have been filtered. |
|
Close a list of incidents and modify their status | ||
Context | SQL Query | |
|
UPDATE SD_REQUEST SET SD_REQUEST.STATUS_ID = XX WHERE SD_REQUEST.REQUEST_ID IN (#LIST_ID#) Note: The *#LIST_ID# tag refers to a list of records that have been filtered.
|
|
Add an end date to actions associated with incidents when the date is unspecified | ||
Context | SQL Query | |
|
UPDATE AM_ACTION SET AM_ACTION.END_DATE_UT = getutcdate() WHERE AM_ACTION.REQUEST_ID IN (#LIST_ID#) AND AM_ACTION.END_DATE_UT is null Note: getUTCDate() corresponds to the current date and time. |
|