SharePoint Fanatic

SharePoint insights, real world experience

Reporting on SharePoint MySite distribution by Content Database

Wouldn’t it be nice to see the breakdown of MySites, which belong to a given Content Database, and the total size of each Content Database? Here’s a script that generates this useful report:

$DBs = Get-SPWebApplication http://MySites | Get-SPContentDatabase
foreach ($db in $DBs)
{
 Write-Host -ForegroundColor DarkBlue "DB Name: $($db.Name)"
 $siz="{0:N3}" -f ($db.disksizerequired/1GB)
 write-host -ForegroundColor DarkBlue "DB Size: $($siz) GB"
 Write-Host "========"
 $db | Get-SPSite -Limit all
 Write-Host " " 
}

How to recover from a malfunctioning SharePoint Master Page

Last night I had a bit of fun trying to fix a bad Master Page that prevented access to anything in a site collection via the browser. This Master Page had a reference to a SharePoint Feature that had since been retracted and removed. The solution was to use PowerShell to change the Master Page:

$Site = get-spsite “http://SharePoint/Sites/SpecificSite”
$webcount = $Site.AllWebs.Count
$webs=$Site.AllWebs
for ($i=0; $i -lt $webcount; $i++)
{
 $web=$webs[$i]
 $Master=$web.get_MasterUrl()
 $Master=$Master.Replace("your-custom-master", "v4.master")
 $web.set_Masterurl($Master)
 $web.update()
 $web.dispose()
}

Metadata Warehouse of SharePoint Content

I often write custom reports to be able to analyze huge data from a SharePoint farm. I can ansewr questions such as:
- Number of documents updated per user per month over time.
- What are the metadata fields and values in use, and their frequency by department.

I typically export as a CSV, using pipe delimited format (to avoid misinterpreting the ubiquitous comma), and import into a spreadsheet.

What happens when the dataset is too large for Excel? The data I analyze often pushes the limits of Excel or my machine. In that case, I’ll import into SQL Server. To make it repeatable, I’ll create a import process, that drops and recreates the target table, defines the fields, and how to handle errors for each, then export as an SSIS DTSX package.

To run a DTSX package, I’ll import into Business Intelligence Studio as a package and run from there.

Once in SQL Server, one can handle larger datasets, with all the tools and techniques of SQL for dealing with really big data.

The data can be exposed as an SSRS report.

Report on every library in a SharePoint Farm

It is very useful to be able to examine attributes from every library in a SharePoint farm. Here’s how to generate a report of useful library summary information, such as what level of versioning is enabled, which libraries are configured for QuickLinks and which have Content Types enabled, and how many documents are within each library. I use pipe-separated fields, in case commas are encountered in library fields, such as titles.

Add-PSSnapin "Microsoft.SharePoint.PowerShell" -ErrorAction SilentlyContinue
$mylogfile="L:\PowerShell\CORE\reporting\LibSettings.csv"

$envrun="Prod"			# selects environment to run in
if ($envrun -eq "Dev")
{
$siteUrl = "http://SharePointDev/"
}
elseif ($envrun -eq "Prod")
{
$siteUrl = "http://sharepoint/"
}
else
{
Write-Host "ENVIRONMENT SETTING NOT VALID: script terminating..."
$siteUrl =  $null;
return;
}

Write-Host "script starting" 

$myheader = "STARTING: $(get-date)"
$Sep="|"
add-content $mylogfile "Site$($sep)Web$($sep)Library$($sep)FileCount$($sep)CTEnabled$($sep)VerMajor$($sep)VerMinor$($sep)QuickLaunch$($sep)MajVerLimit$($sep)MinorVerLimit$($sep)UniquePerms"

