Create list views across webs using PowerShell or code

Sometimes you’ll want to have one basic list, which is accessible on all your subsites. One way to do this is using the content query web part. This however, blocks the functionalities a standard list view offers. So instead of the content query web part, we’ll use the basic XsltListViewWebPart. An important note: the standard ListViewWebPart class cannot work across your different webs inside your site collection. You have to use the Xslt one.

First, get an instance of the list you want to reference in your subsite:

$webWithList = Get-SPWeb http://weburl
$list = $webWithList.Lists["ListName"]

Then, get the web part manager of the page of your subsite where you want to insert the list view:

$targetWeb = Get-SPWeb http://weburl/subweb
$wpm = $targetWeb.GetLimitedWebPartManager("default.aspx", [System.Web.UI.WebControls.WebParts.PersonalizationScope]::Shared);

Now that we’ve got both required objects, it’s time to create the XsltListViewWebPart:

$ListViewWebPart = New-Object Microsoft.SharePoint.WebPartPages.XsltListViewWebPart
$ListViewWebPart.ListId = $list.id
$ListViewWebpart.Title = $list.title
$ListViewWebPart.WebId = $list.parentweb.id

It’s very important the WebId is set or otherwise you’ll get a nasty error like this:

“List does not exist. The page you selected contains a list that does not exist. It may have been deleted by another user.”

Last but not least, add the web part to your page:

$wpm.AddWebPart($ListViewWebPart, "Left", 0)

In one flow the script looks like this:

$webWithList = Get-SPWeb http://weburl
$list = $webWithList.Lists["ListName"]
$targetWeb = Get-SPWeb http://weburl/subweb
$wpm = $targetWeb.GetLimitedWebPartManager("default.aspx", [System.Web.UI.WebControls.WebParts.PersonalizationScope]::Shared);
$ListViewWebPart = New-Object Microsoft.SharePoint.WebPartPages.XsltListViewWebPart
$ListViewWebPart.ListId = $list.id
$ListViewWebpart.Title = $list.title
$ListViewWebPart.WebId = $list.parentweb.id
$wpm.AddWebPart($ListViewWebPart, "Left", 0)

If you would like for instance feature stapling, you can use the following code block to your web feature activation:

public override void FeatureActivated(SPFeatureReceiverProperties properties)
{
    var web = properties.Feature.Parent as SPWeb;
    using (var rootWeb = web.Site.OpenWeb())
    {
        var list = rootWeb.Lists[Constants.ListName];
        string file = web.RootFolder.WelcomePage;
        var webPartManager = web.GetLimitedWebPartManager(file, PersonalizationScope.Shared);
        var webPart = new XsltListViewWebPart
        {
            ListId = list.ID,
            Title = list.Title,
            WebId = list.ParentWeb.ID
        };
        webPartManager.AddWebPart(webPart, "Left", 0);
    }
}

Manage your Forms Based Authentication Membership Provider using PowerShell

In SharePoint you can configure a FbaMembershipProvider so that you can store and retrieve membership data from SQL Server. Unlike AD, you don’t have a real management tool for managing FBA users. In cases like this, where you want to help people out when they locked their account or they need a password reset, you can always go to PowerShell.

The first step is of course launching PowerShell. Then you have to “prep” your session so that you can connect to the SQL database. When you’ve already configured your Web Application’s web.config file, you’re good to go. All you need to do is load in that web.config file so the connectionstrings are available.

Do this by the following statement:

[System.AppDomain]::CurrentDomain.SetData("APP_CONFIG_FILE", "C:\inetpub\wwwroot\wss\VirtualDirectories\<iisDir>\web.config")

When you’ve loaded your config file, it’s time to initialize the System.Web assembly.

[void][System.Reflection.Assembly]::LoadWithPartialName("System.Web")

When the assembly gets loaded, the config path is used to read data for the membership providers.

You can list all available membership providers:

[System.Web.Security.Membership]::Providers

FbaMembershipProvider

We’re gonna need the second one. The collection let’s us index on the “Name” property so we’ll go ahead and select the second one:

$provider = [System.Web.Security.Membership]::Providers["FbaMembershipProvider"]

Now that we have the $provider object, we can use all the useful methods it offers:

ChangePassword
ChangePasswordQuestionAndAnswer
CreateUser
DeleteUser
FindUsersByEmail
FindUsersByName
GeneratePassword
GetAllUsers
GetHashCode
GetNumberOfUsersOnline
GetPassword
GetUser
GetUserNameByEmail
ResetPassword
UnlockUser
UpdateUser
ValidateUser

SharePoint 2010: PowerShell to Clear the Timer Job Cache

Great script when you need to clear the timer job cache

