Alert if audit log is missing for last 24 hours

Is there any possibility to achieve this. Not able to figure out what condition should I put under where clause so that I can receive an alert incase audit log is missing for say last 24 hours or 12 hour depending upon the requirement

Find more posts tagged with

npm

audit log

Accepted answers

All comments

mnsh_majumder

I did setup one small query for Health check report as below but the challenge is we can't modify the select statement while creating the alert based on SQL

SELECT
case when count (timeloggedutc)>0
then 'Audit is present for last 12 hours'
else 'Audit is not present for last 12 hours'
end as Audit_Log_Record_Status
FROM AuditingEvents
where timeloggedutc>(DATEADD(hour, -12, (select DATEADD(hour, -6, getutcdate()))))

mnsh_majumder

Have been working on this part.

I have set the trigger condition as below & modified the sql condition for email as below so that I'll receive alert if audit is not present for last 30 min's (including evaluation time as 15 min)

SELECT
case when count (timeloggedutc)>0
then 'Audit is present for last 30 minutes'
else 'Audit is not present for last 30 minutes'
end
FROM AuditingEvents
where timeloggedutc>(DATEADD(minute, -30, (select DATEADD(hour, -6, getutcdate()))))