Thursday, February 14, 2013

User Profile Sync Issues - MOSS 2007, SharePoint

Errors had been popping up in our event log for some time and no resolution had been found by opening tickets with Microsoft so I embarked on another epic analysis and you get the cream off the top.

The user does not exist or is not unique

We were seeing errors in the event viewer that looked like:
failure trying to synch site <GUID> for ContentDB <GUID> WebApp <GUID>.  Exception message was The user does not exist or is not unique

Now right off the bat, I should let you know that we cheat a little and have developed some SQL Server Integration Services (SSIS) scripts that gather all these super helpful guid numbers and link them to fairly useless discripters like database names, web app URLs, site URLs...  So I just take the GUID and dump it in my search routine and voila - I know which site this little timer job is harping about.  You may need to query (never access the SharePoint database directly... blah blah blah) the objects table in the config database then query the sites table in the content database to find your site.

Analysis:

This was likely caused by user IDs that were formatted with an @ symbol instead of the common AD format with the \.  IE:  userid@domain (wrong) vs. domain\userid (right).  The assumption is that these IDs were migrated in from WSS 2.0.
Neither of the supported methods for removing the malformed ID worked:
·         STSADM -o deleteuser –url <siteurl> -userlogin <userid>
·         Using the API: SPUserCollection.Remove(<userid>)
The user ID had to be transformed to a supported format before it could be deleted properly from SharePoint using the API.  The user ID is stored in two places – the UserInfo table and the AllUserData table.  Site collection users are also members of a list.  Both locations must be updated with the corrected user ID.

Solution: Transform IDs to Supported Format

The following query will transform all malformed IDs to the supported format in all content databases on a database server (test, test, test, test... I recommend testing this in non-prod first.  test, test, test):
-- These series of statements will transform IDs with @ symbols in them to
-- the standard format ms\userid
drop table #UserIdsSel
create table #UserIdsSel (u_db varchar(128), u_siteid uniqueidentifier, id bigint, t_login varchar(255), u_type varchar(12))
exec sp_msforeachdb @command1 = 'print "?"; use [?]
create table #UserIds (u_db varchar(128), u_siteid uniqueidentifier, id bigint, t_login varchar(255), u_type varchar(12))
insert #UserIds
select db_name() as dbname
  ,tp_siteid
  ,tp_ID
  ,''<YOURDOMAIN>\'' + parsename(replace(tp_login,''@'',''.''),2 )as NewLogin
  ,''@ Removed''
from userinfo
where tp_ExternalTokenLastUpdated is NULL
 and tp_deleted = 0
 and tp_domaingroup = 0
 and tp_Login like (''%@%'')

 update UserInfo set tp_Login = t_login
 from #UserIds u
 where tp_SiteID = u.u_siteid
      and tp_ID = id

update AllUserData set nvarchar3 = t_login
 from #UserIds u
 where u.u_siteid = tp_SiteId
      and u.id = tp_ID
      and tp_contenttype = ''Person''

insert #UserIdsSel
select * from #UserIds

drop table #UserIds'

select * from #UserIdsSel
Once the IDs have been transformed to a supported format, they can be deleted without error.  Since they were malformed in the first place, they could not have been used to access SharePoint.

Duplicate User IDs in the Site Collection

We discovered that there were some site collections with duplicate user ID values.  The API should prevent this from happening but this is likely due to a migration.  We seem to be moving corruption from place to place instead of cleaning it up as we go.

Analysis:

The STSADM command and the API both operate against the first ID.  The code assumes the user ID is unique so the second value is missed.  The duplicate ID needs to be converted to a new value so the API can “see” it and handle it correctly.

Solution: Transform IDs to Unique IDs by Adding ‘zzz’

The duplicate ID needs to be transformed to a new value.  The following query will process any non-deleted, inactive duplicate ID in all content databases on the database server - you may need to go back and reread the assumptions I made if the query doesn't work for you:
--Transform duplicate ids to new values
drop table #UserIdsSel
create table #UserIdsSel (u_db varchar(128), u_siteid uniqueidentifier, id bigint, t_login varchar(255), u_type varchar(12))
exec sp_msforeachdb @command1 = 'print "?"; use [?]
create table #UserIds (u_db varchar(128), u_siteid uniqueidentifier, id bigint, t_login varchar(255), t_type varchar(12))
insert #UserIds
select db_name() as dbname
      ,u1.tp_siteid
      ,u1.tp_ID 
      ,u1.tp_Login + ''zzz''
      ,''Altered''