SharePoint Tips by Nick Hobbs

I have been clearing the Timer Job Cache manually when needed for years. A few months back I looked for a script to do this automatically and ended up writing one since it can be a slow and laborious task to perform on a multi-server SharePoint farm, and I have now finally got around to sharing it here.

The Timer Job Cache may need to be cleared for a number of reasons, however the main ones I have come across are mainly in development environments when redeploying the same or updated solutions repeatedly, where updated assemblies are not used by timer jobs, and also if for example a small development VM runs out of disk space (frugal disk allocation) because of database transaction logs or data files filling up then the timer jobs stop working. I am sure there are several other reasons you might want or need to clear the…

View original post 438 more words

Remove items from a huge SharePoint list

At a certain client of mine, there was a huge list consisting over 67 million items. This is well over the suggested 30 million item limit. This list was filled with random information and was adding lots of data to the dbo.AllUserData table so we decided to remove it. After trying several approaches, we couldn’t get past the huge DELETE statement SQL builds in order to remove the list. This statement was so large, it even crashed the SQL server. One approach that worked was using the GUI to delete items. As you can guess, this would take a huge amount of mandays to click away the list ūüėČ

Thankfuly SharePoint accepts batch updating items so I’ve written a script to delete those items in no time (subject to your processing power).

$web = get-spweb http://
$list = $web.lists | ? { $_.title -eq "<<ListTitle>>" }
$spQuery = New-Object Microsoft.SharePoint.SPQuery
$spQuery.ViewAttributes = "Scope='Recursive'";
$spQuery.RowLimit = 100
$caml = '<OrderBy Override="TRUE"><FieldRef Name="ID"/></OrderBy>' 
$spQuery.Query = $caml 

