Building SharePoint Online Business Solutions using Document Sets – Part 1

This series of posts I will discuss how SharePoint 2013 Document Sets and other SharePoint features can be combined to provide a robust solution for managing numerous related documents like those related to activities such as project management, legal cases, contract negotiations, business proposals or whatever.

In this set of articles we will create a typical corporate Project Management Office (PMO) solution that involves structuring and grouping all document artifacts related to a given project together with as little administrative overhead as necessary.

The sub-goals of satisfying this requirement are also:

  1. Use out-of-the-box SharePoint (no-code capabilities as much as possible)
  2. Centrally initiate new projects and ensure all have common meta data, document structures and templates
  3. Populate the project with standard PMO document templates such as Project Charter, Status Reports etc.
  4. Automatically associate all project documents together
  5. Search, aggregate, filter Projects within and across Site Collections
  6. Leverage retention policies so projects can be archived for safe keeping

Create Content Types

Document Set Content Type

Firstly, we create a “Document Set” Content Type called appropriately: “Project Document Set.” By leveraging the Document Set’s synchronization feature we can achieve quite a bit of automation without code.

The “Document Set” Content Type is an out-of-the-box SharePoint Content Type with special powers for coordinating the properties among the Content Types it manages. This coordination of Content Types allows you to treat a group of Documents as though they are one. The benefit of this is that you can manage an entire groupings of documents as one unit. For, example moving all related documents to a Records or Document Center for archiving.

For detailed information about the Document Set Content Type please refer to this article from Microsoft.

The image below illustrates the concept more clearly. The green oval represents the Document Set Content Type with Content Types it manages inside. Notice the common fields marked with the red star. The Project Document Set is configured to synchronize the common fields thereby alleviating the burden of requiring the users to complete those fields over and over again with each new document that is added to the set.

There is a design decision here that you will need to make as far as where to define your Content Types:

You can define Content Types and Site Columns in either a single Site Collection or in the central Content Type Hub. The difference is that when defined in the Content Type Hub they are available throughout all the site collections in your SharePoint Farm using the Content Type Publishing feature. Otherwise the Content Type definitions are only available in the site collection in which they were defined. For more information about Content Type Publishing and the Content Type Hub refer to this article.

I’m not going to cover what a Content Type is or how to define them in this article. If you want to learn and understand them start here.

Shared Site Columns

The “Project Document Set” Content Type will have the following fields so those need to be defined in the Site Columns:

  1. Project Name – Single Line Text, Required
  2. Project Type – Managed Metadata, Required
  3. Project ID – Single Line Text, Required
  4. Project Status – Choice

Here you will want some of the fields as required. By doing so you can later use those fields in a Documents Center or Records Center to filing your documents using automatic rules. In subsequent articles I’ll demonstrate how to archive these Project Document Sets using automatic filing rules so I am marking the Project ID, Project Type and Project Name as required.

Project Management Document Types

Next, we create Content Types for the various document types that the Document Set will manage. For this I am using a few standard PMP document templates available at http://www.projectmanagementdocs.com.

I will create a Content Type for each document template I downloaded. The Parent Content Type for each of them will simply be SharePoint’s “Document.”

  1. Charter Template
  2. Communication Plan
  3. Status Report
  4. Work Breakdown Structure

 

Although I did not do it here, I find it helpful to first create a custom base Content Type derived from “Document” (call it “Project Document” or something) and then derive all the remaining Content types from that. This provides the flexibility of easily adding additional fields to all child Content Types by simply adding columns to be base.

 

Each of the four Content Types will utilize the same site columns we defined before creating the “Project Document Set.”

  1. Project Name – Single Line Text
  2. Project Type – Managed Metadata
  3. Project ID – Single Line Text

Reusing these same fields is important because, as mentioned earlier, SharePoint will automatically synchronize these fields across all the Content Types it manages.

Here is the completed Content Type definition for “Project Charter.” The other three Content Types will be configured the same.

Associate Individual Content Types to the Document Set Content Type

Now we have our “Project Document Set” Content Type defined along with the other Content Types it will manage.

Configuring the Document Set

To configure the “Project Document Set” click it from the list of Content Types and select Document Set Settings as shown.

Here is where all the magic happens.

Allowed Content Types

