Get summary of all alerts from single SPUser

Question

I am trying a way to get all alerts from all sites from a SPUser in a summarized email. Is there any way to achieve this or I have to fetch each SPSite individually?

Thank you very much

Answer 1

You need to simply go through site, iterate through each web within the web loop through each alert, then check to see if its the same user you only want, print to string or what ever you want :)

string myUserLogin = @"MyDomain\MyUser";

SPSite oSite = SPContext.Current.Site;
SPWebCollection collWebsites = oSite.AllWebs;
foreach (SPWeb oWebsite in collWebsites)
{
    SPAlertCollection collAlerts = oWebsite.Alerts;

    foreach (SPAlert oAlert in collAlerts)
    {
        if (oAlert.User.LoginName == myUserLogin)
        {
            //do your code here...... print to label ect...
            //display the title of each site
            string siteTitle = oWebsite.Title;
            //display name of the list and item to which each alert applies
            string listTitle = oAlert.Title;
        }
    }
}

http://msdn.microsoft.com/en-us/library/microsoft.sharepoint.spalertcollection(v=office.12).aspx

to send an email create a seperate method that you call after the loop has finished using the SPUtility method:

How to send E-Mail using SPUtility.SendEmail

http://www.sharepointdiary.com/2012/01/how-to-send-e-mail-using.html

so it will look somthing similar to this:

protected override void createchildcontrols()
{
    StringBuilder strbuild = new StringBuilder();
    string listResults;
    string myUserLogin = @"MyDomain\MyUser";

    SPSite oSite = SPContext.Current.Site;
    SPWebCollection collWebsites = oSite.AllWebs;

    foreach (SPWeb oWebsite in collWebsites)
    {
        SPAlertCollection collAlerts = oWebsite.Alerts;

        foreach (SPAlert oAlert in collAlerts)
        {
            if (oAlert.User.LoginName == myUserLogin)
            {
                //do your code here...... print to label ect...
                //display the title of each site
                string siteTitle = oWebsite.Title;
                //display name of the list and item to which each alert applies
                string listTitle = oAlert.Title;
                strbuild.Append("Site Title" + siteTitle + " List Name:" + listTitle).AppendLine();
            }
        }
    }

    strbuild.Insert(0, "The User "+ myUserLogin+ " has alerts assigned to the following: ").AppendLine();

    listResults = strbuild.ToString();
    sendEmail(listResults)
}


private sendEmail(string List)
{
    using (SPSite oSPSite = new SPSite("https://sharepoint.com"))  //Site collection URL
    {
                using (SPWeb oSPWeb = oSPSite.OpenWeb("News"))  //Subsite URL
                {

                    StringDictionary headers = new StringDictionary();

                    headers.Add("from", "sender@domain.com");

                    headers.Add("to", "receiver@domain.com");

                    headers..add("bcc","SharePointAdmin@domain.com");

                    headers.Add("subject", List);

                    headers.Add("fAppendHtmlTag","True"); //To enable HTML format

                    System.Text.StringBuilder strMessage = new System.Text.StringBuilder();

                    strMessage.Append("Message from CEO:");

                    strMessage.Append("<span style='color:red;'> Make sure you have completed the survey! </span>");

                    SPUtility.SendEmail(oSPWeb, headers, strMessage.ToString());

                }
     }
}

im sure ive propbably missed somthing out or done somthing wrong as im doing it all from memory and cant test it what so ever so its a proof of consept ;) but im sure there are some ways of probably doing it better but thats the first thing that has come to my mind :)

EDIT

the method above is very slow as it has alot of looping todo and will depend on how your doing it, for me I created this on a application as I can just show on another thread a loading screen(form) whilst it loops through and finishes :)

but if you cant wait or dont want to do that or not in an app than i would recommend you call the database that holds the values! more specifically your looking for the table

schedSubscription

within that it will contain all the alerts, you can query it by using this:

select * from [Content_DB].[dbo].[SchedSubscriptions] (nolock) where [UserEmail] = ‘User.email@domain.com’

have that in a stored procedure that you can call from a webpart or application or call directly (not recommended due to sql attacks), have a paramiter for user email to query against.

when you call the stored procedure pass the value of the user email to return the results for the user only!

from that you should get all the info you want from list name to site name and everything else :), you can then display it in a list or what ever you want!

This method is very very fast and a better option ;) ahh btw make sure the stored procedure only has read access and nothing more!

EDIT

