Identify SharePoint Enterprise Feature usage script

I recently had to produce a report that identified where enterprise features were being used in a SharePoint farm, I did a quick search on google and found a PowerShell script that almost worked.

The original script was at the following site: http://panduchunduri.com/powershell-script-to-get-a-list-of-sites-that-have-enterprise-features-enabled/

This site seems to have disappeared, it’s a good thing I found it when I did.

Here is the script that I developed based off the panduchunduri script:

	Add-PSSnapin Microsoft.SharePoint.PowerShell -ErrorAction SilentlyContinue

	$enterpriseFeatureUsageFile = "C:\Reports\EnterpriseFeatureUsage.csv"
	$enterpriseReportFile = "C:\Reports\EnterpriseReport.csv"

	$enterpriseFeatureUsageArray = new-object System.Collections.ArrayList($null)
	$enterpriseReportArray = new-object System.Collections.ArrayList($null)
	function Get-PowerPivotLibs ($ssurl)
	{
	$powerPivotFeatures = @("1a33a234-b4a4-4fc6-96c2-8bdb56388bd5", "e9c4784b-d453-46f5-8559-3c891d7159dd", "f8c51e81-0b46-4535-a3d5-244f63e1cab9")
	$web = Get-SPWeb $ssurl
	$libs = $web \|
	Select -ExpandProperty Lists \|
	Where { $_.GetType().Name -eq "SPDocumentLibrary" -and -not $_.hidden }

	foreach($lib in $libs)
	{
	if(($powerPivotFeatures -contains $lib.TemplateFeatureId))
	{
	$entry = new-object psobject -Property @{
	Url = ("{0}{1}" -f $ssurl.trim('/'), $list.DefaultViewUrl)
	Feature = "PowerPivot Library"
	}
	$tmp = $enterpriseFeatureUsageArray.Add($entry)
	}
	}
	$web.Dispose()
	}

	function Get-DataConnectionLibs($ssurl)
	{
	$dataConnFeatures = @("00bfea71-dbd7-4f72-b8cb-da7ac0440130")
	$web = Get-SPWeb $ssurl
	$libs = $web \|
	Select -ExpandProperty Lists \|
	Where { $_.GetType().Name -eq "SPDocumentLibrary" -and -not $_.hidden }

	foreach($lib in $libs)
	{
	if(($dataConnFeatures -contains $lib.TemplateFeatureId))
	{
	$entry = new-object psobject -Property @{
	Url = ("{0}{1}" -f $ssurl.trim('/'), $lib.DefaultViewUrl)
	Feature = "DataConnection Library"
	}
	$tmp = $enterpriseFeatureUsageArray.Add($entry)
	}
	}
	$web.Dispose()
	}

	function Get-WebPartUsage($ssurl, $webpartType, $friendlyName){

	$web = Get-SPWeb $ssurl
	$lists = $web \|
	Select -ExpandProperty Lists
	Where { -ne $_.hidden }

	if([Microsoft.SharePoint.Publishing.PublishingWeb]::IsPublishingWeb($web))
	{
	$pWeb = [Microsoft.SharePoint.Publishing.PublishingWeb]::GetPublishingWeb($web)
	$pages = $pWeb.PagesList

	foreach ($item in $pages.Items) {
	try{

	if($item.Url.EndsWith(".aspx")){

	$manager = $item.file.GetLimitedWebPartManager([System.Web.UI.WebControls.Webparts.PersonalizationScope]::Shared);
	$wps = $manager.webparts
	foreach($wp in $wps)
	{
	if($wp.GetType().Name -eq $webpartType){
	$url = $web.Url +"/"+ $item.Url
	$entry = new-object psobject -Property @{
	Url = $url
	Feature = $friendlyName
	}
	$tmp = $enterpriseFeatureUsageArray.Add($entry)
	}
	}
	}

	}catch{

	$fileUrl = $web.Url +"/"+ $item.Url
	Write-Host ("Error in Get-WebPartUsage for PublishingWeb {0}, {1}" -f $fileUrl, $webpartType) -ForegroundColor Red
	Write-Host $_.Exception.Message -ForegroundColor Red
	Write-Host $_.Exception.StackTrace -ForegroundColor Red
	}
	}

	} else {
	foreach($list in $lists)
	{
	if($list.BaseTemplate -eq "WebPageLibrary")
	{
	foreach ($item in $list.Items) {
	try{
	if($item.Url.EndsWith(".aspx")){

	$manager = $item.file.GetLimitedWebPartManager([System.Web.UI.WebControls.Webparts.PersonalizationScope]::Shared);
	$wps = $manager.webparts

	foreach($wp in $wps)
	{
	if($wp.GetType().Name -eq $webpartType){
	$url = $web.Url +"/"+ $item.Url
	$entry = new-object psobject -Property @{
	Url = $url
	Feature = $friendlyName
	}
	$tmp = $enterpriseFeatureUsageArray.Add($entry)
	}

	}
	}

	}catch{

	$fileUrl = $web.Url +"/"+ $item.Url
	Write-Host ("Error in Get-WebPartUsage for PublishingWeb {0}, {1}" -f $fileUrl, $webpartType) -ForegroundColor Red
	Write-Host $_.Exception.Message -ForegroundColor Red
	Write-Host $_.Exception.StackTrace -ForegroundColor Red
	}
	}
	}
	}
	}

	$web.Dispose();
	}

	function Get-EnterpriseWebPartsUsage($ssurl){

	Get-WebPartUsage $ssurl "BusinessDataActionsWebPart" "Business Data Actions WebPart"
	Get-WebPartUsage $ssurl "BusinessDataFilterWebPart" "Business Data Filter WebPart"
	Get-WebPartUsage $ssurl "BusinessDataDetailsWebPart" "Business Data Details WebPart"
	Get-WebPartUsage $ssurl "BusinessDataItemBuilder" "Business Data Item Builder WebPart"
	Get-WebPartUsage $ssurl "BusinessDataListWebPart" "Business Data List WebPart"
	Get-WebPartUsage $ssurl "BusinessDataAssociationWebPart" "Business Data Association WebPart"
	Get-WebPartUsage $ssurl "ChartWebPart" "Chart WebPart"
	Get-WebPartUsage $ssurl "ExcelWebRenderer" "Excel Web Access WebPart"
	Get-WebPartUsage $ssurl "IndicatorWebpart" "Indicator Details WebPart"
	Get-WebPartUsage $ssurl "KPIListWebPart" "Status List WebPart"
	Get-WebPartUsage $ssurl "VisioWebAccess" "Visio WebPart"
	Get-WebPartUsage $ssurl "WhatsPopularWebPart" "Web Analytics Web Part"
	Get-WebPartUsage $ssurl "ApplyFiltersWebPart" "Apply Filters Button Web Part"
	Get-WebPartUsage $ssurl "SPSlicerChoicesWebPart" "Choice Filter Web Part"
	Get-WebPartUsage $ssurl "UserContextFilterWebPart" "Current User Filter Web Part"
	Get-WebPartUsage $ssurl "DateFilterWebPart" "Date Filter Web Part"
	Get-WebPartUsage $ssurl "PageContextFilterWebPart" "Page Field Filter Web Part"
	Get-WebPartUsage $ssurl "QueryStringFilterWebPart" "Query String (URL) Filter Web Part"
	Get-WebPartUsage $ssurl "SpListFilterWebPart" "SharePoint List Filter Web Part"
	Get-WebPartUsage $ssurl "ScorecardFilterWebPart" "SQL Server Analysis Services Filter Web Part"
	Get-WebPartUsage $ssurl "SPSlicerTextWebPart" "Text Filter Web Part"
	Get-WebPartUsage $ssurl "OWACalendarPart" "My Calendar Web Part"
	Get-WebPartUsage $ssurl "OWAContactsPart" "My Contacts Web Part"
	Get-WebPartUsage $ssurl "OWAInboxPart" "My Inbox Web Part"
	Get-WebPartUsage $ssurl "OWAPart" "My Mail Folder Web Part"
	Get-WebPartUsage $ssurl "OWATasksPart" "My Mail Folder Web Part"
	Get-WebPartUsage $ssurl "ReportViewerWebPart" "SQL Server Reporting Services Report Viewer Web Part"
	Get-WebPartUsage $ssurl "BrowserFormWebPart" "InfoPath Form Web Part"
	}

	function Get-ContentOrganizerRules($ssurl)
	{
	$web = Get-SPWeb $ssurl
	$lists = $web \|
	Select -ExpandProperty Lists \|
	Where { $_.GetType().Name -eq "SPList" -and $_.hidden }

	foreach($list in $lists)
	{

	foreach($contenType in $list.ContentTypes){
	if($null -ne $contenType){
	if($contenType.Id.ToString() -eq "0x0100DC2417D125A4489CA59DCC70E3F152B2000C65439F6CABB14AB9C55083A32BCE9C" -and $contenType.Name -eq "Rule")
	{
	$entry = new-object psobject -Property @{
	Url = ("{0}{1}" -f $ssurl.trim('/'), $list.DefaultViewUrl)
	Feature = "ContentOrganizerRules"
	}
	$tmp = $enterpriseFeatureUsageArray.Add($entry)
	}
	}
	}
	}
	$web.Dispose()
	}

	function Get-InfoPathLists($ssurl)
	{
	$web = Get-SPWeb $ssurl
	$listsAndLibs = $web \|
	Select -ExpandProperty Lists \|
	Where { ($_.GetType().Name -eq "SPList" -or $_.GetType().Name -eq "SPDocumentLibrary") -and -not $_.hidden }

	foreach($list in $listsAndLibs)
	{
	switch($list.BaseType)
	{
	"DocumentLibrary"{
	if( $list.BaseTemplate -eq "XMLForm" )
	{
	$entry = new-object psobject -Property @{
	Url = ("{0}{1}" -f $ssurl.trim('/'), $list.DefaultViewUrl)
	Feature = "InfoPath Library"
	}
	$tmp = $enterpriseFeatureUsageArray.Add($entry)
	}
	}
	{"GenericList" -or "Survey"}
	{
	if($null -ne $list.Forms){
	foreach($form in $list.Forms){
	if($form.Url.EndsWith("ifs.aspx"))
	{
	$entry = new-object psobject -Property @{
	Url = ("{0}{1}" -f $ssurl.trim('/'), $list.DefaultViewUrl)
	Feature = "InfoPath Library"
	}
	$tmp = $enterpriseFeatureUsageArray.Add($entry)
	break;
	}
	}
	}
	}
	default {
	}
	}
	}
	$web.Dispose()
	}

	Function Evaluate-Site ($site)
	{
	$premiumfeature = $null
	$EnterpriseSiteCollection = "no"
	if($site.ReadLocked -eq $false)
	{
	$premiumfeature = Get-SPFeature "PremiumSite" -Site $site.Url -ErrorAction SilentlyContinue
	if ($null -ne $premiumfeature)
	{
	$EnterpriseSiteCollection = "yes"
	}
	$EnterpriseWeb = "no"
	foreach ($web in $site.AllWebs)
	{
	$premiumfeature = $null
	$premiumfeature = Get-SPFeature "PremiumWeb" -Web $web.Url -ErrorAction SilentlyContinue
	if ($null -ne $premiumfeature)
	{
	$EnterpriseWeb = "yes"
	}

	$entry = new-object psobject -Property @{
	SiteUrl = $site.Url
	SiteCollection = $EnterpriseSiteCollection
	WebUrl = $web.Url
	EnterpriseWeb = $EnterpriseWeb
	}
	$tmp = $enterpriseReportArray.Add($entry)

	Get-InfopathLists($web.URL);
	Get-ContentOrganizerRules($web.URL);
	Get-DataConnectionLibs($web.URL);
	Get-PowerPivotLibs($web.URL);
	Get-EnterpriseWebPartsUsage($web.URL);
	}
	$web.Dispose();
	}
	}

	function Get-EnterpriseReport($webApp)
	{
	$sites = Get-SpSite -Limit All -WebApplication $webApp
	[int]$i = 0
	[int]$c = $sites.Count
	foreach ($spsite in $sites)
	{
	$i++
	[int]$p = 100
	if($c -gt 0){
	$p = ($i/$c)*100
	}

	Write-Progress -Activity "Current SiteCollection" -CurrentOperation $spsite.Url -PercentComplete $p -Status "$i of $c"
	Evaluate-Site ($spsite)
	}

	$enterpriseFeatureUsageArray \| Export-Csv $enterpriseFeatureUsageFile -NoTypeInformation -Force
	$enterpriseReportArray \| Export-Csv $enterpriseReportFile -NoTypeInformation -Force
	}

	Get-SPWebApplication \| select Url \| Get-EnterpriseReport

view raw

GetEnterpriseEnabledSites.ps1

hosted with ❤ by GitHub

Powershell Export SQL results to CSV

I have a bunch of SQL scripts for which I need to export the results to CSV, this is something I need to do on a semi-regular basis and its generally a handy thing to be able to automate without having to create a SSIS package or write a console application.

So here is a Powershell script i have written which will read the SQL queries in from .sql files in the input directory and output the results to csv files with the same name as the source script.

WARNING: This script doesn’t handle SQL comments very well, so make sure you remove them from your SQL scripts before using this script to run them, also for some reason wordpress keeps messing up the connection string in the SqlConnection so Datace should be Data Source.

	# Runs the SQL command and returns a data table
	function Run-Query($query, $database, $server){

	$SqlConnection = New-Object System.Data.SqlClient.SqlConnection("Data Source = $server;Initial Catalog=$database;Integrated Security = True")
	$SqlConnection.Open()

	$SqlCmd = New-Object System.Data.SqlClient.SqlCommand($query,$SqlConnection)

	$SqlAdapter = New-Object System.Data.SqlClient.SqlDataAdapter($SqlCmd)

	$DataSet = New-Object System.Data.DataSet
	$nRecs = $SqlAdapter.Fill($DataSet)
	$nRecs \| Out-Null

	$objTable = $DataSet.Tables[0]

	$SqlConnection.Close()

	return $objTable
	}

	# Runs the SQL scripts in the input directory and exports the results to csv
	function Export-SqlToCsv($inputDirectory, $outputDirectory, $server, $database){

	$scripts = Get-ChildItem -Path $inputDirectory \| where { $_.Name.EndsWith('.sql') }

	if((Test-Path $outputDirectory) -eq 0){
	New-Item -ItemType Directory -Force -Path $outputDirectory \| Out-Null
	}

	foreach($script in $scripts){

	$query = Get-Content $script.FullName

	$fileName = $script.Name.Split('.')[0] + '.csv'

	$outPutFile = Join-Path -path $outPutDir -childpath $fileName

	$dataTable = Run-Query $query $database $server

	$dataTable \| Export-CSV $outPutFile -notype
	}
	}

	$Database = "Database"
	$Server = "SqlInstance"
	$ScriptsDir = 'C:\Scripts\SQL'
	$OutPutDir = 'C:\Data'

	Export-SqlToCsv $ScriptsDir $OutPutDir $Server $Database

view raw

ExportSQLResults.ps1

hosted with ❤ by GitHub

Load Workflow Class: System.Reflection.TargetInvocationException: Exception has been thrown by the target of an invocation. —> System.Collections.Generic.KeyNotFoundException: The given key was not present in the dictionary.

I ran into an interesting issue today on one of the sharepoint servers we manage, the issue was affecting all workflows across a single site collection, the workflows would just fail immediately and gave a ‘Failed to Start (Retrying)’ message, when I looked at the logs I found these errors:

Load Workflow Class: System.Reflection.TargetInvocationException: Exception has been thrown by the target of an invocation. ---> System.Collections.Generic.KeyNotFoundException: The given key was not present in the dictionary.   
 at System.ThrowHelper.ThrowKeyNotFoundException()   
 at System.Collections.Generic.Dictionary`2.get_Item(TKey key)   
 at Microsoft.SharePoint.Workflow.SPWorkflowManager.IsConfigForSite(SPSite site)   
 at Microsoft.SharePoint.Workflow.SPWorkflowManager.GetWorkflowConfurationSection(SPSite site, String section)   
 at Microsoft.SharePoint.Workflow.SPWinOeHostServices.EnsurePluggableServices(SPSite site, SPWorkflowExternalDataExchangeServiceCollection services, ExternalDataExchangeService existingServices)   
 at Microsoft.SharePoint.Workflow.SPWinOeHostServices..ctor(SPSite site, SPWeb web, SPWorkflowManager manager, SPWorkflowEngine engine)     -
-- End of inner exception stack trace ---   
 at System.RuntimeMethodHandle._InvokeConstructor(Object[] args, SignatureStruct& signature, IntPtr declaringType)   
 at System.Reflection.RuntimeConstructorInfo.Invoke(BindingFlags invokeAttr, Binder binder, Object[] parameters, CultureInfo culture)   
 at System.RuntimeType.CreateInstanceImpl(BindingFlags bindingAttr, Binder binder, Object[] args, CultureInfo culture, Object[] activationAttributes)   
 at Microsoft.SharePoint.Workflow.SPWorkflowManager.LoadPluggableClass(String classname, String assembly, Object[] parameters)

System.Reflection.TargetInvocationException: Exception has been thrown by the target of an invocation. ---> System.Collections.Generic.KeyNotFoundException: The given key was not present in the dictionary.   
 at System.ThrowHelper.ThrowKeyNotFoundException()   
 at System.Collections.Generic.Dictionary`2.get_Item(TKey key)   
 at Microsoft.SharePoint.Workflow.SPWorkflowManager.IsConfigForSite(SPSite site)   
 at Microsoft.SharePoint.Workflow.SPWorkflowManager.GetWorkflowConfurationSection(SPSite site, String section)   
 at Microsoft.SharePoint.Workflow.SPWinOeHostServices.EnsurePluggableServices(SPSite site, SPWorkflowExternalDataExchangeServiceCollection services, ExternalDataExchangeService existingServices)   
 at Microsoft.SharePoint.Workflow.SPWinOeHostServices..ctor(SPSite site, SPWeb web, SPWorkflowManager manager, SPWorkflowEngine engine)     -
