Delete all alerts for a user in Sharepoint with this PowerShell script

When a user leaves an organisation, their account is usually cleaned up – either by disabling or deleting it.  In Sharepoint this cleaning up is not automatic – generally Sharepoint hangs on to user accounts so that information about that user is still linked to the documents the user worked on. One of the side effects of this is that any alerts that user may have set up will remain, at least in Sharepoint 2007.  This leads to a lot of undeliverable messages going to Site Administrators.  Manually removing the alerts for a user requires visiting each site that has alerts, going to the site settings, User Alerts, finding the user and deleting their alerts.  I wrote the following PowerShell script to remove all alerts for a given user in one step. The script requires my supporting functions script (Download SPFunctions) – you need to save this in the same directory as the script below.  It works in a similar way to my Edit-SPAdmins script – it allows you to select a Web Application via GUID (don’t worry, it lists all the Web Applications and their GUIDs), and then loops through all the site collections and sites, removing the users alerts as it goes. So – on with the script:

# Remove alerts for a given user from all SharePoint sites in a web application
# Usage:  delete-spalert.ps1  
# Example:  delete-spalert.ps1 DOMAINUser

# Validate arguments
if ($args.Count -lt 1)
{
    Write-Warning "Usage: alerts.ps1 "
	Write-Warning "Username must be in DOMAINuser format"
    exit
}

# check that arguement is in correct format including domain
if ($args[0] -inotmatch '^w+[\]w+$')
{
    Write-Warning "Username must be in DOMAINUser format"
    exit
}

# Load Sharepoint functions
.SPFunctions.ps1;

# store arguement in variable
$myuser = $args[0]

# Preamble
cls;
write-host "###################################################################################

This application adds or removes alerts for a user in a specified web application
Here is a list of all Web Applications on the farm" -foregroundcolor yellow;

# List the GUIDs of all web apps on this farm
List-SPWebApp-GUID;

write-host "Please copy the GUID for the web application you wish to use by selecting then
clicking the right mouse button."  -foregroundcolor yellow;

write-host "
Note that the Central Admin site collection has no name,
but does have a GUID!  Don't select this one by accident!
" -foregroundcolor red;

Write-Host "You can paste the GUID by clicking the Right mouse button again" -ForegroundColor yellow;
# Prompt user for GUID to use
$guid = Read-Host -Prompt "GUID";
$guid = $guid.Trim();

# Check GUID for correct format
Write-Host "Checking GUID";
switch(Check-GUID($guid)){
	"False" { Write-Host "Error: Invalid GUID.  Exiting..." -ForegroundColor red; return;}
}

# Get the specified web app
$webapp = Get-SPWebApp($guid);

# Check that webapp has been got and if not inform user
Write-Host "The following Web App will be affected: $($webapp.name)"
Write-Host "You will be removing alerts for the following user: $myuser"

$continue = Read-Host -Prompt "Continue? (y|n)>";
switch($continue){
	"y" {break;}
	default {Write-Host "Exiting..." -ForegroundColor red; return;}
}

$alertcount = 0

# For each site collection remove all alerts for the given user
$time = Measure-Command {
    foreach ($site in $webapp.Sites)
    {
        # get the collection of webs
        $webs = $site.AllWebs
        foreach ($web in $webs)
        {
            # get the alerts
            $alerts = $web.Alerts

            # if more than 0 alerts, iterate through these alerts to see if there is one for the user
            if ($alerts.Count -gt 0)
            {
                $myalerts = @()
                $mysites += $web.Url
                foreach ($alert in $alerts)
                {
                    if ($alert.User.LoginName -eq $myuser)
                    {
                        echo "Deleting $($alert.Title)"
                        $myalerts += $alert
                        $alertcount++
                    }
                }

                # now we have alerts for this site, we can delete them
                foreach ($alertdel in $myalerts)
                {
                    $alerts.Delete($alertdel.ID)
                }
            }
        }
    }
}

echo "Deleted $alertcount alerts in $($time.TotalSeconds) seconds"

How the hell do Sharepoint 2007 permissions work?

I’ve written about this before, but today I found myself explaining the whole permissioning thing to a colleague, and put together a diagram which shows how inheritance works.  Permissions and groups are inherited as follows:

sp_permissions_inheritance

This shows “Administrative” rights, but can equally be applied to other permissions at any level below a site collection. Dotted boundaries show where the inheritance are optional (and shouldn’t really be called inheritance either).

To explain this a little further let’s start at the top:

Web application level

The Policy for Web Application setting allows you to give Active Directory users and groups rights over all Site Collections in that Web Application.  If you give a user or group Full Access permissions at this level, they will have access to all the settings pages for all sites in the web application.  These users and groups names do not show up in any Sharepoint groups or permissions pages.

Site Collection level

When creating a site collection you can set Primary and Secondary Site Collection Administrators.  These can only be set as Active Directory user accounts.  These users get access to all settings pages for the sites within the site collection (including the root site /).  Emails sent by Sharepoint relating to sites in the site collection appear to come from the Primary Site Collection Administrator.

