SharePoint 2016 Get All User Alerts By List Name

In this post, we will learn how to Get All User Alerts Per List in SharePoint 2019/2016 and SharePoint 2013.

---

SharePoint 2016 Get All User Alerts By List Name

In SharePoint Server, if you have tried to get all Alerts for a specific user.(Site Settings > Site Administration > User Alerts.)

You will note that you can get all alerts per User. however, there is no way to know what’s the SharePoint list in which this alert has been configured!

How to Get All User Alerts Per SharePoint List in SharePoint?

Actually, in SharePoint 2019/2016/2013, there are no OOTB settings or GUI interface that you can go through to be able to get All user alerts details categorized by SharePoint List Name. However, as a workaround solution, you can get all alerts for a particular user per List Name from SharePoint Content Database by following the below steps:

1. Get All alert fields.
2. Get the SharePoint Content database that hosts the current site.
3. List all tables and fields that used to store Alert info in the SharePoint Content Database.
4. Finally, build your Alert SQL query.

1) The fields required to create an Alert in SharePoint?

To create an alert in SharePoint, you should provide the below info:

1. Alert Title: Meaning name for the new alert, this is included in the subject of the notification sent for this alert.

2. Send Alert To: Specify who will receive the alert.

3. Delivery Method: Specify how you want the alerts delivered by Email or SMS.

4. Change Type: Specify the type of changes that you want to be alerted to.

5. Send Alert for these changes: Specify which criteria you want to receive the alerts.

6. When To Send Alerts: Specify how frequently you want to be alerted

2) Get the SharePoint Content Database

All alerts fields are already stored in the content database. So you will need to get the SharePoint content database that host the current site, by running the below SharePoint PowerShell cmdlet:

1. Open SharePoint Management Shell as Administrator.

2. Run the below cmdlet

Get-SPContentDatabase -Site http://SiteCollectionURL

Output

Id               : 182a9a2f-a18e-4e9e-8e3f-1460cfe5569c
Name             : WSS_Content
WebApplication   : SPWebApplication Name=SharePoint - 80
Server           : EPM\EPMDB
CurrentSiteCount : 1

Now, we get the content database of the current site. below, we will show where the alert info is stored in this SharePoint content database.

3) Where’s the alert fields stored in the SharePoint Content database?

The Alerts in SharePoint are stored in Content Database, specifically at ImmedSubscriptions and SchedSubscriptions Tables.

• If the “When to Send Alert Option” is set to Send notification immediately, the alert info will be stored in the ImmedSubscriptions table.

• Otherwise, the alert info will be stored in the SchedSubscriptions table.

Get Alert Title

The “Alert Title” is stored in ImmedSubscriptions table, AlertTitle column.

Get Send Alert To

The “Send Alert To” is stored in ImmedSubscriptions table, UserId column. and to get the corresponding UserName of this UserId, you should query the UserInfo table in the SharePoint content database.

Get Alert Delivery Method

The “Alert Delivery Method” is stored in ImmedSubscriptions table, DeliveryChannel column.

• If the DeliveryChannel equal to 1, so the delivery method is “Emails”.
• Else the delivery method is “SMS”.

Delivery Channel	Value
Emails	1
SMS	2

Get Alert Change Type

The “Alert Change Type” is stored in ImmedSubscriptions table, EventType column.

Event Type	Value
All Changes	-1
New Items are Added	1
Existing items are modified	2
Items are deleted	4

Get Send Alert for these changes

The “Send Alert for these changes” is stored in ImmedSubscriptions table, Filter column but sometimes, this value is empty, so you can get this value from ImmedSubscriptions table, Properties column at “filterindex” property.

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>

Send Alert for these changes	Value
Anything changes	0
Someone else changes an item	1
Someone else changes an item created by me	2
Someone else changes an item last modified by me	3

Get When To Send Alerts

The “When To Send Alerts” is stored in ImmedSubscriptions table, NotifyFreq column.

• If NotifyFreq equal to 1, Send a daily summary.
• Else, Send a weekly summary.

When To Send Alerts	Value
Send a daily summary	1
Send a weekly summary	2

If we have earlier mentioned, Alerts that set to Send notification immediately are stored in the ImmedSubscriptions table.

4) Query All User Alerts Per SharePoint List

After we know where exactly the user alert info is stored in the content database, we are ready now to get All User Alerts per SharePoint list through the below query.

-- Get All User Alerts per List Name using SQL
USE PMO_Content_DB
 -- Specify the content database name