The first section called “Allowed Content Types” is where you associate Content Types that the Document Set will manage. Here I have selected a group of Content Types that contains all the PMP project Content Types.

Default Content

The next section called “Default Content” defines documents that will be prepopulated when a user creates a new “Project Document Set.” This is awesome because at any time an administrator can change or remove a document template insuring new projects will use the latest templates.

To configure, just select one of the Content Type in the dropdown, specify a folder name where the template will reside and upload a document. The document file is mandatory so if you just want to create an empty folder you won’t be able to. (In another post I’ll demonstrate a remote event receiver which deletes the file so the user sees only an empty folder).

Below is the newly created Project Document Set and notice how SharePoint creates the Folder(s) specified in the “Default Content” settings.

Drilling into that folder you will find a copy of each document that was also specified.

Shared Columns

The “Shared Columns” section lists the fields in the “Project Document Set” Content Type that will be synchronized with the Content Types it manages. Simply check the fields that need to be synchronized. Not only does the synchronizer work when a new Document Set is created but it also works when you update one of the synchronized fields. For instance if, after some time a project name is changed it can be updated on the Document Set and all child items will also be updated.

Continuing from the previous image; below are the properties of the files where the Project Name, Project Type, Project ID fields synchronized from parent Document Set.

Note that word “Frozen” in the Project Name is misspelled. To clean that up all that is necessary is to update the Project Name field with the correct spelling in the Project Document Set and SharePoint will synchronize that change to all the child Content Type’s it manages!

Gotcha’s

There are two primary issues with this out-of-the-box Document Set functionality that I don’t like. I found no way around these so I eventually ended up created a Remote Event Receiver to clean them up.

The first issue is the Content Type SharePoint assigns to the sub-folders. Remember in the Document Set Setting’s “Default Content Types” section, folders and templates can deployed when a new Document Set is created. Here I noticed that the assigned Content Type is the “Project Document Set” itself which means the sub-folders are themselves “Document Sets” and not the typical “Folder” Content Types that you would expect. Having the sub-folders assigned to my “Project Document Set” Content Type is problematic because we will get incorrect search results when querying the system for Projects.

Not only was the Folder Content Type wrong but I also noticed that the Project Name, Project ID, and Project Type fields are not synchronized. I experimented by attempting to allow my “Project Document Set” to also manage the “Folder” Content Type but it cannot be selected in the “Allowed Content Types” section of the Document Set Settings. As mentioned above I eventually developed a Remote Event Receiver to update the sub-folders so that they were “Folder” Content Types and also populated the values correctly.

The next big undesirable I found when working with Document sets was the “Name” field of the Document Set Content Type. Apparently, “Document Set” Content Types are derived from the “Document Collection Folder” Content Type which in turn are derived from “Folder.” This means that a Content Type is basically a “Folder” and it has a Required “Name” field. As such, the “Project Content Type” also must have a “Name” Field which is redundant to the “Project Name” field we defined. My solution was to have the user simply enter the Project Name in the “Name” field and then the remote Event Receiver would copy the value from the “Name” to the “Project Name” field.

Up Next

In subsequent posts I’ll demonstrate how to tune the search engine to display and filter these Projects and how they can be moved in their entirety to a Documents or Records Center using automatic filing rules..

 

 

 

 

 

 

 

 

 

 

 

 

 

Release of my latest Web Project: “Check In Now”

I don’t usually get to showcase my development work because the vast majority is for internal business and intranet usage. Today, however, is an exception as we are live with a project I have worked on over the past 4 months. It is an ASP.Net, HTML5 MVC4 application which leverages Entity Framework 6, JQuery, Titter Bootstrap, oAuth, Toastr and other plugins.

The Covenant Medical Group “Check In Now” application allows patients to view current wait times and pre-register at four urgent care facilities located in Lubbock Texas. This app will be expanded to cover many more facilities and regions of the St. Joseph Health System. Patients can complete paperwork at home, ahead of their arrival time. Doing so, fast-tracks their registration process and time to treatment once they arrive. Facility administrators have the ability of updating wait times, view incoming queue of patients and send emails and SMS text messages directly to arriving patients.

https://covenantmedicalgroupcheckinnow.org

 

Analyzing Your Site’s usage

Recently I was asked if there was a web part that could added to a page which would track site visitors.

The answer to that is no.