do
{
    $listItems = $list.GetItems($spQuery)
    $count = $listItems.Count
    $spQuery.ListItemCollectionPosition = $listItems.ListItemCollectionPosition
    $batch = "<?xml version=`"1.0`" encoding=`"UTF-8`"?><Batch>"
    $j = 0
    for ($j = 0; $j -lt $count; $j++)
    {
        $item = $listItems[$j]
        write-host "`rProcessing ID: $($item.ID) ($($j+1) of $($count))" -nonewline
        $batch += "<Method><SetList Scope=`"Request`">$($list.ID)</SetList><SetVar Name=`"ID`">$($item.ID)</SetVar><SetVar Name=`"Cmd`">Delete</SetVar><SetVar Name=`"owsfileref`">$($item.File.ServerRelativeUrl)</SetVar></Method>"
        if ($i -ge $count) { break }
    }
    $batch += "</Batch>"

    write-host

    write-host "Sending batch..."
    $result = $web.ProcessBatchData($batch)

    write-host "Emptying Recycle Bin..."
    $web.RecycleBin.DeleteAll()
}
while ($spQuery.ListItemCollectionPosition -ne $null)
$web.Dispose()

Setting several SharePoint sites read only

Setting a SharePoint site collection is quite an easy task. Setting several sites and subsites read only is not. There’s no cmdlet available to set one or more SharePoint sites read only.

For me read only means that all the permissions that exist are being put to “Read”. So here’s the script that does all the magic:

There are 3 parameters: The LogFilePath (for instance c:\temp\log.txt) where the old permissions are being written to for later consultation. The SiteUrl of the SharePoint site collection where the sites are located. And the exclusionSitesTitles is an array of titles of sites that should not be put read only. You can also change these to URLs, but then you have to edit the AllWebs line:

$webs = $site.AllWebs | ?{-not ($exclusionSitesTitles -contains $_.Title)}

Param (
  [Parameter(Mandatory=$True, Position=0)]
  [string]$LogFilePath,

  [Parameter(Mandatory=$True, Position=1)]
  [string]$SiteUrl,

  [Parameter(Mandatory=$True, Position=2)]
  [string[]]$exclusionSitesTitles
)

[System.Reflection.Assembly]::LoadWithPartialName(&quot;Microsoft.SharePoint&quot;)
$site = New-Object Microsoft.SharePoint.SPSite($siteUrl)
$webs = $site.AllWebs | ?{-not ($exclusionSitesTitles -contains $_.Title)}

function checkPermissions([Microsoft.SharePoint.SPRoleAssignmentCollection]$roles)
{
	$roles | Out-File -Append -FilePath $logfilepath
	$roles | %{
		if($_.RoleDefinitionBindings.Count -eq 1 -and $_.RoleDefinitionBindings.Contains($guestPermission))
		{
			$_.RoleDefinitionBindings.RemoveAll();
		}
		else
		{
			$_.RoleDefinitionBindings.RemoveAll();
			$_.RoleDefinitionBindings.Add($readPermission);
		}
		$_.Update();
	}
}

function checkLists($web)
{
	$web.Lists | %{
		if($_.HasUniqueRoleAssignments)
		{
			checkPermissions($_.RoleAssignments)
		}
	}
}

$webs | %{
	$readPermission = $_.RoleDefinitions.GetByType([Microsoft.SharePoint.SPRoleType]::Reader);
	$guestPermission = $_.RoleDefinitions.GetByType([Microsoft.SharePoint.SPRoleType]::Guest);
	$_.Url | Out-File -Append -FilePath $logfilepath
	if($_.HasUniqueRoleAssignments)
	{
		checkPermissions($_.RoleAssignments)
	}
	checkLists($_)
	$_.Dispose();
}
$site.Dispose();

Deep dive into SharePoint Timer Jobs and Health Rules

In a previous post, I raised an issue regarding a SQL stored procedure not being created upon the creation of the ASP.NET Session State Service Application. This stored procedure takes care of the deletion of the expired sessions. When we check our timer jobs, we also see we have a definition which is called “State Service Delete Expired Sessions”. This obviously is connected to the regular State Service, but let’s take a closer look on how this timer job works.

Before we go deeper into the fundamentals, you’ll need a tool, which is free, called ILSpy. It’s a .NET decompiler, so if you already have one, you’re good to go. When you’ve downloaded ILSpy, extract it somewhere and you can fire it up. The main screen looks like this:

ilspy main window

Select any open libraries and remove them. This gives us a clean start:

ilspy remove standard libs

Next, we’re gonna look at the timer job itself. We need to know in which library it’s located so we can decompile¬†it with ILSpy. Leave ILSpy¬†open and fire up the SharePoint 2010 Management Shell. Type in the following command:

Get-SPTimerJob | ?{$_.Title -eq "State Service Delete Expired Sessions"} | Select TypeName

This will give you the following output:

Microsoft.Office.Server.Administration.StateServiceExpiredSessionJobDefinition

Great! Let’s go back to ILSpy¬†and open the Microsoft.Office.Server library from the GAC:

ilspy open from gac

Next select the Microsoft.Office.Server library and click “Open”:

ilspy open microsoft office server from gac

Expand the library to view all the namespaces. Expand the Administration namespace and scroll down until you find the StateServiceExpiredSessionJobDefinition¬†class. When you decompile this class, you can expand any of the methods and properties. We’re interested in the Execute method, cause that’s what fires when the timer job is executed:

ilspy decompile class

So all it does is call the static DeleteExpiredSessions(current) method on the StateSqlSession class. When we go deeper and click on the method, we can see what happens behind the scene:

ilspy click on method

Seems that all that happens is call a SQL Stored Procedure “proc_DeleteExpiredItems” from this Timer Job:

ilspy deep into method

Deep diving into Timer Jobs can give you a lot of insight into the engine of SharePoint. The following PowerShell command lists all the Titles and TypeNames from the registered Timer Job Definitions so you can go even deeper:

Get-SPTimerJob | Select Title, TypeName | fl

You’ll notice that health rule jobs are registered differently. Getting their TypeName¬†is also a bit different. You have to use the local SPHealthRulesList:

[Microsoft.SharePoint.Administration.Health.SPHealthRulesList]::Local.Items | Select Title, @{ Label="HealthRuleType"; Expression= { $_["HealthRuleType"] } } | fl

Quick SharePoint farm overview using PowerShell

Getting a quick overview of where your data is can be handy at times. Even to create reports. Using built-in SharePoint tools you can easily get site collection information from your whole farm, and convert that information to a CSV. Once you got that CSV, you can do anything with it.

The following script will export the selected data to a CSV file, called “Export.csv”. It will do this for all of the site collections, besides the central administration, in your farm.

Get-SPSite -limit all | select Url,Owner,@{label="ContentDatabase";Expression={$_.ContentDatabase.Name}},@{label="Size in MB";Expression={$_.Usage.Storage/1MB}} | Export-CSV -Path Export.csv -NoTypeInformation

When you open the CSV, you’ll get an output like this:

"Url","Owner","ContentDatabase","Size in MB"
"http://sharepoint","fondant\administrator","SHP2013_Content_SharePoint80","1.93870639801025"
"http://sharepoint/sites/apps","fondant\administrator","SHP2013_Content_SharePoint80","1.1555118560791"
"http://sharepoint/sites/team","fondant\administrator","SHP2013_Content_SharePoint80","1.85408592224121"
"http://sharepoint:81","fondant\administrator","SP2013_Content_SharePoint81","0.111626625061035"

Now you can do practically anything with the data. If you want more properties, check out the SPSite object, and add these to the select statement.