To update all your Site Collection Administrators in one go, take a look at my Sharepoint 2007 PowerShell scripts.

Sites

This is the level that the groups you can administer within Sharepoint become available, and what most people think of when they think of permissions in Sharepoint.

An important point to note is that there is always a root site within a site collection – this is accessed at the root of the site collection directory (/).  All other sites in the site collection are subsites (or subsites of subsites) of this site.  The groups and permissions you set up for this root site can be used by the other sites in the collection.

This all gets a bit complicated, so lets use an example to illustrate.

sp_perms_inherit2

We’ve got a site collection with a root site at http://dunxd/sites/teams/.  Within this site there are two subsites – a and b.  Site a has a subsite 1.

When we create the site collection (and root site), Sharepoint will set up the following groups:

  • Teams owners – this is for users who can administer the root site.  They won’t see the Site Collection admin links unless they are included in one of the higher level permissions described above.
  • Teams members – this is for users who need to be able to work on content in the site
  • Teams visitors – this is for users who only require read access to the site

When we create subsite a, we have the choice of using the same permissions as the parent site. If we choose that, subsite a will not have any additional groups created.  Owners of the Teams site will also be owners of the a site, and so on.

Note that here we are simply using the groups – once these have been set up, we can change the permissions those groups have for site a without changing the permissions they have for site Teams.  Also note that if we add a user to the Teams owners group, that user will also have the Owner permissions on site a.

We also have the choice of using unique permissions.  If we choose that, additional groups may be created, or we may use existing groups for each of the Owner, Member and Visitor roles.  The default options in this situation are to share the parent Visitors group, and create new Member and Owner groups specifically for the site.

When we come to create a subsite of site a called site 1, we get the same options for using parent permissions or unique permissions.  If we use the parent permissions, we get whatever we set for the parent site – if we used the permissions for Teams in site a then site 1 will also have Teams owners as owner and so on.  If we select to use unique permissions for site 1,  we can create 1 Owners etc, or we can use a owners, or even Teams owners.  A site can access all the groups in the parent sites above it.

This is quite a major departure from the way that normal file system permissions work.  It’s also quite difficult to explain.  I hope this post helps you if you need to understand this, or explain to someone else.

With any luck you didn’t need to read 800 words – the diagram explained it for you.

Getting User Profile Properties out of Sharepoint and into a table

One of the shortcomings of Sharepoint 2007 is the lack of a tabular view of User Profile Properties.  This would be really useful, so I wrote a PowerShell script which gets specified profile properties for every user and writes them into a delimited file.

First up however, you need a list of the profile property names so you know what to select.  The following PowerShell script will display a table showing the internal name used by Sharepoint, and the property name displayed in the Sharepoint UI:

# Outputs a list of User profile names - both the internal name, and the name displayed in Sharepoint

[void][System.Reflection.Assembly]::LoadWithPartialName("Microsoft.Office.Server")
[void][System.Reflection.Assembly]::LoadWithPartialName("Microsoft.Office.Server.UserProfiles")
# Function:          Get-UserProfileConfigManager
# Description:       return a UserProfileConfigManager object which is used for management of MOSS User Profiles
# Parameters:        SSPName          Shared Service Provider Name    

Function global:Get-UserProfileConfigManager($SSPName)
{
$ServerContext = [Microsoft.Office.Server.ServerContext]::GetContext($SSPName);
new-object Microsoft.Office.Server.UserProfiles.UserProfileConfigmanager($servercontext)
}

$cm=Get-UserProfileConfigManager("SharedServices");
$cm.getProperties() | ft name,displayname

The output from this can be used to determine the names of the properties you want to use in the next script.  To use this script, update the  $arProperties list with the property names you need.  By default this saves the results to a file UserProfiles.csv in the directory from which you run the script.  You can then import this into Excel or whatever.

# Outputs a delimited file with specified user profile properties for each user in Sharepoint

# Create array of desired properties
$arProperties = 'UserName','FirstName','LastName','Title','WorkEmail','WorkPhone','Manager','AlternateContact','RoleDescription','PictureURL';
# Specify output file
$outfile = 'UserProfiles.csv';
#Specify delimiter character (i.e. not one that might appear in your user profile data)
$delim = '^';
# Specify Shared Service Provider that contains the user profiles.
$SSP = "SharedServices";

[void][System.Reflection.Assembly]::LoadWithPartialName("Microsoft.Office.Server")
[void][System.Reflection.Assembly]::LoadWithPartialName("Microsoft.Office.Server.UserProfiles")

# Function:          Get-UserProfiles
# Description:       return a UserProfileManager object containing all user profiles
# Parameters:        SSPName          SSPName

Function global:Get-UserProfiles($SSPName)
{
	$ServerContext = [Microsoft.Office.Server.ServerContext]::GetContext($SSPName);
	$UPManager = new-object Microsoft.Office.Server.UserProfiles.UserProfileManager($ServerContext);
	return $UPManager.GetEnumerator();
}
$profiles = Get-UserProfiles($SSP);