However, each SharePoint site collection has a more powerful tool for understanding site usage. SharePoint tracks usage for you and that information is available in Site Settings Site Collection Web Analytics

Reports.   Within this area is a plethora or data including:

  • Top Pages – Pages most visited
  • Top Vistors – Users that visit your site most frequently
  • Top Queries – Search criteria that your users are using to find information on your site. This can be used in conjunction with Search Scopes and Search Keywords for fine tuning search results.
  • Trends and other statistics

 

Seeking new Web/SharePoint Leadership Opportunities

My most recent position with St. Joseph Health as Manager of Web & SharePoint development has been eliminated along with 30 or so other IT positions due to budget workforce reductions. I really enjoyed working there and have been concerned about this possibility for many months now. However, I wanted to see it through rather than leave because I really enjoyed it there.

So the good news is I am ready for the next challenge and wanted to be sure my readers were aware of this situation. I am seeking opportunities in technical leadership with focus on web and SharePoint development and strategy.

My resume can be found here http://tjo.me/13hH4lv. If you or others in your network need access to it just let me know and I’ll share it out if it is not already.

I really appreciate your support and if you hear of any openings that may be fitting please let me know so it can be evaluated.

Thanks for reading!

Sniffing SharePoint for Enterprise License Feature Usage

SharePoint Enterprise features provide users the ability to use advanced capabilities not found in standard SharePoint licensing. Once you have licenses for Enterprise features all you do is enable them on the site collections and users are free to consume them.

Once enabled, however, there is no easy way to determine if they are being actually used. Enterprise license are pretty expensive so you want to be sure you are getting your money’s worth.

One, painful way to determine usage is to manually thumb around sites looking for things like Excel or Visio Web Parts, InfoPath forms, Content Organizer Rules, Data Connection Libraries or Power Pivot libraries. That is not practical, nor accurate, nor fun.

In my case I have no fancy commercial tools to manage and administer my farm so I often resort to rolling my own PowerShell scripts. The following are some functions I wrote to detect and document the instances of Enterprise feature usages.

The first thing you need is a loop that iterate down through your farm Site Collections and sites. Something like this usually works fine.

$rootSite = New-Object Microsoft.SharePoint.SPSite("http://my.spdomain.org") ;
$spWebApp = $rootSite.WebApplication;
$allSites = $spWebApp.Sites;
 
foreach($site in $allSites){
 
    write-host "Site: " $site.Url;
 
    $subWebs = $site | Get-SPWeb -Limit all;	
 
    foreach($web in $subWebs){
        write-host "Web: " $web.Url;
 
        #--------------------------
        # Put detection code here
        #--------------------------
 
        $web.Dispose();
        $web = $null;
    }
 
    $subWebs.Dispose;
    $subWebs = $null;
 
    $site.Dispose();
    $site=$null
}
 
$allSites.Dispose;
$allSites = $null;
 
$spWebApp = $null;

Once this main loop is in place we can insert detection functions which analyze SPList objects of each site. With the help of some online research below are the functions I came up with. All of the functions accept a site URL and iterates over the SPList objects to determine if the condition I am looking for is found. If a SPList object contains the feature I seek, it is added to an ArrayList and returned to the calling main loop. The main loop collects all ArryLists and then exports them to CSV or whatever you want to do with them. I export to CSV and put those in MS Access for creating nice reports.

Detecting PowerPivot Libraries

The trick to this function is to analyze the TemplateFeatureId property of a document library and compare it to the feature ID’s for PowerPivot. The PowerPivot feature Id’s are specified in the code.

function Get-PowerPivotLibs
(
    [System.String]$siteUrl = "http://my.spdomain.org/team/Team_Site_Test"
)
{
 
$powerPivotFeatures = @("1a33a234-b4a4-4fc6-96c2-8bdb56388bd5", "e9c4784b-d453-46f5-8559-3c891d7159dd", "f8c51e81-0b46-4535-a3d5-244f63e1cab9")
 
# ArrayList to hold any found PowerPivot Libs
[System.Collections.ArrayList]$PPLibs = New-Object System.Collections.ArrayList($null)
 
$libs = Get-SPWeb $siteUrl |
    Select -ExpandProperty Lists |
    Where { $_.GetType().Name -eq "SPDocumentLibrary" -and -not $_.hidden }
 
foreach($lib in $libs)
{
    if(($powerPivotFeatures -contains $lib.TemplateFeatureId))
    {
        $PPLibs.Add($lib) > $null
        Write-Host -BackgroundColor Blue -ForegroundColor White "PowerPivot Lib Part Found" 
    }
}
 
return, $PPLibs;
 
}

