Regular alerts are custom alerts that are automatically sent via email. The content of the alerts is optional, e.g. they can be alerts about upcoming expiration of software licenses, upcoming end of warranty period of certain devices, etc.

Regular alerts can be managed by members of the Administrators group in WebApp - Administration - Regular Alerts.

The basis of each alert definition is a single SQL script that returns a table with message parameters and sent data. The data is always part of the email text and can optionally be attached as a CSV file.

SQL script

TheSQL script of the created alert is executed periodically according to the set retry period.

The script return table must contain the following mandatory columns:

Column	Description
From	The sender of the message, e.g. Alvao DontReply <do-not-reply@domain.com>. A NULL value means that the notification is sent from the set message sender of the Alvao system. A user account, that is used to send messages through the SMTP server must have permission to send messages on behalf of that sender.
This	A list of message recipients separated by semicolons, e.g. "user1@domain.com; user2@domain.com".
Cc	List of recipients of a copy of the message
Bcc	Hidden message copy recipient list
Subject	Message subject
Text	Message text. The text may use html tags to format the text.

The return table is first grouped by message parameters (mandatory columns). The resulting list represents individual sent emails. The text of each email then contains a data table containing the values from the other columns of the table from all rows with the given values of the mandatory items.

Note:

If an email contains more than 50 recipients in any field (recipient, copy, hidden copy), it is sent in batches of up to 50 recipients per field.

Note:

The notification is only sent when the SQL script returns some rows. If the entity list is empty, and the user still wants to be notified, the script must be written so that it always returns some rows.

Tip:

Alerts can optionally be evaluated over an analytics database instead of a production database. This is especially recommended for alerts that take a long time (tens of seconds or more) to execute the SQL script.

Caution:

Each periodic alert is triggered in a transaction with an isolation level of ReadUncommited. Therefore, in extreme cases, the script may work with data that a user is currently editing.

Examples

Licences with an expiry date approaching

The query below generates alerts for software licenses that will expire within the next 30 days.

The sender of the notification is the message sender. of the Alvao system. Recipients of notifications are all users who have permission to read an organization's licenses.

The license table will contain the license's Name, Inventory Number, Expiration From, Number and Expiration To.

declare @advanceDays int = 30
declare @sender nvarchar(255) = dbo.GetProperty('Alvao.StandardSenderAddress')

select 
    @sender as [From],
    stuff(
    (
            select cast(';' as varchar(max)) + P.sPersonEmail 
            from tPerson P
            join vLicRight_Read RR on RR.PersonId = P.iPersonId 
	    WHERE P.dPersonRemoved is null and P.bPersonAccountDisabled = 0 and ISNULL(P.sPersonEmail, '') != ''
            for xml path('')
	    ), 1, 1, '') as [To],
    null as [Cc],
    null as [Bcc],
    N'License about to expire' as [Subject],
    N'Hello,<br/><br/>the licenses listed below will expire within the next 30 days.' as [Text],
    LH.txtLicName as [License Name],
    LH.txtInventoryNum as [License inventory number],
    LH.dteDate as [Validity from],
    LH.dteDateExpire as [Validity to]
from tblLicHist LH
where LH.RemovedDate is null 
	and (LH.dteDateExpire is not null and LH.dteDateExpire between GETUTCDATE() and DATEADD(day, @advanceDays, GETUTCDATE()))

Computers with an upcoming end of warranty

The query below generates alerts for computers whose warranty will expire within the next 30 days.

The sender of the notification is the sender of the message of the Alvao system. Recipients of notifications are all users who are members of the Asset Administrators group.

The table of computers will include their Name, Type, Path in the tree and Warranty Expiration Date.

declare @advanceDays int = 30
declare @kindName nvarchar(255) = N'Warranty expiration'
declare @sender nvarchar(255) = dbo.GetProperty('Alvao.StandardSenderAddress')
declare @toGroupName nvarchar(255) = N'Asset managers'

