Wednesday, January 9, 2013

SharePoint Alerts - Failing Alerts, All Databases Summary Query

exec sp_msforeachdb @command1 = 'print "?"; use [?] select db_name() as dbname,
  dbo.Webs.FullUrl
        ,sum(CASE dbo.SchedSubscriptions.NotifyFreq
                WHEN 1 THEN 1
            END) as Daily
        ,sum(CASE dbo.SchedSubscriptions.NotifyFreq
                WHEN 2 THEN 1
            END) as Weekly
        ,SUM(CASE dbo.SchedSubscriptions.UserEmail
                WHEN N'''' THEN 1
                ELSE 0
                END) MissingEmail
        ,MAX(DATEDIFF(hour,EventLog.EventTime,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.0 else 7.0 END)))
GROUP BY dbo.SchedSubscriptions.NotifyFreq, dbo.Webs.FullUrl
ORDER BY dbo.SchedSubscriptions.NotifyFreq, dbo.Webs.FullUrl '

No comments:

Post a Comment