Detecting Data Connection Libraries

This method is the same as used for detecting PowerPivot Libraries with the exception of a different feature Id.

function Get-DataConnectionLibs
(
    [System.String]$siteUrl = "http://my.spdomain.org/team/Team_Site_Test/InternalTeam/DocCtr/"
)
{
Write-Host -ForegroundColor Gray "Searching for DataConnection Libraries: "  $siteUrl
$dataConnFeatures = @("00bfea71-dbd7-4f72-b8cb-da7ac0440130")
 
# ArrayList to hold any found DataConn Libs
[System.Collections.ArrayList]$DCLibs = New-Object System.Collections.ArrayList($null)
 
$libs = Get-SPWeb $siteUrl |
    Select -ExpandProperty Lists |
    Where { $_.GetType().Name -eq "SPDocumentLibrary" -and -not $_.hidden }
 
foreach($lib in $libs)
{
    if(($dataConnFeatures -contains $lib.TemplateFeatureId))
    {
        $DCLibs.Add($lib)>$null;
    }
}
 
return, $DCLibs;
 
}

Detecting Excel Web Parts

This one is a bit more complicated and requires first determining if the function is dealing with a Publishing site or not. For Publishing sites we have to get something called a “LimitedWebPartManager” from each SPListItem in the Pages library. The “LimitedWebPartManager” allows you to iterate over each web part on the page to detect if the any web part of type “ExcelWebRenderer.”

If we are not dealing with a Publishing site, then function looks through all SPList objects that have a “BaseTemplate” property of “WebPageLibrary.” Again using the “LimitedWebPartManager” we look for the same “ExcelWebRenderer” type web parts.

When we find even one “ExcelWebRenderer” web part in a page we add the SPList to the ArrayList which is returned to the calling loop.

function Get-ExcelWebParts
(
    [System.String]$siteUrl = "http://my.spdomain.org/team/Team_Site_Test/"
 
    # Reference Sites: 
    # http://sharepointpromag.com/sharepoint/windows-powershell-scripts-sharepoint-info-files-pagesweb-parts
    # http://support.microsoft.com/kb/2261512
 
)
{
Write-Host -ForegroundColor Gray  "Searching for Excel Web Parts: "  $siteUrl
$ExcelWebRenderer = "ExcelWebRenderer";	
 
# ArrayList to hold any found Exel Web part page s
[System.Collections.ArrayList]$ExcelWps = New-Object System.Collections.ArrayList($null)
 
$web = Get-SPWeb $siteUrl
$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{
            $manager = $item.file.GetLimitedWebPartManager([System.Web.UI.WebControls.Webparts.PersonalizationScope]::Shared);
            $wps = $manager.webparts
            $webPartCount = 0;
 
                foreach($wp in $wps)
                {
                    if($wp.GetType().Name -eq $ExcelWebRenderer){
                        $webPartCount++;
                        Write-Host -BackgroundColor Blue -ForegroundColor White "Excel Web Part Found" 
                    }
                }
 
            if($webPartCount -gt 0)
                {$ExcelWps.Add($pages) > $null;}
        }
        catch [system.exception]{
            #Write-Host -ForegroundColor Red -BackgroundColor Black "Error"
        }
    }
 
} else {
    foreach($list in $lists)
    {	
        if($list.BaseTemplate -eq "WebPageLibrary")
        {
            foreach ($item in $list.Items) {
                try{
                    $manager = $item.file.GetLimitedWebPartManager([System.Web.UI.WebControls.Webparts.PersonalizationScope]::Shared);
                    $wps = $manager.webparts
                    $webPartCount = 0;
 
                    foreach($wp in $wps)
                    {
                        if($wp.GetType().Name -eq $ExcelWebRenderer){
                            $webPartCount++;
                            Write-Host -BackgroundColor Blue -ForegroundColor White "Excel Web Part Found" 
                        }
 
                    } 
 
                    if($webPartCount -gt 0)
                        {$ExcelWps.Add($list) > $null;}
 
                }catch [system.exception]{
                    #Write-Host -ForegroundColor Red -BackgroundColor Black "Error"
                }
            }
        }
    }
}
 
$web.Dispose();
 
return, $ExcelWps;
 
}

Detecting Content Organizer Rules

Content Organizing Rules are Enterprise features usually found in Document or Record Centers. These rules are created by users to automatically route documents to their proper folder based on Meta data on the document. Look it up if you are not sure what they are I think you will find them pretty cool.

Content Organizer Rules are stored in HIDDENs list using a content type named “Rule” so we just iterate over the Hidden SPList objects looking to see if the “Rule” content type is being used. If it is, the SPList is put in my return ArrayList.

function Get-ContentOrganizerRules
(
    [System.String]$siteUrl = "http://my.spdomain.org/team/Team_Site_Test/InternalTeam/DocCtr/"
 
)
{
Write-Host -ForegroundColor Gray "Searching for Content Organizer Rules: "  $siteUrl
 
# ArrayList to hold any found DataConn Libs
[System.Collections.ArrayList]$CORules = New-Object System.Collections.ArrayList($null)
 
$lists = Get-SPWeb $siteUrl |
    Select -ExpandProperty Lists |
    Where { $_.GetType().Name -eq "SPList" -and  $_.hidden }
 
foreach($list in $lists)
{
    #Write-Host $list ;
 
    foreach($contenType in $list.ContentTypes){
        if($contenType -ne $null){
            if($contenType.Id.ToString() -eq "0x0100DC2417D125A4489CA59DCC70E3F152B2000C65439F6CABB14AB9C55083A32BCE9C" -and $contenType.Name -eq "Rule")
            {
                $CORules.Add($list)>$null;
                Write-Host -BackgroundColor Green -ForegroundColor White "Content Organizer Rule found: " $list.Url>$null;
            }
        }
    }
}
 
return, $CORules;
 
}

Detecting InfoPath Forms

There is probably a cleaner way to detect usage of InfoPath forms but this was what I found in a pinch. In my environment it worked for at least a very large portion of InfoPath usages. I noticed that when you examine a list that has a custom InfoPath form instead of the default, SharePoint Designer will display alongside the default NewForm.aspx and EditForm.aspx additional forms called newifs.aspx and editifs.aspx respectively. I assumed that the “ifs” suffix stands for “InfoPath Form Services” so I set out to detect those. In addition, Form Libraries can also use InfoPath forms and those can be detected by inspecting their BaseTemplate property. If “XMLForm” is the BaseTemplate then an InfoPath Form is being used.

function Get-InfoPathLists
(
	[System.String]$siteUrl = "http://teams.stjoe.org/team/Team_Site_Test"
)
{
	# ArrayList to hold any found Lists & Libs
	[System.Collections.ArrayList]$IFPLibs = New-Object System.Collections.ArrayList($null)
 
	$listsAndLibs = Get-SPWeb $siteUrl |
	   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" ) 
				{
					$IFPLibs.Add($list)>$null;
					Write-Host -BackgroundColor Green -ForegroundColor White "InfoPath List found: " $list.Url >$null;
				}
			}
			{"GenericList" -or "Survey"} 
			{
				if($list.Forms -ne $null){
					foreach($form in $list.Forms){
						if($form.Url.EndsWith("ifs.aspx"))
						{
							$IFPLibs.Add($list)>$null;
							Write-Host -BackgroundColor Green -ForegroundColor White "InfoPath List found: " $list.Url>$null;
							break;
						}
					}
				}
			}
			default {
				Write-Host -BackgroundColor Red -ForegroundColor White "Unexpected List BaseType found: " $list.BaseType. $list.Url >$null;
			}
		}
	}
 
	return, $IFPLibs;
}

Exporting to CSV

As mentioned before, the main loop collects all the ArrayLists returned by the detection functions for export to CSV (in my case pipe delimited rather than comma). Just select the properties of the SPList and send them to the Export-CSV commandlet. Since all the CSV’s have the same columns just import them into Excel or Access and make your report.

$InfoPathResults | Select URL, Title, ItemCount, Author, Created, LastItemModifiedDate | Export-Csv  C:\eCalSniffereCalSniffer\InfoPathResults.csv -NoTypeInformation -Encoding UTF8 -Delimiter '|'
$COResults | Select URL, Title, ItemCount, Author, Created, LastItemModifiedDate | Export-Csv  C:\eCalSniffereCalSniffer\COResults.csv -NoTypeInformation -Encoding UTF8 -Delimiter '|'
$DCResults | Select URL, Title, ItemCount, Author, Created, LastItemModifiedDate | Export-Csv  C:\eCalSniffereCalSniffer\DCResults.csv -NoTypeInformation -Encoding UTF8 -Delimiter '|'
$ExcelResults | Select URL, Title, ItemCount, Author, Created, LastItemModifiedDate | Select URL, Title, ItemCount, Author, Created, LastItemModifiedDate | Export-Csv  C:\eCalSniffereCalSniffer\ExcelResults.csv -NoTypeInformation -Encoding UTF8 -Delimiter '|'
$VisioResults | Select URL, Title, ItemCount, Author, Created, LastItemModifiedDate | Export-Csv  C:\eCalSniffereCalSniffer\VisioRsults.csv -NoTypeInformation -Encoding UTF8 -Delimiter '|'
$PPivotResults | Select URL, Title, ItemCount, Author, Created, LastItemModifiedDate | Export-Csv  C:\eCalSniffereCalSniffer\PPivotResults.csv -NoTypeInformation -Encoding UTF8 -Delimiter '|'
$PPivotResults | Select URL, Title, ItemCount, Author, Created, LastItemModifiedDate | Export-Csv  C:\eCalSniffereCalSniffer\All.csv -NoTypeInformation -Encoding UTF8 -Delimiter '|'

Final Code

#References
. C:\eCalSniffereCalSniffer\get-InfopathLists.ps1
. .\get-InfopathLists.ps1
. .\Get-ContentOrganizerRules.ps1
. .\Get-DataConnectionLibs.ps1
. .\Get-ExcelWebParts.ps1
. .\Get-VisioWebParts.ps1
. .\Get_PowerPivotLibs.ps1
. .\Results-To-CSV.ps1
 
    $siteUrls = @("http://my.spdomain.org","http://carenet.stjoe.org");
 
    [System.Collections.ArrayList]$InfoPathResults = New-Object System.Collections.ArrayList($null)
    [System.Collections.ArrayList]$COResults = New-Object System.Collections.ArrayList($null)
    [System.Collections.ArrayList]$DCResults = New-Object System.Collections.ArrayList($null)
    [System.Collections.ArrayList]$ExcelResults = New-Object System.Collections.ArrayList($null)
    [System.Collections.ArrayList]$VisioResults = New-Object System.Collections.ArrayList($null)
    [System.Collections.ArrayList]$PPivotResults = New-Object System.Collections.ArrayList($null)
    [System.Collections.ArrayList]$AllResults = New-Object System.Collections.ArrayList($null)
 
    foreach($siteUrl in $siteUrls)
    {
        $rootSite = New-Object Microsoft.SharePoint.SPSite($siteUrl) 
 
        $spWebApp = $rootSite.WebApplication
 
        $count = 0
 
        write-host -ForegroundColor Gray "Site Collections"
        write-host ""
 
        foreach ($site in $spWebApp.Sites) 
        {
                $count++
                write-host -ForegroundColor Gray "Site Collections URL: " $site.URL
                write-host ""
                write-host -ForegroundColor Gray "SubSites"
 
                foreach ($web in $site.AllWebs) 
                {
                    $count++
                    write-host -ForegroundColor  Gray "SubSite URL: " $web.URL 
 
                    $InfoPathScan = get-InfopathLists $web.URL;
                    if($InfoPathScan.Count -gt 0){
                        $InfoPathResults.AddRange($InfoPathScan);
                    }
 
                    $CORulesScan = Get-ContentOrganizerRules $web.URL;
                    if($CORulesScan.Count -gt 0){
                        $COResults.AddRange($CORulesScan);
                    }
 
                    $DataConnScan = Get-DataConnectionLibs $web.URL;
                    if($DataConnScan.Count -gt 0){
                        $DCResults.AddRange($DataConnScan);
                    }
 
                    $ExcelWpsScan = Get-ExcelWebParts $web.URL;
                    if($ExcelWpsScan.Count -gt 0){
                        $ExcelResults.AddRange($ExcelWpsScan);
                    }
 
                    $VisioWpsScan = Get-VisioWebParts $web.URL;
                    if($VisioWpsScan.Count -gt 0){
                        $VisioResults.AddRange($VisioWpsScan);
                    }
 
                    $PowerPivotScan = Get-PowerPivotLibs $web.URL;
                    if($PowerPivotScan.Count -gt 0){
                        $PPivotResults.AddRange($PowerPivotScan);
                    }
                } 
 
                $web.Dispose()
 
        }
 
        $site.Dispose()
        $rootSite.Dispose()
 
    }
 
    write-host "Total Count :" $count
    $AllResults.AddRange($InfoPathResults);
    $AllResults.AddRange($COResults);
    $AllResults.AddRange($DCResults);
    $AllResults.AddRange($ExcelResults);
    $AllResults.AddRange($VisioResults);
    $AllResults.AddRange($PPivotResults);
 
    Write-Host "Exporting CSV"
    Results-To-CSV

PowerShell function to delete all SharePoint List Items

After killing myself for a few hours creating this I wanted to share it out in hopes of saving you some time.  This PowerShell function will delete every item in your SharePoint list and put it into the recycle bin.   Just pass in the SPList where the

The lesson learned for me is to properly escape your quotation marks in the XML CAML query.   Many examples of this script can be found online using c# but in my case I am using PowerShell which has a different way to escape characters.    PowerShell uses the backtick character “`”  which is like an apostrophe but backwards and typically located to the left of “1” key on your keyboard.

Notice how the quotes are escaped on line 7 below.  Using double quotes or backslash before the double quotes will result in an agonizing and so descript error message when line 17 is called.

   1: function DeleteAllSPListItems
   2: {
   3:     param (    [Microsoft.SharePoint.SPList] $spList_list );
   4:     
   5:     [System.Text.StringBuilder]$deleteBldr = New-Object "System.Text.StringBuilder";
   6:     $deleteBldr.Append("<?xml version=`"1.0`" encoding=`"UTF-8`"?><Batch>");
   7:     $command = [System.String]::Format( "<Method><SetList Scope=`"Request`">{0}</SetList><SetVar Name=`"ID`">{1}</SetVar><SetVar Name=`"Cmd`">Delete</SetVar></Method>", $spList_list.ID, "{0}" );
   8:     
   9:     foreach ($item in $spList_list.Items)
  10:     {
  11:         if($item -ne $null)
  12:             {$deleteBldr.Append([System.String]::Format($command, $item.ID.ToString()));}
  13:     }
  14:     
  15:     $deleteBldr.Append("</Batch>");
  16:     
  17:     $spWeb.ProcessBatchData($deleteBldr.ToString());
  18: }

Here is the error message you will continue to get until you do it right.

Exception calling "ProcessBatchData" with "1" argument(s): "<nativehr>0x80070057</nativehr><nativestack></nativestack>"
At line:1 char:24
+ $spWeb.ProcessBatchData <<<< ("<?xml version='1.0' encoding='UTF-8'?><Batch><Method><SetList Scope='Request'>b37425b3-189d-4031-97f6-15e4ca767185</SetList><SetVar Name='ID'>480</SetVar><SetVar Name='Cmd'>Delete</SetVar></Method></Batch>");
+ CategoryInfo : NotSpecified: (:) [], MethodInvocationException
+ FullyQualifiedErrorId : DotNetMethodException


Cheers!

SharePoint Security Inheritance is Unforgiving

If you manage a SharePoint team site that contains sub-sites you may want to configure them with unique permissions. This is usually desired because a top level site caters to a broader or public audience while lower sub sites have a narrower group of users for departmental or project team members only.

There is one major pain-point you need to be aware of:

 

If you break security inheritance so that your sub-site no longer uses that same users and groups as the upper-level parent site and then, accidentally re-apply the inheritance; you will never get your unique security settings back again! You will have no choice but to manually recreate them.

 

Lets explain in detail. 

When you create a new sub-site you can choose to use or ignore the security settings from the parent.

image

When you choose to use unique permissions the create wizard will ask you to use existing security groups or create new ones.  Typically you will create the basic Visitors, Members and Owners groups (you can always alter as you desire an any time after the site is created).

 

image

After the site is created you can observe the created Permission Groups by navigating to “Site Actions” –> “Site Permission”.  Notice the yellow bar which indicates that the web site has unique permissions.  That warning means the site is NOT inheriting permissions from the parent site and is completely unique.

image

From here, the site administrator can begin adding users and groups to this site; but here is the kicker!

If, at anytime after you have spent a load of time setting up your security, you click the “Inherit Permissions” button (and confirm in the subsequent dialog box) all your work will be lost! 

image

 

It is unforgiving!

Effectively Sharing SharePoint Calendars Across Time Zones

Sharing SharePoint calendars across time zones can cause confusion among users because SharePoint won’t adjust it for users without some intervention.

First, it is interesting to know that when SharePoint Web Applications are created they can be configured to assume to operate in any time zone.  The configured time zone can be equal or different from the time setting on the server in which it operates.  This means a SharePoint farm physically located across the world can be configured to use the US Pacific Time time zone.  So lets assume this is the case for the purpose of this article.

Imagine a user within the Pacific Time zone interacting with a SharePoint calendar adds an event for 10 AM next Monday involving a conference call.  Now image Monday comes along and another user who is located in US Eastern Time zone dials into the conference call precisely at 10 AM only to find the organizing user not there.  The reason the organizer was not on the line is because it is actually 7 AM Pacific Time!

So how does the poor East coast user configure SharePoint to display appointments in his time?  The secret is he must change his personal settings so let me show you how.

The user in the Eastern Time zone must adjust his personal SharePoint settings in order for SharePoint to automatically adjust times local to him.  The personal settings are found in the drop down menu associated with your login name.

image

 

Next select “My Regional Settings”

SelectRegionalSettings

 

Then uncheck the “Always follow web Settings.”  The default setting for this option will be checked so just uncheck it and configure the time zone to Eastern Time. 

 

image

 

Once this is configured for the user in the Eastern Time zone the calendar items will be adjusted according to Eastern time which in this case would correctly be Monday 1 PM.

It is important to note that this setting is only effective in the site collection it which it was set.  This means that if there are other site collections the Eastern Time zone user interacts with the he needs to repeat the above steps in each site collection to avoid future confusion.

Debugging PowerPivot GetSnapShot.exe

 

PowerPivot Gallaries in SharePoint 2010 utilizes a program called GetSnapShot.exe that executes when you add or change a property on PowerPivot Excel library item.  The purpose of this exe is to create a snap shot of your Excel files innards which the Silverlight control displays for the users like so:

PowerPivotSnapShot

The GetSnapShot process can be seen operating in the Windows Task Manager of your Web Front end.  It will usually run about a 40 to 120 seconds depending and then go away.

GetSnapshotProcess

If the GetSnapShot.exe fails your snapshot image will not look like it does above but will have a noticeable red ‘X’. 

PowerPivotSnapShotFailed

Upon failure the GetSnapShot.exe writes an [guid].info file in the c:\users\[current user]\AppData\Local\Temp folder.  This can be used to help identify what is causing the failure.

I am not documenting all the different reasons this process would fail but instead hope that this post will help you fix your specific problem..

In my case, the [guid].info file contained the following:

<SnapshotCaptureLog serverUrl=http://myserver:10003/team/CorpWebIntgtn/WebDev workbookUrl=”http://myserver:10003/team/CorpWebIntgtn/WebDev/Power Pivot Gallary/NorthwindProductAndCustomers1.xlsx” fileNameBase=”6cb015f9_de68_4b61_a940_90d6abd2f9e3″ snapshotCount=”26″ timeout=”300″>
  <Error timeout=”True”>System.TimeoutException: The operation has timed out.</Error>
</SnapshotCaptureLog>

In my SharePoint environment we script out the creation of our web application using a strait forward name and port number incremented by 1 starting at 10,000.  We then use SharePoint Alternate Access Mappings and Host Names in IIS to create a  nicer URL in our DNS.  

Here, in the [guid].info file we can see that the GetSnapShot.exe is using the serverUrl=http://myserver:10003…   That was the problem for us because we don’t ever use that URL and it is not in our corporate DNS so the server could not find it.  The solution was to simply add that URL to the Web Front End’s C:\Windows\System32\drivers\etc\host file and the problem was solved.