from userinfo u1 with (nolock) inner join
userinfo u2 with (nolock) on u1.tp_siteid = u2.tp_siteid
      and u1.tp_login = u2.tp_login
      and u1.tp_id <> u2.tp_id
where u1.tp_ExternalTokenLastUpdated is NULL
 and u1.tp_deleted = 0
 and u1.tp_domaingroup = 0

update UserInfo set tp_Login = t_login
 from #UserIds u
 where tp_SiteID = u.u_siteid
      and tp_ID = id

update AllUserData set nvarchar3 = t_login
 from #UserIds u
 where u.u_siteid = tp_SiteId
      and u.id = tp_ID
      and tp_contenttype = ''Person''

insert into #UserIdsSel
select * from #UserIds

drop table #UserIds'

Duplicate Key Value

Message: Cannot insert duplicate key row in object 'dbo.UserMemberships' with unique index 'CX_UserMemberships_RecordId_MemberGroupId_SID'. The duplicate key value is (<RecordID>, <MemberGroupID>, <SID>).
This error occurred on the Web Front End but actually refers to the SSP database.  While performing a User Profile Sync, a recursive membership stored procedure is fired off.  The query used to determine the uniqueness of the new row is different than the unique clustered index resulting in a failure to insert the row.
My analysis below returned 10,912 unique RecordIDs out of 175,602 in the UserMemberships table with 120,851 missing a valid recursive membership.  All have an SID.  These 120,851 are cases where we would get the error from the Event Viewer.  The record count in UserMemberships is 3,583,335.

Analysis:

I'm guessing this is due to some sort of corruption in the SSP.  I removed one of the failing entries from the UserMemberships table only to have it fail on another entry.  I pulled the info from the SSP Database stored procedures and I believe I have found where it occurs:
Stored Procedure:  membership_updateRecursiveMemberships
BEGIN
INSERT INTO UserMemberships (RecordId, SID, MemberGroupId, GroupType, GroupTitle, PolicyId, ItemSecurity)
SELECT IM.MasterRecordId, NULL, IM.MemberGroupId, @GroupType_DistributionList, N'', @DLSourceId, @DefaultItemPrivacy
FROM #MembershipsFromImport IM
LEFT JOIN UserMemberships UM ON UM.RecordId = IM.MasterRecordID AND UM.MemberGroupId = IM.MemberGroupId
WHERE UM.MemberGroupId IS NULL
SET @ErrorCode = @@Error
END
This query defines what to insert into the table:
SELECT DISTINCT U.MasterRecordId, G2.[Id], G2.SourceReference, G2.cs_SourceReference
FROM #UsersToConsider U
INNER JOIN UserMemberOf V WITH (NOLOCK) ON U.RecordId = V.RecordId
INNER JOIN MemberGroup G WITH (NOLOCK, INDEX=IX_MemberGroup_Source_SourceReference) ON V.cs_SourceReference = G.cs_SourceReference AND V.SourceReference = G.SourceReference AND G.Source = @DLSourceId
INNER JOIN MembershipRecursive R WITH (NOLOCK) ON G.[Id] = R.GroupId
INNER JOIN MemberGroup G2 WITH (NOLOCK) ON R.ParentGroupId = G2.[Id]

So I did some research on how I might be able to find some corruption.  I pulled the values above and did a union all on UserMemberships to see the differences:
SELECT DISTINCT U.MasterRecordId, G2.[Id], G2.SourceReference, G2.cs_SourceReference
FROM UserProfile_Full U
INNER JOIN UserMemberOf V WITH (NOLOCK) ON U.RecordId = V.RecordId
INNER JOIN MemberGroup G WITH (NOLOCK, INDEX=IX_MemberGroup_Source_SourceReference) ON V.cs_SourceReference = G.cs_SourceReference AND V.SourceReference = G.SourceReference
INNER JOIN MembershipRecursive R WITH (NOLOCK) ON G.[Id] = R.GroupId
INNER JOIN MemberGroup G2 WITH (NOLOCK) ON R.ParentGroupId = G2.[Id]
where U.RecordID = 321984
union all
select UM.RecordId
      ,UM.MemberGroupId as id
      ,NULL
      ,NULL
from UserMemberships UM
where UM.RecordId = 321984
order by id asc

When I performed this query, I found that the "valid" user memberships had a record in both cases.  However, the "invalid" memberships were missing a row from the first case.
So I wrote another query to find all "invalid" memberships in the SSP and I was blown away by how many hits I had:
drop table #groupMembership
create table #groupMembership (
      record bigint,
      mgroup bigint
      )
insert into #groupMembership
SELECT DISTINCT U.MasterRecordId, G2.[Id]
FROM UserProfile_Full U
      INNER JOIN UserMemberOf V WITH (NOLOCK) ON U.RecordId = V.RecordId
      INNER JOIN MemberGroup G WITH (NOLOCK, INDEX=IX_MemberGroup_Source_SourceReference) ON V.cs_SourceReference = G.cs_SourceReference AND V.SourceReference = G.SourceReference
      INNER JOIN MembershipRecursive R WITH (NOLOCK) ON G.[Id] = R.GroupId
      INNER JOIN MemberGroup G2 WITH (NOLOCK) ON R.ParentGroupId = G2.[Id]

select um.RecordId
         ,sum(case when SID IS NULL then 1 else 0 end) NULLSid
         ,sum(case when SID IS null then 0 else 1 end) WithSid
from UserMemberships  UM with (nolock)
where um.id not in
      (select um.id
      from UserMemberships UM with (nolock) inner join
            #groupMembership gm on UM.RecordId = gm.record and UM.MemberGroupId = gm.mgroup)
and UM.RecordId <> -1
group by UM.RecordId
order by UM.RecordId

Likely Solution:  Delete Database Sync Information

All memberships that seemed to be causing failures were manually deleted with another series of SQL queries but the correct method is likely the stsadm sync command.  Microsoft Premier support claims that the sync command will clear the SSP of these relationships and cause them to be recreated on the next firing of the “Profile Synchronization” timer job which by default runs every hour.  The sync timer frequency can be changed using the -SyncTiming option - M:5 would set it to run every 5 minutes.

stsadm -o sync -listolddatabases 0

Shared Service Provider SharedServices1
ID: e51f0d3e-85cc-498a-b330-04a2ccfc1086  Synchronized: 2/5/2013 8:55:06 AM
ID: 401a8778-32de-4836-82db-085191159ce7  Synchronized: 2/5/2013 8:55:07 AM
ID: ea06156e-27a5-4bb5-a3c5-0ae046083930  Synchronized: 2/5/2013 8:55:06 AM
ID: d82336e9-71c1-40eb-8c41-10793f7100db  Synchronized: 1/22/2013 8:50:07 AM
ID: 5f93b1cd-0510……

stsadm -o sync -deleteolddatabases 0

Deleted sync information for DB e51f0d3e-85cc-498a-b330-04a2ccfc1086
Deleted sync information for DB 401a8778-32de-4836-82db-085191159ce7
Deleted sync information…..

Wednesday, January 9, 2013

SharePoint Scheduled Alerts Deconstructed

This post contains:

  • Links To Other Alert Troubleshooting Articles
  • Why Scheduled Alerts Mysteriously Stop Working
  • What The Immediate Alerts Timer Job Does (Five Stored Procedures)
  • How To Find Alerts That Didn't Fire
  • The Moral Of The Story
I've spent the past two weeks scouring the internet, tracing SQL, extracting table DDL and stored procedures from SharePoint content databases to finally crack the scheduled alert code. I've generated some SQL queries you can use to find failing alerts, force alerts to re-fire, and determine the type of each alert.

The SharePoint scheduled alert design hasn't changed much from WSS 3.0. This analysis applies to WSS 3.0, MOSS 2007, SharePoint 2010 and I suspect SharePoint 2013 but haven't verified it.   All my research and data analysis have lead to four words:

Synchronize your server times.

If your database server and WFEs are not sync'd to the time server, your scheduled alerts will fail. No setting changes, property changes, timer restarts, moving DB processing to another WFE... or whatever will fix your problem. And the next time you do a reboot, time will sync up and things will start working again. Maddening.

 

Links To Other Alert Troubleshooting Articles


I'm going to start with credit to some of the posts that helped me along before I get into my stuff.
I can sum most of this up fairly quickly.
  • Scheduled alerts are run via the Immediate Alerts timer job.
  • The Timerlock table in each database tells you which WFE runs timer jobs for it.
  • The property "alerts-enabled" must be set to true for each URL.
  • The property "job-immediate-alerts" tells you when the job runs, typically "every 5 minutes between 0 and 59".
  • Daily and weekly alert subscriptions are both stored in the SchedSubscriptions table - NotifyFreq column value of 1 = daily, 2 = weekly.
  • All activity on the site is stored in the EventCache table.
  • When a subscription is set on an EventCache row, an EventLog entry is created the next time the "Immediate Alerts" job runs from the timer.
  • Since immediate alerts fire immediately (hence the name) there is no need to store up alerts for a daily or weekly summary.

 

Why Scheduled Alerts Mysteriously Stop Working 


This is where we kept getting lost. There was nothing wrong with any part of the email or event processing architecture. Immediate alerts worked fine, scheduled alerts didn't go out. We needed a deeper understanding of what was really happening.
  • Scheduled alert subscriptions require an intersection entity (many to many relationship) and use the EventSubsMatches table to join on the EventCache Event ID and the SchedSubscriptions Subscription ID.
  • When the "Immediate Alerts" timer runs and the SchedSubscription NotifyTime has been past by the SQL server current time, all alerts joined to the subscription EventsSubsMatches table are summarized and sent out to the subscriber.
  • The processed rows are removed from EventSubsMatches.
  • The subscriptions where the NotifyTime has been passed by the WFE time are incremented by one unit (day, week).
Notice that the alerts are processed using the SQL server time and the subscriptions are incremented by the WFE time? If your SQL server time is running behind the WFE, it will fail to find any alerts to process (less than 24 hours) and then another stored procedure comes along and pushes the subscription forward like it was processed. This is why my subscriptions weren't going out.

If you turn on verbose for the Timer ULS log, you will see that the process is running fine but not finding anything to send out.  You watch your logs and you capture:
Begin invoke timer job Immediate Alerts, id {81E07A90-327B-471A-A3A6-7A7672F1D905}, DB {7ABFF5BD-7E23-43FE-B399-F6980AA3E954}
It's working!  You say to yourself...
AlertsJob loaded 0 of 0 event data records
AlertsJob loaded 0 of 0 subscription records
Alertsjob results for immediate delivery: 0 prematches, 0 passed filtering, 0 of 0 passed security trimming, 0 final after rollup
Alertsjob results for scheduled delivery: 0 prematches, 0 passed filtering, 0 of 0 passed security trimming, 0 final after rollup
AlertsJob processed 0 daily notifications in 0 digests, sent 0 emails, failed to send 0 emails
AlertsJob processed 0 weekly notifications in 0 digests, sent 0 emails, failed to send 0 emails
Wait, I know I subscribed to alerts and I SHOULD have seen some!  Why does it say "0" everywhere?

 

What The Immediate Alerts Timer Job Does


Time to fire up the SQL Profiler and run a trace.  I've captured the stored procedures that the timer job uses.  Now we can really see what goes on.

proc_GetEventDataAndSubscriptionFilters
This takes entries from EventCache table, processes immediate alerts
Updates EventLog
Updates EventBatch with timestamp and last processed EventCache Item ID
Nulls out the EventData and ACL columns in EventCache
proc_EnumSubscribedSites
Pulls a list of Site GUIDs that have subscriptions
proc_MatchSchedSubscriptions
For each site with subscriptions, pull the list of scheduled subscriptions that should fire
This one has a where clause that blocks my failed subscriptions
EventLog.EventTime  >= @EventTime - CASE
                    WHEN (SchedSubscriptions.NotifyFreq = 1)
                    THEN 1.0
                    ELSE 7.0
                    END
proc_DeleteEventLog
For each site collection, delete from EventSubsMatchs
Where EventLog.EventTime < current time
Delete from EventLog
proc_UpdateSchedSubscriptionTimes
Adds either a day or a week to NotifyTime and NotifyTimeUTC

As you can see, I tested all the where clauses for proc_MatchSchedSubscriptions and found that this particular one was selecting against my scheduled alerts.

Then, proc_UpdateSchedSubscriptionTimes procedure runs after everything - there is no two phased commit, no logic to ensure it didn't miss anything - just an increment by one to get ready for the next summary.
This is probably a good time to say:

 "Microsoft doesn't support any manual manipulation of the SharePoint content databases and recommends only using the API."  

So, proceed at your own risk :D.

 

How To Find Alerts That Didn't Fire


Open up your SQL Management Studio, connect to your data server, and run this query

Look at your results tab and you will see all the databases where scheduled alerts that should have been sent - weren't.  You will also see another curiosity - alerts with no e-mail addresses.  Why would those be there?  You can't send an alert unless you have an e-mail address right?  This cracks me up.  You can create an alert in the GUI even if your site collection user profile doesn't have an e-mail address.  However, the stored procedure where clause in proc_MatchSchedSubscriptions blocks those entries.  The user THINKS they are going to get alerts, nothing tells them they won't on the front end, and SharePoint just ignores them on the back end.  Isn't that nice?


Ok, you now have database names with failing alerts, now open a new query window and run all these queries at once against the DB in question. This is an older screen shot:
 
Hmmm, you say. What next? Well, I found out that if you push the Schedule back one unit (day, week), it will fire again the next time "Immediate Alerts" timer runs. Also, if there is no e-mail address, you can blow them away (UNSUPPORTED!!).  If you want to try your luck, you can use these queries.  I found this would fire off my alerts 90% of the time but they would fail again on the next rotation - because the server times were off.

The moral of this story:  

 

"Make sure all your system times are in sync and you can use the summary queries I provided to watch your alerts magically correct themselves as they cross the immediate alert timer boundary."

SharePoint Alerts - Force Scheduled Alerts, Remove Bad Alerts

--Push All Old Subscriptions
update SchedSubscriptions
  SET
        NotifyTime    = CASE WHEN (NotifyTime    IS NOT NULL) THEN NotifyTime    ELSE  @Now END - CASE WHEN (NotifyFreq = 1) THEN 1.0 ELSE 7.0 END,
        NotifyTimeUTC = CASE WHEN (NotifyTimeUTC IS NOT NULL) THEN NotifyTimeUTC ELSE  @Now END - CASE WHEN (NotifyFreq = 1) THEN 1.0 ELSE 7.0 END
  where Id in (select ss.Id
            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
            WHERE (DATEDIFF(Hour,EventLog.eventtime,SchedSubscriptions.NotifyTimeUTC) > (24 * (CASE SchedSubscriptions.notifyfreq when 1 then 1.0 else 7.0 END))));
           
--Delete subscriptions with missing e-mail addresses
SET NOCOUNT ON;
DECLARE @sub uniqueidentifier;
DECLARE sub_cursor CURSOR FOR
    select ss.Id
    from UserInfo as ui inner join
    SchedSubscriptions as ss on ui.tp_ID = ss.UserId
    where ss.UserEmail = N'';
OPEN sub_cursor

FETCH NEXT FROM sub_cursor
INTO @sub
WHILE @@FETCH_STATUS = 0
BEGIN
    delete from SchedSubscriptions    where Id = @sub
    delete from EventSubsMatches    where SubId = @sub
    PRINT 'Deleting subscription with missing e-mail address ' + CAST(@sub as varchar(40))
    FETCH NEXT FROM sub_cursor
    INTO @sub
END
CLOSE sub_cursor;
DEALLOCATE sub_cursor;

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 '

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
    ,LockedBy
    ,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
    ,ec.SiteId
    ,esm.eventid
    ,esm.subid as SubscriptionID
    ,ec.ItemName
    ,ec.ItemFullUrl
    ,el.eventtime as EventTime
    ,ss.NotifyTime as SubscriptionNotifyTime
    ,w.TimeZone
    ,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
    ,ss.UserEmail
    ,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;