Creating Email Alerts

Some customers have asked to be alerted when certain criteria are met in the Argos database. This article shows how to utilize SQL Database Mail to create alert scripts for the following scenarios:

• Users have pending activity records for more than four days
• Activity has not been recorded for a device for more than four days

Additional alert scripts can be provided as a professional service through Sepialine Support. Contact us for more details.

Set up Database Mail

Argos alerts are generated through SQL Server's Database Mail component. Database Mail is not enabled in SQL Express, so you must be using the full version of SQL 2012 or 2014.

You will need to create a Database Mail profile if one does not already exist.

1. Open SQL Management Studio, connect to your SQL instance and browse to Management>Database Mail.
2. Right-click and choose "Configure Database Mail"
   
3. Click "Next"
4. Choose "Set up Database Mail by performing the following tasks" and click "Next"
   
5. Create a profile with the name "ArgosAlerts"
   
6. Click "Add" under "SMTP Accounts"
7. Click "New Account"
8. Enter "ArgosAlerts" as the Account Name
   
9. Use a valid email address to send accounts (we recommend creating "ArgosAlerts@yourcompany.com"
10. Set Display name as "Argos Alerts"
11. Enter mail server name in "Server name" field
12. Set SMTP Authetntication settings as required by your mail server
13. Click "OK"
14. Click "Next"
15. Select your Profile Name from the Public Profiles list and click "Next"
    
16. Review the System Parameters and change as desired (we recommend changing Account Retry Attempts from 1 to 2)
    
17. Click "Next"
    
18. Click "Finish"
19. Click "Close"
20. Send a test email to verify that the system is functioning. Right-click Management>Database mail and choose "Send Test Email"
    
21. Verify that you receive the test email

Set Argos Administrator email address

the Alert scripts send an email to the Argos administrator as defined in the Argos Manager. If this should be sent to multiple users, we recommend creating an email distribution address in your mail server and adding the desired recipients in there.

1. Click Settings>General
   
2. Enter email address in Administrator email field
3. Click "OK"

Download Alert Scripts

Alerts are stored in a separate database. Use the following scripts to create the database and generate the alert emails. Save these files to C:\Argos7Installers\Alerts.

CreateAlertDatabase.sql

UnbilledUsers.sql

IdleDevices.sql

Create Argos7Alerts Database

1. Open SQL Management Studio and connect to the server/instance that contains your Argos database
2. Log in using an account that has rights to create databases
3. Open CreateAlertDatabase.sql
4. Click "Execute". This will create a new database within your SQL Server instance called "Argos7Alerts", and generate two tables within that database - "UnbilledJobs" and "IdleDevices"

Create Unbilled Users script

The Unbilled Users script will send an email alert when any user has pending activity that has not been billed for more than four days.

1. Open SQL Management Studio and connect to the server/instance that contains your Argos database
2. Open UnbilledUsers.sql
3. Click "Execute"

Create Idle Devices script

The Idle Devices script will send an email alert when any device has not had activity for more than four days.

1. Open SQL Management Studio and connect to the server/instance that contains your Argos database
2. Open IdleDevices.sql
3. Click "Execute"

 

Create a SQL Agent task to automate the alert generation

The final step is to create tasks that will generate the email alerts on a schedule.

Enable SQL Server Agent

The SQL Server Agent is not started by default. You must enable it to run scheduled tasks.

1. Click Start>Run and type Services.msc
2. Locate the SQL Server Agent service for your SQL instance
   
3. Double-click and change the Startup Type to "Automatic"
   
4. Change the Service status to "Started"
   
5. Click OK and close Services

Create a SQL Agent job

1. Open SQL Management Studio and right-click on SQL Server Agent>Jobs
2. Choose "New Job..."
   
3. Name the job "Idle Devices"
4. Click "Steps" from the left hand menu
5. Click "New"
   
6. Name the step "Run Script"
7. Set Type to "Transact-SQL script (T-SQL)
8. Set Database to Argos7Alerts
9. Click "Open..." and locate IdleDevices.sql. This will populate the command screen with the SQL script
   
10. Click OK
11. Click Schedules
12. Click New
    
13. Enter a name, such as "Daily"
    
14. Change "Frequency" to "Daily"
15. Choose a time that does not interfere with mail server maintenance and SQL backup schedules
16. Click OK
17. Note that the Daily schedule is selected. Multiple schedules can be defined if desired
    
18. Click OK and to exit the SQL job settings