Category Archives: SharePoint

Sniffing SharePoint for Enterprise License Feature Usage

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-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;
```
```
 
```
```
}
```

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

1 Reply

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

Debugging PowerPivot GetSnapShot.exe

20 Replies

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:

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.

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

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.

Remove-SPSolution cmdlet does not remove class references in Farm Config DB

Tim Odell's Blog

SharePoint Technology and other stuff