Custom Business Alerts

GP does a good job of giving you the ability to create business alerts to keep you informed of events that have or may occur based on conditions in your database. Sometimes, the need to create an alert outside of the functionality in GP does, amazingly, come up. Just today a post at http://groups.google.com/group/microsoft.public.greatplains/topics and a response by Polino @ DynamicsAccounting.net drove me to create a sample business alert using only T-SQL that you could use as a starting point to developing your own.

This alert, if scheduled to run periodically, will e-mail a list of users that have been logged into GP for longer than 12.5 hours or 750 minutes. It's pretty simple!

IF EXISTS
(
select datediff(mi,logindat+logintim, getdate()) as DURATION,--convert(datetime, convert(varchar(15), GetDate(), 114), 114) - LOGINTIM as DURATION,
USERID,
CMPNYNAM,
LOGINDAT,
LOGINTIM
from DYNAMICS.dbo.ACTIVITY
where datediff(mi,logindat+logintim, getdate()) > 750
)
BEGIN

DECLARE @SQL varchar(8000)

SET @SQL = 'select datediff(mi,logindat+logintim, getdate()) as DURATION,
USERID,
CMPNYNAM,
LOGINDAT,
LOGINTIM
from DYNAMICS.dbo.ACTIVITY
where datediff(mi,logindat+logintim, getdate()) > 750'

print @SQL

EXEC master.dbo.xp_sendmail @recipients = 'youralias@yourcompany.com',
@subject = 'Users Logged in beyond limit',
@message = 'Attached is a list of users that have been logged in beyond the limit',
@query = @SQL,
@attach_results = 'TRUE',
@width = 250
END