select 
    @sender as [From],
    stuff(
        (
        select cast(';' as varchar(max)) + P.sPersonEmail
        from tPerson P
            join tRolePerson RP on RP.liRolePersonPersonId = P.iPersonId
            join tRole R on R.sRole=@toGroupName and R.iRoleId = RP.liRolePersonRoleId
        WHERE P.dPersonRemoved is null and P.bPersonAccountDisabled = 0 and ISNULL(P.sPersonEmail, '') != '' 
        for xml path('')
        ), 1, 1, '') as [To],
    null as [Cc],
    null as [Bcc],
    N'Computers with impending end of warranty' as [Subject],
    N'Hello,<br/><br/>the computers listed below will expire their warranty within the next '+cast(@advanceDays as nvarchar)+' days.' as [Text],
    N.txtName as [Name],
    N.txtClass as [Computer kind],
    N.txtPath as [Path in tree],
    PK.txtValue as [Warranty expiration]
from vNodeClass N
    join vPropertyKind PK on PK.lintNodeId = N.intNodeId and PK.txtKind = @kindName
where N.bolHidden = 0 and N.bolScrapped = 0
    and PK.txtValue is not null
    and (CONVERT(datetime, PK.txtValue, 103) between GETUTCDATE() and DATEADD(day, @advanceDays, GETUTCDATE()))    
    and N.bComputer=1

Changes in RAM size on active computers in inventory

The query below generates an alert for objects for which the RAM Size (GB) property was changed during the previous calendar month.

The sender of the notification is the message sender of the Alvao system. Recipients of notifications are all users who are members of the Asset Administrators group.

The object table will contain the Change Date, Object Name, Type, Path in Tree, Change Message and the Name of the user who made the change.

The asset managers will receive a corresponding message even if no changes have been made during the past month.

select 
    NULL as [From],
    stuff((
            select cast(';' as varchar(max)) + P.sPersonEmail 
            from tPerson P
            join tRolePerson RP on RP.iRolePersonId = P.iPersonId 
            join tRole R on R.RoleBehaviorId = 11 and R.iRoleId = RP.liRolePersonRoleId
            WHERE P.dPersonRemoved is null and P.bPersonAccountDisabled = 0 and ISNULL(P.sPersonEmail, '') != ''
            for xml path('')
	    ), 1, 1, '') as [To],
    null as [Cc],
    null as [Bcc],
    N'Computers with resized RAM' as [Subject],
    N'Hello,<br/><br/>The objects listed below have had their RAM size changed within the last month.<br/>Please check if this change was justified.' as [Text],
    L.dteLog as [Date],
    N.txtName as [Object Name],
    N.txtClass as [Type],
    N.txtPath as [Path in tree],
    L.txtLog as [Message],
    P.sPerson as [User]
into #Result
from tblLog L 
join vNodeClass N on L.lintNodeId = N.intNodeId
join tblKind K on K.intKindId = L.KindId
join tPerson P on P.iPersonId = L.liLogPersonId
where N.bolHidden = 0 and N.bolScrapped = 0 and N.bComputer = 1 
    and K.intKindCode = 147 and L.lintLogKindId = 4
    and (DATEPART(MONTH, L.dteLog) = (DATEPART(MONTH, DATEADD(MONTH, -1, GETUTCDATE()))))

if exists(select 1 from #Result) 
    select * from #Result
else
select 
    NULL as [From],
    stuff(
    (
        select cast(';' as varchar(max)) + P.sPersonEmail 
        from tPerson P
        join tRolePerson RP on RP.iRolePersonId = P.iPersonId 
        join tRole R on R.RoleBehaviorId = 11 and R.iRoleId = RP.liRolePersonRoleId
        WHERE P.dPersonRemoved is null and P.bPersonAccountDisabled = 0 and ISNULL(P.sPersonEmail, '') != ''
        for xml path('')
        ), 1, 1, '') as [To],
    null as [Cc],
    null as [Bcc],
    N'Computers with resized RAM' as [Subject],
    N'Hello,<br/><br/>no object has had its RAM size changed in the last month.' as [Text]

drop table #Result

Problem solving

If some notifications are not being sent as they should, check the following points:

In the WebApp - Administration- Regular Alerts table, select the alert in question. If an error occurred the last time it was evaluated, you will see the text of the error message in the preview on the right.
In SQL Server Management Studio, run the SQL script manually and check that it returns some data and that it returns all the required columns.
Check the messaging settings.

Did not find what you were looking for? Ask our technical support team.