$WA=Get-spWebApplication $siteUrl;
foreach ($site in $wa.sites)
{
  write-host $site.Url
foreach ($web in $site.allwebs)
{

  if ($true) #useful placeholder for filtering. 
  {
   Write-Host -foregroundcolor darkgreen "$($site.id) - $($site.Url) - $($site.contentdatabase.id) - $($site.contentdatabase.name)"   
 

   for ($i=0;$i -lt $web.Lists.Count;$i++)
   { 
   $JPLib=$web.Lists[$i];
   $A_Lib_Count++;
   $SkipLib=$true; #true
   
   if ( ($JPlib.BaseType -ne "DocumentLibrary") -or ($JPlib.hidden) )
    {
      # forget the rest and return to top
      Write-Host -foregroundcolor green "fast test skipping Library: $($JPlib)";   
	}
    elseif ($JPLib.Title -Match "Site\sAssets|Photo|Image|Customized\sReports|Templates|Pages|Picture|cache|style|Slide")
    {
      # forget the rest and return to top
      Write-Host -foregroundcolor red "fast test skipping Library because it mentions $Matches: $($JPlib)";   
	}
	elseif ($JPLib.BaseTemplate -ne "DocumentLibrary")   #alternatively, only skip if -eq XMLForm
    {
      # forget the rest and return to top
      Write-Host -foregroundcolor red "fast skipping Library because it is not of base DocumentLibrary, it is BaseType:$($JPlib.basetemplate): $($JPlib.title)";   
	}
	elseif (($JPLib.ThumbnailsEnabled) -or ($JPLib.DefaultView -eq "AllSlides"))
    {
      # forget any library with thumbnails, these are not normal doclibs, and return to top
      Write-Host -foregroundcolor red "fast test skipping Library because it has Thumbnails/Slides $($JPlib)";   
	}
    else
    {  $SkipLib=$false;	}
     
	if (!$SkipLib)
	{
	  #write-Host -foregroundcolor green "Processing Library: $($JPlib)";   

	  $LineOut = "$($site.url)$($sep)$($web.title)$($sep)$($JPLib.title)$($sep)$($JPLib.items.count)$($sep)$($JPLib.ContentTypesEnabled)$($sep)$($JPLib.get_EnableVersioning())$($sep)$($JPlib.get_EnableMinorVersions())$($sep)$($JPLib.OnQuickLaunch)$($sep)$($JPLib.MajorVersionLimit)$($sep)$($JPLib.MajorWithMinorVersionsLimit)$($sep)$($JPLib.HasUniqueRoleAssignments)"
	  Write-Host $lineOut
	add-content $mylogfile $LineOut
	}
	
	}
   
} #foreach site
} #if $true/Siteurl is not null, if environment setup is valid
} #foreach letter
###########################

Resetting a SharePoint Farm Passphrase

