Wednesday, January 9, 2013

SharePoint Alerts - Failing Alerts Single DB Query

--Scheduled Alerts Query
-- Tom Sheffrey 1/3/2013
-- Find alerts that should have fired
-- Check for missing Join values

-- DB and Server Info
select DB_NAME() as DatabaseName
    ,getdate() as ServerTimeStamp
    ,dateadd(hour, -6,LockedTime) as LockedTimeCST from timerlock;

-- Failing alert summary
Select dbo.Webs.FullUrl
        ,count(CASE dbo.SchedSubscriptions.NotifyFreq
                WHEN 1 THEN 1
            END) as Daily
        ,count(CASE dbo.SchedSubscriptions.NotifyFreq
                WHEN 2 THEN 2
            END) as Weekly
        ,SUM(CASE dbo.SchedSubscriptions.UserEmail
                WHEN N'' THEN 1
                ELSE 0
                END) MissingEmail
        ,MAX(DATEDIFF(hour,EventLog.TimeLastModified,SchedSubscriptions.NotifyTimeUTC)) as OldestHours
FROM         dbo.EventCache with (nolock) INNER JOIN
                      dbo.EventLog with (nolock) ON dbo.EventCache.Id = dbo.EventLog.Id INNER JOIN
                      dbo.EventSubsMatches with (nolock) ON dbo.EventCache.Id = dbo.EventSubsMatches.EventId INNER JOIN
                      dbo.SchedSubscriptions with (nolock) ON dbo.EventSubsMatches.SubId = dbo.SchedSubscriptions.Id INNER JOIN
                      dbo.Webs with (nolock) ON dbo.EventCache.WebId = dbo.Webs.Id
WHERE (DATEDIFF(Hour,EventLog.eventtime,SchedSubscriptions.NotifyTimeUTC) > (24 * (CASE SchedSubscriptions.notifyfreq when 1 then 1 else 7 END)))
GROUP BY dbo.SchedSubscriptions.NotifyFreq, dbo.Webs.FullUrl
ORDER BY dbo.SchedSubscriptions.NotifyFreq, dbo.Webs.FullUrl;

-- Entries with NULL subscriptions
select COUNT(eventsubsmatches.eventid) as NullMatches
            from EventSubsMatches left outer join
                SchedSubscriptions on EventSubsMatches.SubId = SchedSubscriptions.Id
            where SchedSubscriptions.Id is NULL;
-- Detail records with EventType and Filter analysis
select w.FullUrl as WebUrl
    ,esm.subid as SubscriptionID
    ,el.eventtime as EventTime
    ,ss.NotifyTime as SubscriptionNotifyTime
    ,DATEDIFF(hour,ss.NotifyTime,ss.NotifyTimeUTC) as OffsetHour
    ,DATEDIFF(hour,el.eventtime,ss.NotifyTimeUTC) as Hours
    ,CASE ss.NotifyFreq
        when 1 THEN 'Daily'
        when 2 THEN 'Weekly'
    END as Frequency
    ,CASE ss.AlertType
        when 0 then 'List'
        when 1 then 'Item'
        else CAST(ss.AlertType as varchar(8))
    END as AlertType
    ,CASE ss.EventType
        when -1 THEN 'All Changes'
        when 1 then 'New items are added'
        when 2 then 'Existing items are modified'
        when 4 then 'Items are deleted'
        when 4080 then 'Web discussion updates'
        else 'Unknown'
    END as 'Change Type'
    ,CASE ss.Filter
        when N'' then 'Anything Changes'
        when '%Editor/New%' then 'Someone else changes a document'
        when '%Author%' then 'Someone else changes a document owned by me'
        when '%Editor/Old%' then 'Someone else changes a document modified by me'
        else 'Unknown'
    END as 'Send alerts for these changes'
FROM dbo.EventSubsMatches AS esm LEFT OUTER JOIN
     dbo.EventCache AS ec ON esm.EventId = ec.Id LEFT OUTER JOIN
     dbo.SchedSubscriptions AS ss ON esm.SubId = ss.Id LEFT OUTER JOIN
     dbo.EventLog AS el ON esm.EventId = el.Id LEFT OUTER JOIN
     dbo.Webs AS w ON ec.WebId = w.Id
WHERE (DATEDIFF(hour,el.eventtime,ss.NotifyTimeUTC) > (24 * (CASE ss.notifyfreq when 1 then 1.0 else 7.0 END)))
    or WebUrl is NULL
order by hours desc;

1 comment: