Get All User Alerts By List Name in SharePoint 2013

When I tried to retrieve all User Alerts for a specific list in SharePoint 2013 . by going to

  • Site Settings > Site Administration > User Alerts.

User Alerts.gif

but Unfortunately, I found out I can only get all alerts based on specific User not based on a specific list!

Filter User - User Alerts.gif

Finally , I believed that there’s not an OOTB solution to show all User Alerts for a Specific List in SharePoint 2013, So I decided to get it from Content Database by answering the following questions :

  • What’s the required information to create an alert in SharePoint ?
    • Alert Title.
    • Send Alert To (User Account).
    • Delivery Method.
    • Change Type.
    • Send Alert for these changes.
    • When To Send Alerts.

create-sharepoint-alert

  • Where have the Alerts been stored in SharePoint  ?
    • The Alerts in SharePoint have been stored in Content Database Specifically at ImmedSubscriptions and SchedSubscriptionsTable. based on When to Send Alert Option.
      when-to-send-alert
      • If it is Send notification immediately it will be stored at ImmedSubscriptions
      • else it will be stored at SchedSubscriptions
  • How can I get Alert Title in SharePoint Content Database ?
    • AtImmedSubscriptions.AlertTitle
  • How can I get Send Alert To in SharePoint Content Database ?
    • At ImmedSubscriptions.UserId
  • How can I get Alert Delivery Method in SharePoint Content Database ?
    • At    CASE WHEN ImmedSubscriptions.DeliveryChannel = 1 THEN 'EMAIL' ELSE 'SMS' END AS 'Delivery Method ', 
  • How can I get Alert Change Type in SharePoint Content Database ?

CASE WHEN CONVERT(varchar, ImmedSubscriptions.EventType) = '-1' THEN 'All changes'
WHEN CONVERT(varchar, ImmedSubscriptions.EventType) = '1' THEN 'New items are added'
WHEN CONVERT(varchar, ImmedSubscriptions.EventType) = '2' THEN 'Existing items are modified'
WHEN CONVERT(varchar, ImmedSubscriptions.EventType) = '4' THEN 'Items are deleted'
ELSE CONVERT(varchar, ImmedSubscriptions.EventType) END AS 'Change Type ',

  • How can I get Send Alert for these changes in SharePoint Content Database ?
    • At ImmedSubscriptions.Filter
    • Or from property name="filterindex" at ImmedSubscriptions..Properties

<miscellaneous><property name="dispformurl" value="Lists/LookupFilter/DispForm.aspx" /><property name="filterindex" value="1" /><property name="sendurlinsms" value="False" /><property name="mobileurl" value="http://epm/workflow/_layouts/15/mobile/" /><property name="eventtypeindex" value="-1" /><property name="siteurl" value="http://epm" /></miscellaneous>

  • How can I get When To Send Alerts in SharePoint Content Database ?
    • at case when SchedSubscriptions.NotifyFreq = 1 then 'Send a daily summary' else 'Send a weekly summary' end as 'When to Send Alerts'
  • Where have the Users been stored in SharePoint?
    • The Users in SharePoint have been stored in Content Database Specifically at UserInfo Table.
  • So I build this Query to can retrieve all users alerts in SharePoint site.
SELECT TOP (1000) UserInfo.tp_Title as UserName,
ImmedSubscriptions.SiteUrl + ImmedSubscriptions.WebUrl AS Site,
ImmedSubscriptions.WebTitle, ImmedSubscriptions.ListTitle,
ImmedSubscriptions.AlertTitle, 
Case when ImmedSubscriptions.AlertType=0 then 'List Alert' else 'List Item Alert'
 end AlertType,ImmedSubscriptions.AlwaysNotifyBit, ImmedSubscriptions.SystemBit,
ImmedSubscriptions.Status FROM ImmedSubscriptions 
INNER JOIN UserInfo ON ImmedSubscriptions.UserId = UserInfo.tp_ID 

The OutPut :

Alerts Query.gif

I can now filter by ListTitle to retrieve all users alerts for a specific list in SharePoint site. as the following

WHERE (ImmedSubscriptions.ListTitle = 'Doc')

Now I need to show this data in SharePoint. So I will Use BCS to build External List in SharePoint by following the mentioned steps below :

Note : At the following steps , I have considered that the Bussiness Connectivity Service has been configured.

Warning: Do not modify the content database in any manner other than using SharePoint or the DB will be considered as in unsupported mode. but You can read all data in SharePoint databases and still be in supported mode.

  • To avoid the above warning, try to create a new database , and create a SQL view.
  • Paste the following code into the SQL view > Save.
use [WSS_Content]
SELECT TOP (1000) UserInfo.tp_Title as UserName,
ImmedSubscriptions.SiteUrl + ImmedSubscriptions.WebUrl AS Site,
ImmedSubscriptions.WebTitle, ImmedSubscriptions.ListTitle,
ImmedSubscriptions.AlertTitle,
Case when ImmedSubscriptions.AlertType=0 then 'List Alert' else 'List Item Alert'
end AlertType,ImmedSubscriptions.AlwaysNotifyBit, ImmedSubscriptions.SystemBit,
ImmedSubscriptions.Status FROM ImmedSubscriptions
INNER JOIN UserInfo ON ImmedSubscriptions.UserId = UserInfo.tp_ID

Now you need to create an External Content Type and External List via SharePoint Designer by following the mentioned steps at Create an External Content Type via SharePoint Designer

Alternative Solution with Code

You can also create a SharePoint Solution > Visual WebPart and use the following code to get all alerts for a specific list.

public DataTable string AlertperList(SPWeb web, string listInternalName)
{
  string listUrl = web.ServerRelativeUrl + "/Lists/" + listaInternalName;
  listUrl = listUrl.Replace("//", "/");
  SPList list = web.GetList(listUrl);
  SPAlertCollection alertsColl = web.Alerts;
  DataTable dt = new DataTable();
  dt.Columns.AddRange(new DataColumn[3] { 
new DataColumn("UserName", typeof(string)),
new DataColumn("AlertType", typeof(string)), 
new DataColumn("DeliveryChannels",typeof(string)) });

 foreach (SPAlert alert in alertsColl)
    {
        if (alert.ListID == list.ID)
        {
          DataRow row = table.NewRow();
          row[UserName] = alert.User.LoginName.ToString(); 
          row[AlertType] = alert.AlertType.ToString();
          row["DeliveryChannels"] = alert.DeliveryChannels.ToString();
          dt.Rows.Add(row);
       }
    }
 return dt;
 }

Enjoy 🙂

Advertisements

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s