SELECT UserInfo.tp_Title AS UserName,
       UserInfo.tp_Login,
       ImmedSubscriptions.SiteUrl + ImmedSubscriptions.WebUrl AS SiteURL,
       ImmedSubscriptions.SiteUrl + ImmedSubscriptions.WebUrl + ImmedSubscriptions.ListUrl AS ListURL,
       ImmedSubscriptions.WebTitle,
       ImmedSubscriptions.ListTitle,
       ImmedSubscriptions.AlertTitle,
       CASE
           WHEN ImmedSubscriptions.AlertType = 0 THEN 'List Alert'
           ELSE 'List Item Alert'
       END 'Alert Type',
           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 ',
           CASE
               WHEN ImmedSubscriptions.DeliveryChannel = 1 THEN 'EMAIL'
               ELSE 'SMS'
           END AS 'Delivery Method',
           'Send notification immediately' AS 'When to Send Alerts',
           CASE
               WHEN CAST(ImmedSubscriptions.Properties AS XML).value('(/miscellaneous/property/@value)[2]', 'int')= 0 THEN 'Anything changes'
               WHEN CAST(ImmedSubscriptions.Properties AS XML).value('(/miscellaneous/property/@value)[2]', 'int')= 1 THEN 'Someone else changes an item'
               WHEN CAST(ImmedSubscriptions.Properties AS XML).value('(/miscellaneous/property/@value)[2]', 'int')= 2 THEN 'Someone else changes an item created by me'
               WHEN CAST(ImmedSubscriptions.Properties AS XML).value('(/miscellaneous/property/@value)[2]', 'int')= 3 THEN 'Someone else changes an item last modified by me'
           END AS 'Send Alert for these changes',
           ImmedSubscriptions.Status
FROM ImmedSubscriptions
INNER JOIN UserInfo ON ImmedSubscriptions.UserId = UserInfo.tp_ID
UNION ALL
SELECT UserInfo.tp_Title AS UserName,
       UserInfo.tp_Login,
       SchedSubscriptions.SiteUrl + SchedSubscriptions.WebUrl AS SiteURL,
       SchedSubscriptions.SiteUrl + SchedSubscriptions.WebUrl + SchedSubscriptions.ListUrl AS ListURL,
       SchedSubscriptions.WebTitle,
       SchedSubscriptions.ListTitle,
       SchedSubscriptions.AlertTitle,
       CASE
           WHEN SchedSubscriptions.AlertType = 0 THEN 'List Alert'
           ELSE 'List Item Alert'
       END 'Alert Type',
           CASE
               WHEN CONVERT(varchar, SchedSubscriptions.EventType) = '-1' THEN 'All changes'
               WHEN CONVERT(varchar, SchedSubscriptions.EventType) = '1' THEN 'New items are added'
               WHEN CONVERT(varchar, SchedSubscriptions.EventType) = '2' THEN 'Existing items are modified'
               WHEN CONVERT(varchar, SchedSubscriptions.EventType) = '4' THEN 'Items are deleted'
               ELSE CONVERT(varchar, SchedSubscriptions.EventType)
           END AS 'Change Type ',
           CASE
               WHEN SchedSubscriptions.DeliveryChannel = 1 THEN 'EMAIL'
               ELSE 'SMS'
           END AS 'Delivery Method',
           CASE
               WHEN SchedSubscriptions.NotifyFreq = 1 THEN 'Send a daily summary'
               ELSE 'Send a weekly summary'
           END AS 'When to Send Alerts',
           CASE
               WHEN CAST(SchedSubscriptions.Properties AS XML).value('(/miscellaneous/property/@value)[2]', 'int')= 0 THEN 'Anything changes'
               WHEN CAST(SchedSubscriptions.Properties AS XML).value('(/miscellaneous/property/@value)[2]', 'int')= 1 THEN 'Someone else changes an item'
               WHEN CAST(SchedSubscriptions.Properties AS XML).value('(/miscellaneous/property/@value)[2]', 'int')= 2 THEN 'Someone else changes an item created by me'
               WHEN CAST(SchedSubscriptions.Properties AS XML).value('(/miscellaneous/property/@value)[2]', 'int')= 3 THEN 'Someone else changes an item last modified by me'
           END AS 'Send Alert for these changes',
           SchedSubscriptions.Status
FROM SchedSubscriptions
INNER JOIN UserInfo ON SchedSubscriptions.UserId = UserInfo.tp_ID

Output

Download the full SQL Query from GitHub at SharePoint 2016 Get All User Alerts By List Name

Now, we can filter by ListTitle to retrieve all users alerts for a specific list in SharePoint site.

WHERE (ImmedSubscriptions.ListTitle = 'The Title of your List')

Warning: Do not modify the content database in any manner, any content database modification is unsupported. however, it’s supported to read all data in SharePoint databases.

---

Get All User Alerts Per SharePoint List Programmatically

Besides the above query, you can also create a SharePoint Solution to get all alerts for a specific list in your SharePoint solution using C# SSOM.

Download the C# code sample from GitHub at SharePoint 2016 Get All User Alerts By List Name Programmatically.

---

Applies To

• SharePoint 2019.
• SharePoint 2016.
• SharePoint 2013.

Download

• Download the SQL Query and C# code sample to get the SharePoint User Alerts Details from GitHub at SharePoint 2016 Get All User Alerts. Please, don’t forget to follow me to get the latest updates!

See Also

• Missing Alert Me button in SharePoint 2016
• Missing Alerts And Reminders Project Server 2016
• PowerShell Script: SharePoint Farm Scan Report