#Initialise Output file with headings
$header = [string]::join($delim,$arProperties);
Write-Output $header | Out-File $outfile

#Output the specified properties for each
$profiles | ForEach-Object {
	foreach($p in $arProperties){
		# Get the property name and add it to a new array, which will be used to construct the result string
		$arProfileProps += $_.Item($p);
	}
	$results = [string]::join($delim,$arProfileProps);
	# Get rid of any newlines that may be in there.
	$CleanResults = $results.Replace("`n",'');
	Write-Output $CleanResults
	Remove-Variable -Name arProfileProps
} | Out-File -Append $outfile

The next stage of development would be to pipe the output of the first script into the second, instead of setting up a list of desired properties – it is probably more useful to just grab everything.

I’m sure this could be written much more gracefully, but I can’t work out how to iterate through the UserProfile.Item array/object.  Any suggestions gratefully received!

Notes on Windows Network Load Balancing

Network Load Balancing simply round robins between servers, and makes sure that any client goes to the same host (called affinity) so that the client always reaches the same host in case it has a session on it.

As such, any problems with SharePoint related to performance will not have anything to do with NLB, as all it does is decide which host a client will send requests to.  Rather such problems would be down to one of the web front ends responding slowing – usually down to the application pool needing to free up memory, which should happen automatically after a while.  Where NLB confuses things is that you can’t easily tell which WFE a client machine is talking to.

If NLB is not working, the only likely symptom would be that no requests to the NLB IP address would work.  If you can ping your NLB IP address, then NLB is working.

You can check Network Load Balancing by going to Start > Administrative Tools and running Network Load Balancing (most likely you have this on your PC).  You need to connect to one of the nodes involved.  You can then see the status of the nodes. This should always be checked first in case one of the nodes has stopped responding on the NLB address).  You should also use this interface to drainstop a node before taking it offline for any reason – that way you get as graceful as possible a handover of affinities.

Update: It seems that in certain circumstances, NLB can come apart under load.  We recently switched from two high end Cisco core switches to two stacks of three Dell PowerConnect 6048 switches.  Since then NLB works, but as soon as we get moderate load coming to the NLB address, Sharepoint grinds to a halt.  Dell are chasing this, and we are promised a fix soon – until then no NLB – we can’t roll back to our Cisco switches.  Christian Aid isn’t such a huge organisation (~800 staff worldwide), but heavy users of Sharepoint for file storage – seems enough to break NLB.  I think it is something to do with the multicast hack Microsoft use to make NLB work – but I’d better wait to hear back from Dell and get it working before I can say for sure.

Where to set Sharepoint Admin Permissions

There are a lot of places in Sharepoint where you can assign permissions.  This is intended as a summary of where administrative level permissions can be assigned.

Site Collection Users and Groups

This is set per site collection.  Super user and regular user permissions to children of the site collection are set here.  These permissions can be inherited by an site collection child, including sub-sites.  Giving a user Full Control here does not give Site Collection Admin level control as might be assumed, but does give enough functionality for many things.

Site Collection Administrators

This can be set through the Site Collection Site Settings if you are already a Site Collection Administrator, or owner or secondary contact – i.e. existing site collection administators can add other people.  You cannot add AD groups to this list, so Site Collection Administrators must be listed individually.  Owners and Secondary Contacts are automatically Site Collection Administrators whether they are listed as such or not.

Site Collection Administrators is the highest level permission that can be given to a site collection.  A Farm Administrator does not automatically inherit this permission (but see below) but can add themselves to the a specific Site Collection’s Administrators list through Central Administration > Application management > Site Collection Administrators.

Policy for Web Application

Here you can give Site Collection Administrator type permissions to all Site Collections in a Web Application (e.g. http://division).  This can be useful for setting up admin accounts.

SSP Users and Groups

Access to some, but not all, SSP functions can be assigned via the SSP Users and groups page.

SSP Site Collection Administrators

If you are not on this list, you cannot access the Search Usage Report

SSP Web Application Policy

As for any other site collection, you can add an AD group as Site Collection Administrator by adding the group to this list.

Personalisation Services Permissions

Set up specific users who need to manage profiles and MySites in here.  You also need to set up these users under the Policy for Web Application for your MySites Web Application or they won’t be able to admin mysites fully.

Business Data Catalog Permissions

If you use these, then you need permissions in here for administration.

Central Admin Site Users and Groups

Some functionality can be given here, but mainly you will be able to see the menu, but not do anything.

Central Admin Site Site Collection Administrators

Here you get the real power over Sharepoint.

Policy for Central Admin Web Application

Again – useful for assigning admin permissions by AD group.

Phew – that is a lot of places, and possibly not an exhaustive list.  Does anyone actually need this level of granularity?  Even if that is the case, could this have been made easier through better thought out admin pages, and better labelling?  I am sure that Donald Norman and Edward Tufte would have a lot to say about Sharepoint admin!