USE [DBNameGoesHere]
GO
/****** Object:  StoredProcedure [dbo].[GetAlertsForUser]    Script Date: 01/18/2013 16:08:04 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
-- =============================================
-- Author:      <AlmostsharepointMaster>
-- Create date: <>
-- Description: <get all alerts assigned to user by email>
-- =============================================
Create PROCEDURE [dbo].[GetAlertsForUser]
    -- Add the parameters for the stored procedure here
    @Email varchar(256)

AS
select 
    *
from
    WSS_Content.dbo.SchedSubscriptions
where
    UserEmail = @Email

open sql server manager and open your DB the personal one not sharepoint DB. goto programmability folder and then stored procedures. rightclick stored procedure folder and select "new stored procedure". copy paste code from above instead of the generic code.

ok there is only two places you need to change, at the start change [DBNameGoesHere] to the DB that you store your common stored procedures and tables NOT the sharepoint DB is stored!!!

now goto from and change WSS_Content to the DB name to whatever its call that contains the SchedSubscriptions table to query.

Once you have done that you can compile and execute the code, you should see the new stored procedure within the Programmability folder under stored procedures, refresh the folder if you cant!

last step isto give permissions to that stored procedure (read only).

You can now test the stored procedure by right clicking it and select "execute stored procedure", you should get a box with one input.... on the right hand side under "value" insert the email you want to query and click ok.

You should see a list of alerts to only that user!

call this stored procedure from whereever you feel like be it from webpart or application ;)!

if you dont want all columns returned and only the ones you want just change

Select * 

to

Select columnName

so it will look like this

Select UserEmail, SiteUrl, WebUrl, ListTitle

that will only return thoes columns :)

to call a stored procedure follow:

http://stackoverflow.com/questions/10556354/call-stored-procedure-in-c-sharp

EDIT

You dont need to replicate the stored procedure on each DB for each web application, just ammend the current stored procedure to take in the new DB tables like so:

USE [DBNameGoesHere]
GO
/****** Object:  StoredProcedure [dbo].[GetAlertsForUser]    Script Date: 01/18/2013 16:08:04 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
-- =============================================
-- Author:      <AlmostsharepointMaster>
-- Create date: <>
-- Description: <get all alerts assigned to user by email>
-- =============================================
Create PROCEDURE [dbo].[GetAlertsForUser]
    -- Add the parameters for the stored procedure here
    @Email varchar(256)

AS
select 
    *
from
    WSS_Content.dbo.SchedSubscriptions,
     WSS2_Content.dbo.SchedSubscriptions,
     WSS3_Content.dbo.SchedSubscriptions,
     WSS4_Content.dbo.SchedSubscriptions
where
    UserEmail = @Email

this is getting from 4 different content DB so shows all from all web applications. This is the only method that i know of that is very fast as DB calls are extreamly fast compared to sharepoint (looping through spsite and spweb), if you have a dataaccess layer to call the stored procedure than its even more secure to call the stored procedure :),

     WSS_Content.dbo.SchedSubscriptions,
     WSS2_Content.dbo.SchedSubscriptions,
     WSS3_Content.dbo.SchedSubscriptions,
     WSS4_Content.dbo.SchedSubscriptions

I dont currently know if that is possible to have a master alert that alerts the user from all spsites. I would presume that involves connecting splist to one master list that contains an alert but it would be difficult. The solution i propose it relativly easy, 1 stored procedure that is connected to by webpart or application or list or feature that returns the result ;), as you stated you want to email them, I would go for the webpart method but you can setup a custom list that gets populated by the stored procedure with those values. The list would be created in a webpart and populated through that. This would also mean its only visible to the current user so only them can see their own alerts (all of them).

hope it helps :)

Answer 2

You can also get User's alerts through Powershell :

try this :

 if ((Get-PSSnapin "Microsoft.SharePoint.PowerShell" -ErrorAction SilentlyContinue) -eq $null) 
    {
        Add-PSSnapin "Microsoft.SharePoint.PowerShell"
    }
 $weburl = "http://dhaval-pc:2011"  
 $site = new-Object Microsoft.SharePoint.SPSite($weburl)
 $webs = get-spweb $weburl
 $UserLoginName = "i:0#.f|fbamembershipuser|jignesh"

 foreach($web in $site.AllWebs)
        {
            $SPuser = $web.EnsureUser($UserLoginName)
            if($SPuser -ne $null)
            {
                foreach ($SPAlert in $SPuser.Alerts)
                {
                    Write-Host Web : $web.Title and Alert Name : $SPAlert.Title -ForegroundColor Green
                }
            }

        } 

also be recursively call subwebs using $web.Webs.

Hope it helps!!!