-- End of inner exception stack trace ---   
 at System.RuntimeMethodHandle._InvokeConstructor(Object[] args, SignatureStruct& signature, IntPtr declaringType)   
 at System.Reflection.RuntimeConstructorInfo.Invoke(BindingFlags invokeAttr, Binder binder, Object[] parameters, CultureInfo culture)   
 at System.RuntimeType.CreateInstanceImpl(BindingFlags bindingAttr, Binder binder, Object[] args, CultureInfo culture, Object[] activationAttributes)   
 at Microsoft.SharePoint.Workflow.SPWorkflowManager.LoadPluggableClass(String classname, String assembly, Object[] parameters)   
 at Microsoft.SharePoint.Workflow.SPWorkflowManager.GetService(SPWorkflowAssociation association, SPWorkflowEngine engine)   
 at Microsoft.SharePoint.Workflow.SPWorkflowManager.RunWorkflowElev(SPWorkflow workflow, Collection`1 events, SPWorkflowRunOptionsInternal runOptions)

When I googled these errors I found this article and I tried his approach but no luck, so I opened up .NET Reflector and had a look at the IsConfigForSite method on the SPWorkflowManager class and found the following:

.net-reflector

The exception is being thrown when it’s trying to access the IisSettings dictionary using the SPUrlZone.Default enum, so I opened up a powershell console and had a look at what was in the IisSettings dictionary:

$site = Get-SPSite http://mysite
$site.WebApplication.IisSettings

This showed that there was no entry for the default zone in the IisSettings dictionary, initially I thought we just needed to reconfigure the alternate access mappings and add an entry for the default zone but this had no affect.

So I started discussing this issue with one of my colleagues who had worked on some issues with this sever the previous week. I knew he had made some pretty significant changes, and as it turned out one of those changes was he had extended the web application to the intranet zone and then deleted the previous web application.

This was the root cause of the problem, this is why there was no default entry in the IisSettings dictionary, so I extended the web application to the default zone and the workflows started working again.

Powershell, adding and connecting webparts

Recently had an issue with a powershell script I was writing for a sharepoint project I was working on, I needed to add two webparts to a page and connect them, they were an XsltListViewWebpart and a Nintex List Form webpart and I was following these articles:

http://social.msdn.microsoft.com/Forums/sharepoint/en-US/1cb661a0-b42f-4ec8-a42a-9a5d1fe1dce1/connect-xslt-list-view-and-query-string-filter-web-part-via-powershell?forum=sharepointdevelopment
http://troyvssharepoint.blogspot.in/2012/08/web-parts-connections-via-powershell.html

I could add the webparts to the page without any problems, but when I tried to connect them the connection wouldn’t save properly, and the problem turned out to be the i just needed to re-get the SPLimitedWebPartManager after adding the webparts, then create the connection.

And here is an example script:

add-pssnapin Microsoft.Sharepoint.Powershell
$url = http://mysiteurl
$web = Get-SPWeb $url
$wpGallery = $web.ParentWeb.Lists["Web Part Gallery"]
$wpManager = $web.GetLimitedWebPartManager($web.Url + “pages/test.aspx”,[System.Web.UI.WebControls.WebParts.PersonalizationScope]::Shared)

Write-Host ‘clearing page…’
while ($wpManager.WebParts.count -gt 0)
{
    $wpManager.DeleteWebPart($wpManager.WebParts[0])
}
$recordList = $web.Lists['Record List']
$viewFields = New-Object System.Collections.Specialized.StringCollection
$viewFields.Add(“RecordID”)
$view = $recordList.Views | where { $_.title -eq ‘RecordByID’ }
if($view -eq $null){
    Write-Host ‘creating RecordByID view…’
    $parameterBindings = ‘’
    $viewQuery = ‘{RecordID}’
    $view = $recordList.Views.Add(“RecordByID”, $viewFields, $viewQuery, 100, $True, $False, “HTML”, $False)
    $view.ParameterBindings = $parameterBindings;
    $view.Update()
}

Write-Host ‘creating filtered list view webpart…’
$recordByID = New-Object Microsoft.SharePoint.WebPartPages.XsltListViewWebPart
$recordByID.ChromeType = [System.Web.UI.WebControls.WebParts.PartChromeType]::TitleOnly
$recordByID.Title = “Record List”
$recordByID.ListName = ($recordList.ID).ToString(“B”).ToUpper()
$recordByID.ViewGuid = ($view.ID).ToString(“B”).ToUpper()
$recordByID.ParameterBindings = $parameterBindings;
$recordByID.TitleUrl = ‘$url/’ + $view.Url
$recordByID.WebId = $recordList.ParentWeb.ID
$wpManager.AddWebPart($recordByID, “Header”, 1)

Write-Host ‘creating nintex list form webpart…’
$wpl = $wpGallery.Items | where {$_.Title -eq ‘$Resources:NFResource,WebPart_List_Form_Title;’} 
$xmlReader = New-Object System.Xml.XmlTextReader($wpl.File.OpenBinaryStream()); 
$errorMsg = “” 
$webPart = $wpManager.ImportWebPart($xmlReader, [ref]$errorMsg) 
$webPart.Title = ‘List Form’;
$webPart.Mode = ‘Edit’;
$wpManager.AddWebPart($webpart,”Header”,1)

##### you must re-get the webpart manager #####
$wpManager.Dispose()
Write-Host ‘refreshing webpart manager…’
$wpManager = $web.GetLimitedWebPartManager($web.Url + “pages/test.aspx”,[System.Web.UI.WebControls.WebParts.PersonalizationScope]::Shared)
$consumerWebPart = $wpManager.WebParts | where {$_.Title -eq ‘List Form’}
$providerWebPart = $wpManager.WebParts | where {$_.Title -eq ‘Record List’}

Write-Host ‘getting connection points…’
$providerConnectionPoints = $wpManager.GetProviderConnectionPoints($providerWebPart)
$consumerConnectionPoints = $wpManager.GetConsumerConnectionPoints($consumerWebPart)
# find matching interfaces
foreach($pc in $providerConnectionPoints){
    $consumerCon = $consumerConnectionPoints | where { $_.InterfaceType -eq $pc.InterfaceType }
    if($consumerCon -ne $null){
       $providerCon = $pc
        break
    }
}

Write-Host ‘connecting webparts…’
$newCon = $wpManager.SPConnectWebParts($providerWebPart,$providerCon,$consumerWebPart,$consumerCon)
$wpManager.Dispose()
$web.dispose()

Write-Host ‘done’