If you don’t have the SharePoint farm passphrase, you can’t join a server to the farm. And there’s no way to get it back once you lose it. However it’s quite easy to reset it. First, let’s be aware fo the stiff requirements for a passphrase. You should ensure that the passphrase meets the following criteria:
Contains at least eight characters
Contains at least three of the following four character groups: ◦English uppercase characters (from A through Z)
+ English lowercase characters (from a through z)
+ Numerals (from 0 through 9)
+ Nonalphabetic characters (such as !, $, #, %)

$passphrase = ConvertTo-SecureString -String "P1ckAg00dPa$$w0rd" -asPlainText -Force
Set-SPPassPhrase -PassPhrase $passphrase -Confirm

The purpose of the passphrase is to prevent unauthorized servers from joining to a farm, and using their newfound access for malicious purposes.

What is less well known is that there is a job that runs that propagates the Passphrase amongst the SharePoint servers in the farm. It is only run when the passphrase is changed or when a server is added to the farm.
SPMasterPassphraseDeploymentJobDefinition. This Timer Job can run into trouble, so it is good to be aware of it for diagnosis and manual retry.

A link straight to a SharePoint document’s metadata

Often users want a link direct to a document’s metadata. That’s easily done using this format:

http://SharePoint/sites/SiteCol/DemoMajorV2/forms/DispForm.aspx?ID=%5Bx%5D

Here’s a sample link to a document’s metadata properties, just add the ID:

http://SharePoint/sites/SiteCol/DemoMajorV2/forms/DispForm.aspx?ID=285

I took a random document:

http://SharePoint/sites/SiteCol/DemoMajorV2/TestDoc.docx

Found its ID in the browser by adding it to a View:

http://SharePoint/sites/SiteCol/DemoMajorV2/Forms/My%20Documents.aspx

Then took the format:
http://SharePoint/sites/SiteCol/DemoMajorV2/forms/DispForm.aspx?ID=%5Bx%5D and added the number to it:

http://SharePoint/sites/SiteCol/DemoMajorV2/forms/DispForm.aspx?ID=285

That same format can be used within the search XSL to add a reference to view the document’s metadata in search results. Here’s the XSL to paste into the XSL field in Core Search Results:

<div class=”srch-Title3″>
 <xsl:variable name=”itemid” select=”ItemID”/>
 <xsl:choose>
 <xsl:when test=”contentclass[. = 'STS_ListItem_DocumentLibrary']“>
 <xsl:choose>
 <xsl:when test=”contains(basic4,’http’)”>
 <xsl:variable name=”library” select=”substring-after(substring-after(url,basic4),’/')” />
 <xsl:variable name=”displayUrl” select=”concat(basic4, ‘/’, substring-before($library,’/'),’/Forms/DispForm.aspx?ID=’,itemid)” />
 <a href=”{$displayUrl}”>
 Show properties
 </a>
 </xsl:when>
 <xsl:otherwise>
 <xsl:variable name=”DocLib” select=”substring-after(substring-after(url,sitename),’/')” />
 <xsl:variable name=”MetaDataPath” select=”concat(sitename, ‘/’, substring-before($DocLib,’/'),’Forms/DispForm.aspx?ID=’,itemid)” />
 <a href=”{$MetaDataPath}”>
 Show properties
 </a>
 </xsl:otherwise>
 
</xsl:choose>
 <a href=”{sitename}”>
 Show library
 </a>
 <br></br>
 </xsl:when>
 <xsl:otherwise>
 </xsl:otherwise>
 </xsl:choose>
 </div>

SharePoint Admins – how to save your hide

I had the scare of the week last night. I was doing some pre-approved SharePoint farm cleanup. Part of that was removing some Farm Solutions (WSPs). The Retract of the solution partially failed, and hosed Central Admin (ouch).

Last time I had this, the uninstall of UMT software left a dozen dangling references in all Web.Configs that I cleaned up by hand. Those caused workflows to all stop. This time, I was getting security and page rendering errors.

Good thing we had done a backupSPFarm recently with ConfigurationOnly option.

Given Central Admin was hosed, I used PowerShell:
Backup-SPFarm: http://technet.microsoft.com/en-us/library/ff607881(v=office.14).aspx
Restore-SPFarm: http://technet.microsoft.com/en-us/library/ff607783(v=office.14).aspx

Once the restore was done, Central Admin appeared to behave erratically. This was simply the farm sync’ing up, with a series of IISResets associated with feature re-deployment.

Feature Reporting and Deactivation across a SharePoint Farm

Before retracting a SharePOint solution, it is best to deactivate the associated feature in each Site Collection. Here’s a script that reports on which Site Collections have a feature enabled, and optionally deactivate the feature. This is easily adapted to Web or Web Application features as well. Best is to first get the SPFeature GUID, using get-spFeature CmdLet. Just flip the $Deactivate switch to $true to actually deactivate. Note any SPFeature not found will generate an error that a try/catch clause will not catch. The quick solution is to capture my output into $OutLine, and clear the console and output the summary at the end of the run.

$OutLine=$null;
$Deactivate = $false;
$a=Get-SPFeature 359d84ef-ae24-4ba6-9dcf-1bbffe1fb788     #my site collection feature, substitute for yours

Get-SPWebApplication | Get-SPSite -Limit all | % {
$b = Get-SPFeature -Identity 359d84ef-ae24-4ba6-9dcf-1bbffe1fb788     -site $_.url

if ($b -ne $null)
{
	$OutLine = $OutLine + "Found active in $($_.url)`n"
	$b=$null;
	if ($Deactivate)
	{
		Disable-SPFeature 359d84ef-ae24-4ba6-9dcf-1bbffe1fb788 –url $_.url
	}
}

}
cls
if ($Deactivate)
{
	Write-Host "Deactivated all; Here are Site Collections where it was active:"
}
else
{
	Write-Host "Here are Site Collections where it is active:"
}
write-Host $OutLine

Tuning SharePoint Search Ranking

SharePoint Search results are returned in order of relevancy, which is determined by a ranking model. There are a number of ranking models cooked into SharePoint 2010. These can be refined to a limited extent, with a bit of insight, to better serve users.

To see the models and their definition, let’s query the SharePoint Search application DB:

SELECT * FROM [Search_Service_Application_DB].[dbo].[MSSRankingModels]

The resultset has the models; the GUID, whether it is default, and the underlying XML that specifies the model. The model name is at the beginning of the XML.

Using PowerShell, we can get the array of ranking models, and is the only supported approach for manipulating the models, changing the default, and for creating new ranking models. Here’s how to get the models:

Get-SPEnterpriseSearchServiceApplication | Get-SPEnterpriseSearchRankingModel

Now we can assign the ranking model array to a variable and index into it:

$A = Get-SPEnterpriseSearchServiceApplication | Get-SPEnterpriseSearchRankingModel

Or we can grab the one ranking model that we like by using the GUID, which we have to predetermine, but that’s easy, as it’s returned by the above query and is unchanging. For new models, we get to specify the GUID as well.

Once you know your rank model GUID, you can switch to it by getting it, and setting it as default:

$r = Get-SPEnterpriseSearchServiceApplication | Get-SPEnterpriseSearchRankingModel 8f6fd0bc-06f9-43cf-bbab-08c377e083f4
$r.MakeDefault()

To create a custom rank model, first identify the Managed Properties, by PID. The name is part of the XML, but it is the PID that drives the ranking. Here’s how to get all the Managed Properties and their PIDs:

Get-SPEnterpriseSearchServiceApplication | Get-SPEnterpriseSearchMetadataManagedProperty

Now we create a new ranking model called MyRank, note i want LastModified property to be relevant.

Get-SPEnterpriseSearchServiceApplication | New-SPEnterpriseSearchRankingModel –rankingmodelxml "<?xml version='1.0'?><rankingModel name='MyRank2' id='8447b4bc-3582-45c5-9cb8-ba2a319d850e' description='MSDRank2' xmlns='http://schemas.microsoft.com/office/2009/rankingModel'>
	<queryDependentFeatures>
		<queryDependentFeature name='Body' pid='1' weight='0.00125145559138435' lengthNormalization='0.0474870346616999'/>
		<queryDependentFeature name='LastModifiedTime' pid='4' weight='3.46602125767061' lengthNormalization='0.549393313908594'/>
		<queryDependentFeature name='Title' pid='2' weight='1.46602125767061' lengthNormalization='0.549393313908594'/>
		<queryDependentFeature name='Author' pid='3' weight='0.410225403867996' lengthNormalization='1.0563226501349'/>
		<queryDependentFeature name='DisplayName' pid='56' weight='0.570071355441683' lengthNormalization='0.552529462971364'/>
		<queryDependentFeature name='ExtractedTitle' pid='302' weight='1.67377875011698' lengthNormalization='0.600572652201123'/>
		<queryDependentFeature name='SocialTag' pid='264' weight='0.593169953073459' lengthNormalization='2.28258134389272'/>
		<queryDependentFeature name='QLogClickedText' pid='100' weight='1.87179361911171' lengthNormalization='3.31081658691434'/>
		<queryDependentFeature name='AnchorText' pid='10' weight='0.593169953073459' lengthNormalization='2.28258134389272'/>
	</queryDependentFeatures>
	<queryIndependentFeatures>
		<queryIndependentFeature name='ClickDistance' pid='96' default='5' weight='1.86902034145632'>
			<transformInvRational k='0.0900786349287429'/>
		</queryIndependentFeature>
		<queryIndependentFeature name='URLDepth' pid='303' default='3' weight='1.68597497899313'>
			<transformInvRational k='0.0515178916330992'/>
		</queryIndependentFeature>
		<queryIndependentFeature name='Lastclick' pid='341' default='0' weight='0.219043069749249'>
			<transformRational k='5.44735200915216'/>
		</queryIndependentFeature>
		<languageFeature name='Language' pid='5' default='1' weight='-0.56841237556044'/>
	</queryIndependentFeatures>
</rankingModel>"

There are two parts to the model; the query dependent section that is associated with the actual query and it’s metadata, and the query independent part that ranks based on number of slashes (URLDepth) and click frequency etc.

As soon as a model is default, you can see the effect of the new ranking model.

Here’s how to change this model, note I add a new field called MyCompany and boost its relevance:

<?xml version="1.0" encoding="utf-8"?>
Get-SPEnterpriseSearchServiceApplication | Get-SPEnterpriseSearchRankingModel 8447b4bc-3582-45c5-9cb8-ba2a319d850e | Set-SPEnterpriseSearchRankingModel –rankingmodelxml "<?xml version='1.0'?><rankingModel name='MSDRank2' id='8447b4bc-3582-45c5-9cb8-ba2a319d850e' description='MyRank2' xmlns='http://schemas.microsoft.com/office/2009/rankingModel'>
	<queryDependentFeatures>
		<queryDependentFeature name='Body' pid='1' weight='0.00125145559138435' lengthNormalization='0.0474870346616999'/>
		<queryDependentFeature name='MyCompany' pid='414' weight='3.610225403867996' lengthNormalization='1.0563226501349'/>
		<queryDependentFeature name='Title' pid='2' weight='0.46602125767061' lengthNormalization='0.549393313908594'/>
		<queryDependentFeature name='Author' pid='3' weight='0.410225403867996' lengthNormalization='1.0563226501349'/>
		<queryDependentFeature name='DisplayName' pid='56' weight='0.570071355441683' lengthNormalization='0.552529462971364'/>
		<queryDependentFeature name='ExtractedTitle' pid='302' weight='1.67377875011698' lengthNormalization='0.600572652201123'/>
		<queryDependentFeature name='SocialTag' pid='264' weight='0.593169953073459' lengthNormalization='2.28258134389272'/>
		<queryDependentFeature name='QLogClickedText' pid='100' weight='1.87179361911171' lengthNormalization='3.31081658691434'/>
		<queryDependentFeature name='AnchorText' pid='10' weight='0.593169953073459' lengthNormalization='2.28258134389272'/>
	</queryDependentFeatures>
	<queryIndependentFeatures>
		<queryIndependentFeature name='ClickDistance' pid='96' default='5' weight='1.86902034145632'>
			<transformInvRational k='0.0900786349287429'/>
		</queryIndependentFeature>
		<queryIndependentFeature name='URLDepth' pid='303' default='3' weight='1.68597497899313'>
			<transformInvRational k='0.0515178916330992'/>
		</queryIndependentFeature>
		<queryIndependentFeature name='Lastclick' pid='341' default='0' weight='0.219043069749249'>
			<transformRational k='5.44735200915216'/>
		</queryIndependentFeature>
		<queryIndependentFeature name='MSDModified' pid='445' default='1' weight='2.56841237556044'>
				<transformRational k='5.44735200915216'/>
			</queryIndependentFeature>
			<languageFeature name='Language' pid='5' default='1' weight='1.5'/>
	</queryIndependentFeatures>
</rankingModel>"

I admittedly did not have success ranking by how recent a document was updated. This is known as “Freshness”. SP2010 has very limited ability to customize ranking. I have not succeeded in getting it to respect “freshness”. A simple freshness ranking seems infuriatingly out of reach. However SP2013 supports it explicitly. While the default SharePoint 2013 ranking model doesn’t boost the rank of search results based on their freshness, we can achieve this by adding a tuning of the static rank that combines information from the LastModifiedTime managed property with the DateTimeUtcNow query property, using the freshness transform function. These Transform functions are used to customize ranking in SP2013. The freshness transform function is the only transform that we can use for this freshness rank feature, because it converts the age of the item from an internal representation into days. In SP2010 the transforms are much more obscure and not really usable. Microsoft reports that the freshness transform in SP2013 can be used. Even before getting to SP2013, we can have an SP2013 farm configured to crawl production SP2010 and return results tuned in this way, and can use the SP2013 search results to serve any client we choose to point to SP2013, including a simple search site in SP2013.

Reporting on all SharePoint Search Scopes

Search scopes are often created to refine the results returned on SharePont Search. I’ve written this small snippet of PowerShell as an easy way to get a report on all Scopes. I decided not to embelish it, and keep it quick and (not too) dirty, here goes:

$a = Get-SPEnterpriseSearchServiceApplication #grabs Content and Query
$scopes = $a | Get-SPEnterpriseSearchQueryScope

foreach ($Scope in $scopes)
{
write-host $Scope.name
write-host "======================="
$scope.Rules  #outputs all the rules
}
Follow

Get every new post delivered to your Inbox.

Join 64 other followers