Pages

11 November 2011

Lock or Unlock all Site Collection in a Web Application

ISSUE DESCRIPTION:
For many reason, like a maintenance window on a SharePoint Farm, you might want to lock all the site collection from a Web Application

SOLUTION:
I have created an automated script that will do that for you.

SPSiteCollectionLockScript:

param (
    [string]$WebAppUrl = $(throw "Missing WebApp URL (please use -webappurl [URL])"),
    [string]$LockState= $(throw "Missing LockState (please use -lockstate [Unlock/ReadOnly])")
)

#Addin SharePoint2010 PowerShell Snapin
Add-PSSnapin Microsoft.SharePoint.PowerShell -erroraction SilentlyContinue

$WebApp = Get-SPWebApplication $WebAppUrl
$AllSites = $WebApp | Get-SPSite

#Set each SiteCollection with the lockstate defined
foreach ($Site in $AllSites)
    {
    Write-Host "Setting " -nonewline
    Write-Host $Site.url -ForegroundColor Yellow -nonewline
    Write-Host " to $lockState..." -nonewline
    Set-SPSiteAdministration -LockState $lockState -Identity $Site.url
    Write-Host "[OK]" -foregroundcolor green
    }

Paste the above code in a PowerShellScript file, like [SPSiteCollectionLock.ps1] and copy it on your local SharePoint Server Drive.

Launch the script from a Windows Powershell Cmd Prompt using the following parametters:
.\SPSiteCollectionLock.ps1 -webappurl <URL> -lockstate [Unlock/ReadOnly]



Et voila !

25 October 2011

Migrated Alerts contains wrong URL

ISSUE DESCRIPTION:
In a context of a sharepoint 2007 Migration to SharePoint 2010, you have changed the URLs of your webapplication (example from http://portal.domain.com to http://sharepoint.domain.com)
Then the alerts received by the users contains the previous url in the links.

SOLUTION:
I have created an automated script that will verify the alerts properties for a SiteCollection, and another script that can update the URLs with the new created ones.

CheckAlertsUrlScript:

param (
        [string]$url = $(throw "Missing Site Collection URL (please use -url [sitecollectionUrl])")
      )
#Addin SharePoint2010 PowerShell Snapin
Add-PSSnapin -Name Microsoft.SharePoint.PowerShell

$webs = Get-SPweb -site $url -limit all
    foreach ($event in $webs)
    {
    $alerts = $event.alerts
        foreach ($event1 in $alerts)
        {
        write-host $event1.user $event1.properties["siteUrl"] $event1.properties["dispformurl"] -foregroundcolor green
        }
    }

Paste the above code in a PowerShellScript file, like [CheckAlertsUrl.ps1] and copy it on your local SharePoint Server Drive.

Launch the script from a Windows Powershell Cmd Prompt using the following parametters:
.\CheckAlertsUrl.ps1 -url <sitecollectionUrl>

UpdateAlertsUrlScript:
param (
        [string]$url = $(throw "Missing Site Collection URL (please use -url [sitecollectionUrl])"),
        [string]$newurl = $(throw "Missing New main URL (please use -newurl [MainUrl])")
      )
     
#Addin SharePoint2010 PowerShell Snapin
Add-PSSnapin -Name Microsoft.SharePoint.PowerShell

$webs = Get-SPweb -site $url -limit all
    foreach ($event in $webs)
    {
    $alerts = $event.alerts
   
        foreach ($event1 in $alerts)
        {
        write-host $event1.user $event1.properties["siteUrl"] $event1.properties["dispformurl"]
        $event1.properties["siteUrl"] = $newurl
        $event1.update()
        write-host "Alert has been updated successfully with correct URL" $event1.properties["siteUrl"] -foregroundcolor green
        }
    }
Paste the above code in a PowerShellScript file, like [UpdateAlertsUrl.ps1] and copy it on your local SharePoint Server Drive.

Launch the script from a Windows Powershell Cmd Prompt using the following parametters:
.\UpdateAlertsUrl.ps1 -url <sitecollectionUrl> -newurl <NewMainUrl>

24 October 2011

Get all the Columns created

ISSUE DESCRIPTION:
Get a list of all created columns in a Site Collection.

Why would you want this ? 
Well in my case I got a BambooLookup Site Collection feature enable on a SharePoint 2007 Site, and I did not know where to find the created column using this feature.
My goal was to remove those column for a clean Migration to SharePoint 2010.


SOLUTION:
The solution is obviously to create a Powershell script.
param (
    [string]$Url = $(throw "Missing Site Collection Url (please use -url [URL])")
)

#Add Powershell CmdLet & Assembly
Add-PSSnapin -Name Microsoft.SharePoint.PowerShell
[System.Reflection.Assembly]::LoadWithPartialName("Microsoft.SharePoint")

#Declare Log File
Function StartTracing
{
    $LogTime = Get-Date -Format yyyy-MM-dd_h-mm
    $script:LogFile = "ListAllColumnOutput-$LogTime.csv"
    Start-Transcript -Path $LogFile -Force
}

$site = new-object Microsoft.SharePoint.SPSite($url)

StartTracing

write-host "Web;List;ColTitle;ColId;ColType;ColType2" -foregroundcolor cyan

Foreach($web in $site.AllWebs)
{
    foreach ($list in $web.Lists)
    {
        foreach ($field in $list.Fields)
        {
        $Stringtype = $field.typeasstring
        $Name = $field.gettype().Name
        write-host $web.url -nonewline -foregroundcolor green
        write-host ";" -nonewline
        write-host $list.title -nonewline -foregroundcolor green
        write-host ";" -nonewline
        write-host $field.title -nonewline -foregroundcolor green
        write-host ";" -nonewline
        write-host $field.id  -nonewline -foregroundcolor green
        write-host ";" -nonewline
        write-host $Stringtype  -nonewline -foregroundcolor green
        write-host ";" -nonewline
        write-host $Name -foregroundcolor green
        }
    }
    $web.Dispose();
}
Stop-Transcript

Paste the above code in a PowerShellScript file, like [ListAllSiteColumns.ps1] and copy it on your local SharePoint Server Drive.

Launch the script from a Windows Powershell Cmd Prompt using the following parametters:
.\ListAllSiteColumns.ps1 -url <sitecollectionUrl>

After execution, the script generates a CSV output file in the same folder with details about the Columns (WebUrl/ListName/ColumnTitle/ColumnId/ColumnType/..).

Import the CSV file to excel and then you can filter / Sort the information easily to find what you are looking for.

19 October 2011

Solving [MissingAssembly] errors from SharePoint Health Analyzer

ISSUE DESCRIPTION:
After a SharePoint 2007 Migration to SharePoint 2010, on the Central Administration, the SharePoint Health Analyzer is generating the critical event [Missing server side dependencies] :

And the event details is generating a lot of [MissingAssembly] errors, examples:
[MissingAssembly] Assembly [DocumentReviewEventHandler, Version=14.0.0.0, Culture=neutral, PublicKeyToken=71e9bce111e9429c] is referenced in the database [Sharepoint_80_Content_01], but is not installed on the current farm. Please install any feature/solution which contains this assembly. One or more assemblies are referenced in the database [Sharepoint_80_Content_04], but are not installed on the current farm. Please install any feature or solution which contains these assemblies.
[MissingAssembly] Assembly [KnowledgeBaseEventHandler, Version=14.0.0.0, Culture=neutral, PublicKeyToken=71e9bce111e9429c] is referenced in the database [Sharepoint_80_Content_02], but is not installed on the current farm. Please install any feature/solution which contains this assembly. One or more assemblies are referenced in the database [Sharepoint_80_Content_04], but are not installed on the current farm. Please install any feature or solution which contains these assemblies.
[MissingAssembly] Assembly [Nintex.Workflow.Features, Version=1.0.0.0, Culture=neutral, PublicKeyToken=913f6bae0ca5ae12] is referenced in the database [Sharepoint_80_Content_03], but is not installed on the current farm. Please install any feature/solution which contains this assembly. One or more assemblies are referenced in the database [Sharepoint_80_Content_04], but are not installed on the current farm. Please install any feature or solution which contains these assemblies.

ROOT CAUSE:

Usualy, finding MissingAssembly errors appear as the result of an event receiver, which is still registered on a list or library but part of a feature/solution no longer present on the farm.
In my case, I have tried to clean up the SharePoint 2007 Sites before the migration by disabling and uninstalling the unused solutions, but most of the times, the assembly references are not getting removed correctly.

SOLUTION:
The easy solution is obviously to install the features/solution related to those assembly, but if you are in the same situation as me, you don't really need the solution/feature anymore and you just want the database to be clean and get rid of these events.

To safely remove the assembly references, we need to be able to identify their specific location on the Farm, I have created an automated PowerShell script based on Phil's article (Thanks Phil), this script will get all the required information for you.

Note: The script is only analyzing Assembly related to event receiver deployed at the level SiteCollection / Site / List, therefore if no information is generated for a reference, you may need to adapt the script to enlarge the research scope (based on the HostId type / check script comments)

param (
    [string]$DBserver = $(throw "Missing server name (please use -dbserver [dbserver])"),
    [string]$path = $(throw "Missing input file (please use -path [path\file.txt])")
)

#Set Variables
$input = @(Get-Content $path)

#Addin SharePoint2010 PowerShell Snapin
Add-PSSnapin -Name Microsoft.SharePoint.PowerShell

#Declare Log File
Function StartTracing
{
    $LogTime = Get-Date -Format yyyy-MM-dd_h-mm
    $script:LogFile = "MissingAssemblyOutput-$LogTime.csv"
    Start-Transcript -Path $LogFile -Force
}

#Declare SQL Query function
function Run-SQLQuery ($SqlServer, $SqlDatabase, $SqlQuery)
{
$SqlConnection = New-Object System.Data.SqlClient.SqlConnection
$SqlConnection.ConnectionString = "Server =" + $SqlServer + "; Database =" + $SqlDatabase + "; Integrated Security = True"
$SqlCmd = New-Object System.Data.SqlClient.SqlCommand
$SqlCmd.CommandText = $SqlQuery
$SqlCmd.Connection = $SqlConnection
$SqlAdapter = New-Object System.Data.SqlClient.SqlDataAdapter
$SqlAdapter.SelectCommand = $SqlCmd
$DataSet = New-Object System.Data.DataSet
$SqlAdapter.Fill($DataSet)
$SqlConnection.Close()
$DataSet.Tables[0]
}


function GetAssemblyDetails ($assembly, $DBname)
    {

    #Define SQL Query and set in Variable
    $Query = "SELECT * from EventReceivers where Assembly = '"+$assembly+"'"
    #$Query = "SELECT * from EventReceivers where Assembly = 'Microsoft.Office.InfoPath.Server, Version=14.0.0.0, Culture=neutral, PublicKeyToken=71e9bce111e9429c'" 

    #Runing SQL Query to get information about Assembly (looking in EventReceiver Table) and store it in a Table
    $QueryReturn = @(Run-SQLQuery -SqlServer $DBserver -SqlDatabase $DBname -SqlQuery $Query | select Id, Name, SiteId, WebId, HostId, HostType)

    #Actions for each element in the table returned
        foreach ($event in $QueryReturn)
        {   
            #HostID (check http://msdn.microsoft.com/en-us/library/ee394866(v=prot.13).aspx for HostID Type reference)
            if ($event.HostType -eq 0)
             {
             $site = Get-SPSite -Limit all | where {$_.Id -eq $event.SiteId}
             #Get the EventReceiver Site Object
             $er = $site.EventReceivers | where {$_.Id -eq $event.Id}
             
             Write-Host $assembly -nonewline -foregroundcolor yellow
             write-host ";" -nonewline
             write-host $site.Url -nonewline -foregroundcolor gray
             write-host ";" -nonewline
             write-host $er.Name -foregroundcolor green -nonewline
             write-host ";" -nonewline
             write-host $er.Class -foregroundcolor cyan
             #$er.Delete()
             }
             
             if ($event.HostType -eq 1)
             {
             $site = Get-SPSite -Limit all | where {$_.Id -eq $event.SiteId}
             $web = $site | Get-SPWeb -Limit all | where {$_.Id -eq $event.WebId}
             #Get the EventReceiver Site Object
             $er = $web.EventReceivers | where {$_.Id -eq $event.Id}
             $er.Name
             
             Write-Host $assembly -nonewline -foregroundcolor yellow
             write-host ";" -nonewline
             write-host $web.Url -nonewline -foregroundcolor gray
             write-host ";" -nonewline
             write-host $er.Name -foregroundcolor green -nonewline
             write-host ";" -nonewline
             write-host $er.Class -foregroundcolor cyan
             #$er.Delete()
             }
             
             if ($event.HostType -eq 2)
             {
             $site = Get-SPSite -Limit all | where {$_.Id -eq $event.SiteId}
             $web = $site | Get-SPWeb -Limit all | where {$_.Id -eq $event.WebId}
             $list = $web.Lists | where {$_.Id -eq $event.HostId}
             #Get the EventReceiver List Object
             $er = $list.EventReceivers | where {$_.Id -eq $event.Id}
             
             Write-Host $assembly -nonewline -foregroundcolor yellow
             write-host ";" -nonewline
             write-host $web.Url -nonewline -foregroundcolor gray
             write-host "/" -nonewline -foregroundcolor gray
             write-host $list.RootFolder -nonewline -foregroundcolor gray
             write-host ";" -nonewline
             write-host $er.Name -foregroundcolor green -nonewline
             write-host ";" -nonewline
             write-host $er.Class -foregroundcolor cyan
             #$er.Delete()
             }
              
        }
    }

#Start Logging
StartTracing

#Log the CVS Column Title Line
write-host "Assembly;Url;EventReceiverName;EventReceiverClass" -foregroundcolor Red

foreach ($event in $input)
    {
    $assembly = $event.split(";")[0]
    $DBname = $event.split(";")[1]
    GetAssemblyDetails $assembly $dbname
    }
    
#Stop Logging
Stop-Transcript


Paste the above code in a PowerShellScript file, like [MissingAssemblyDetails.ps1] and copy it on your local SharePoint Server Drive.

Launch the script from a Windows Powershell Cmd Prompt using the following parametters:
.\MissingAssemblyDetails.ps1 -DBserver <SQLServerName> -path <fullpath\missingassembly.txt>
[path\missingassembly.txt] is a input file you need to create based on the [MissingAssembly] errors that you get on the SharePoint Health Analyzer

The input.txt file should be formated like this (Assembly;ContentDatabase), example:
DocumentReviewEventHandler, Version=14.0.0.0, Culture=neutral, PublicKeyToken=71e9bce111e9429c;Sharepoint_80_Content_01
KnowledgeBaseEventHandler, Version=14.0.0.0, Culture=neutral, PublicKeyToken=71e9bce111e9429c;Sharepoint_80_Content_02
Nintex.Workflow.Features, Version=1.0.0.0, Culture=neutral, PublicKeyToken=913f6bae0ca5ae12;Sharepoint_80_Content_03

After execution, the script generates a CSV output file in the same folder with details about the Assembly (Url /  EventReceiverName / EventReceiverClass).

After investigation using the report generated, if you want you safely remove the EventReceiver References in the content database, you can uncomment the script line  #$er.Delete()
Basicaly remove the # before these lines and reload the script, this will clean all the entries automaticaly.
Note: This information is provided "as is", the author disclaim any loss or liability and no guarantee is given for the execution of this script.

For [MissingSetupFile] events, please check my previous article
For [MissingWebPart] events, please check my previous article
For [MissingFeature] events, feel free to use the great tool FeatureAdmin, it can scan the whole farm and remove the corrupted feature references.

Solving [MissingWebPart] errors from SharePoint Health Analyzer

ISSUE DESCRIPTION:
After a SharePoint 2007 Migration to SharePoint 2010, on the Central Administration, the SharePoint Health Analyzer is generating the critical event [Missing server side dependencies] :

And the event details is generating a lot of [MissingWebPart] errors, examples:
[MissingWebPart] WebPart class [36f2680f-4855-f100-da5b-5dd1d07ae62b] is referenced [1] times in the database [Sharepoint_80_Content_01], but is not installed on the current farm. Please install any feature/solution which contains this web part. One or more web parts are referenced in the database [Sharepoint_80_Content_01], but are not installed on the current farm. Please install any feature or solution which contains these web parts.
[MissingWebPart] WebPart class [d0d5a8f7-2e30-7cd0-276e-7cdc1aac9ab8] is referenced [21] times in the database [Sharepoint_80_Content_01], but is not installed on the current farm. Please install any feature/solution which contains this web part. One or more web parts are referenced in the database [Sharepoint_80_Content_01], but are not installed on the current farm. Please install any feature or solution which contains these web parts.
[MissingWebPart] WebPart class [7dac9698-9043-eb31-b853-be8c1705512b] is referenced [1] times in the database [Sharepoint_80_Content_01], but is not installed on the current farm. Please install any feature/solution which contains this web part. One or more web parts are referenced in the database [Sharepoint_80_Content_01], but are not installed on the current farm. Please install any feature or solution which contains these web parts.
[MissingWebPart] WebPart class [150b4681-ae25-d7be-bc71-1410062c7315] (class [Microsoft.AnalysisServices.SharePoint.Integration.DataRefreshHistoryWebPart] from assembly [Microsoft.AnalysisServices.SharePoint.Integration, Version=10.0.0.0, Culture=neutral, PublicKeyToken=89845dcd8080cc91]) is referenced [1] times in the database [Sharepoint_80_Content_02], but is not installed on the current farm. Please install any feature/solution which contains this web part. One or more web parts are referenced in the database [Sharepoint_80_Content_02], but are not installed on the current farm. Please install any feature or solution which contains these web parts.
[MissingWebPart] WebPart class [60085034-7d97-27c7-5823-2f941e6be1de] (class [Microsoft.AnalysisServices.SharePoint.Integration.WorkbookWebPart] from assembly [Microsoft.AnalysisServices.SharePoint.Integration, Version=10.0.0.0, Culture=neutral, PublicKeyToken=89845dcd8080cc91]) is referenced [1] times in the database [Sharepoint_80_Content_02], but is not installed on the current farm. Please install any feature/solution which contains this web part. One or more web parts are referenced in the database [Sharepoint_80_Content_02], but are not installed on the current farm. Please install any feature or solution which contains these web parts.
[MissingWebPart] WebPart class [7a95935f-d524-5992-f505-809725b6a9e2] (class [Microsoft.AnalysisServices.SharePoint.Integration.DashboardLinksWebPart] from assembly [Microsoft.AnalysisServices.SharePoint.Integration, Version=10.0.0.0, Culture=neutral, PublicKeyToken=89845dcd8080cc91]) is referenced [1] times in the database [Sharepoint_80_Content_03], but is not installed on the current farm. Please install any feature/solution which contains this web part. One or more web parts are referenced in the database [Sharepoint_80_Content_03], but are not installed on the current farm. Please install any feature or solution which contains these web parts.
[MissingWebPart] WebPart class [710dd8b1-df44-baec-3f85-84ef85658f95] (class [Microsoft.AnalysisServices.SharePoint.Integration.ReportsWebPart] from assembly [Microsoft.AnalysisServices.SharePoint.Integration, Version=10.0.0.0, Culture=neutral, PublicKeyToken=89845dcd8080cc91]) is referenced [1] times in the database [Sharepoint_80_Content_03], but is not installed on the current farm. Please install any feature/solution which contains this web part. One or more web parts are referenced in the database [Sharepoint_80_Content_03], but are not installed on the current farm. Please install any feature or solution which contains these web parts.

ROOT CAUSE:
These events are logged because the migrated SharePoint 2010 Sites contains some references to custom WebPart files and the linked feature/solution are not installed in the Farm.
In my case, I have tried to clean up the SharePoint 2007 Sites before the migration by disabling and uninstalling the unused features, but most of the times, the feature custom files are not getting removed correctly.

SOLUTION:
The easy solution is obviously to install the features/solution related to those webpart , but if you are in the same situation as me, you don't really need the webpart anymore and you just want the database to be clean and get rid of these events.

To safely remove the webparts and references, we need to be able to identify their specific location on the Farm, I have created an automated PowerShell script based on Phil's article (Thanks Phil), this script will get all the required information for you:

 param (
    [string]$DBserver = $(throw "Missing server name (please use -dbserver [dbserver])"),
    [string]$path = $(throw "Missing input file (please use -path [path\file.txt])")
)
 
#Set Variables
$input = @(Get-Content $path)
 
#Addin SharePoint2010 PowerShell Snapin
Add-PSSnapin -Name Microsoft.SharePoint.PowerShell
 
#Declare Log File
Function StartTracing
{
    $LogTime = Get-Date -Format yyyy-MM-dd_h-mm
    $script:LogFile = "MissingWebPartOutput-$LogTime.csv"
    Start-Transcript -Path $LogFile -Force
}
 
#Declare SQL Query function
function Run-SQLQuery ($SqlServer, $SqlDatabase, $SqlQuery)
{
$SqlConnection = New-Object System.Data.SqlClient.SqlConnection
$SqlConnection.ConnectionString = "Server =" + $SqlServer + "; Database =" + $SqlDatabase + "; Integrated Security = True"
$SqlCmd = New-Object System.Data.SqlClient.SqlCommand
$SqlCmd.CommandText = $SqlQuery
$SqlCmd.Connection = $SqlConnection
$SqlAdapter = New-Object System.Data.SqlClient.SqlDataAdapter
$SqlAdapter.SelectCommand = $SqlCmd
$DataSet = New-Object System.Data.DataSet
$SqlAdapter.Fill($DataSet)
$SqlConnection.Close()
$DataSet.Tables[0]
}
 
 
function GetWebPartDetails ($wpid, $DBname)
    {
    #Define SQL Query and set in Variable
    $Query =  "SELECT * from AllDocs inner join AllWebParts on AllDocs.Id = AllWebParts.tp_PageUrlID where AllWebParts.tp_WebPartTypeID = '"+$wpid+"'"
 
    #Runing SQL Query to get information about Assembly (looking in EventReceiver Table) and store it in a Table
    $QueryReturn = @(Run-SQLQuery -SqlServer $DBserver -SqlDatabase $DBname -SqlQuery $Query | select Id, SiteId, DirName, LeafName, WebId, ListId, tp_ZoneID, tp_DisplayName)
 
    #Actions for each element in the table returned
        foreach ($event in $QueryReturn)
        {
            if ($event.id -ne $null)
                {
                #Get Site URL
                $site = Get-SPSite -Limit all | where {$_.Id -eq $event.SiteId}
    
                #Log information to Host
                Write-Host $wpid -nonewline -foregroundcolor yellow
                write-host ";" -nonewline
                write-host $site.Url -nonewline -foregroundcolor green
                write-host "/" -nonewline -foregroundcolor green
                write-host $event.LeafName -foregroundcolor green -nonewline
                write-host ";" -nonewline
                write-host $site.Url -nonewline -foregroundcolor gray
                write-host "/" -nonewline -foregroundcolor gray
                write-host $event.DirName -foregroundcolor gray -nonewline
                write-host "/" -nonewline -foregroundcolor gray
                write-host $event.LeafName -foregroundcolor gray -nonewline
                write-host "?contents=1" -foregroundcolor gray -nonewline
                write-host ";" -nonewline
                write-host $event.tp_ZoneID -foregroundcolor cyan
                }
         }
    }
 
#Start Logging
StartTracing
 
#Log the CVS Column Title Line
write-host "WebPartID;PageUrl;MaintenanceUrl;WpZoneID" -foregroundcolor Red
 
foreach ($event in $input)
    {
    $wpid = $event.split(";")[0]
    $DBname = $event.split(";")[1]
    GetWebPartDetails $wpid $dbname
    }
    
#Stop Logging
Stop-Transcript

Paste the above code in a PowerShellScript file, like [MissingWebPartDetails.ps1] and copy it on your local SharePoint Server Drive.

Launch the script from a Windows Powershell Cmd Prompt using the following parametters:
.\MissingWebPartDetails.ps1 -DBserver <SQLServerName> -path <fullpath\missingwebparts.txt>
[path\missingwebparts.txt] is a input file you need to create based on the [MissingWebPart] errors that you get on the SharePoint Health Analyzer

The input.txt file should be formated like this (WebPartId;ContentDatabase), example:

36f2680f-4855-f100-da5b-5dd1d07ae62b;Sharepoint_80_Content_01
d0d5a8f7-2e30-7cd0-276e-7cdc1aac9ab8;Sharepoint_80_Content_01
7dac9698-9043-eb31-b853-be8c1705512b;Sharepoint_80_Content_01
150b4681-ae25-d7be-bc71-1410062c7315;Sharepoint_80_Content_02
60085034-7d97-27c7-5823-2f941e6be1de;Sharepoint_80_Content_02
7a95935f-d524-5992-f505-809725b6a9e2;Sharepoint_80_Content_03
710dd8b1-df44-baec-3f85-84ef85658f95;Sharepoint_80_Content_03

After execution, the script generates a CSV output file in the same folder with details about the webpart location (PageUrl / MaintenanceUrl / Webpart Zone ID).

For [MissingSetupFile] events, please check my previous article
For [MissingAssembly] events, please check my previous article
For [MissingFeature] events, feel free to use the great tool FeatureAdmin, it can scan the whole farm and remove the corrupted feature references.

18 October 2011

Solving [MissingSetupFile] errors from SharePoint Health Analyzer

ISSUE DESCRIPTION:
After a SharePoint 2007 Migration to SharePoint 2010, on the Central Administration, the SharePoint Health Analyzer is generating the critical event [Missing server side dependencies] :
 And the event details is generating a lot of [MissingSetupFile] errors, examples:
[MissingSetupFile] File [Features\DocumentReviewDocsForReviewList\docsforreview\repair.aspx] is referenced [5] times in the database [Sharepoint_80_Content_04], but is not installed on the current farm. Please install any feature/solution which contains this file. One or more setup files are referenced in the database [Sharepoint_80_Content_04], but are not installed on the current farm. Please install any feature or solution which contains these files.
[MissingSetupFile] File [Features\DocumentReviewDocsForReviewList\docsforreview\Upload.aspx] is referenced [5] times in the database [Sharepoint_80_Content_04], but is not installed on the current farm. Please install any feature/solution which contains this file. One or more setup files are referenced in the database [Sharepoint_80_Content_04], but are not installed on the current farm. Please install any feature or solution which contains these files.
[MissingSetupFile] File [Features\DocumentReviewModules\default.aspx] is referenced [5] times in the database [Sharepoint_80_Content_04], but is not installed on the current farm. Please install any feature/solution which contains this file. One or more setup files are referenced in the database [Sharepoint_80_Content_04], but are not installed on the current farm. Please install any feature or solution which contains these files
[MissingSetupFile] File [Features\InfoPathFormViewer_c9ca3d89-c724-4265-9246-70ef8bf8bdbf\InfoPathFormViewer.webpart] is referenced [2] times in the database [Sharepoint_80_Content_04], but is not installed on the current farm. Please install any feature/solution which contains this file. One or more setup files are referenced in the database [Sharepoint_80_Content_04], but are not installed on the current farm. Please install any feature or solution which contains these files.
[MissingSetupFile] File [Features\KnowledgeBaseKnowledgeBaseList\kbase\repair.aspx] is referenced [2] times in the database [Sharepoint_80_Content_04], but is not installed on the current farm. Please install any feature/solution which contains this file. One or more setup files are referenced in the database [Sharepoint_80_Content_04], but are not installed on the current farm. Please install any feature or solution which contains these files.
[MissingSetupFile] File [Features\KnowledgeBaseKnowledgeBaseList\kbase\Upload.aspx] is referenced [2] times in the database [Sharepoint_80_Content_04], but is not installed on the current farm. Please install any feature/solution which contains this file. One or more setup files are referenced in the database [Sharepoint_80_Content_04], but are not installed on the current farm. Please install any feature or solution which contains these files.

ROOT CAUSE:
These events are logged because the migrated SharePoint 2010 Sites contains some references to custom Feature files and the linked feature are not installed in the Farm.
In my case, I have tried to clean up the SharePoint 2007 Sites before the migration by disabling and uninstalling the unused features, but most of the times, the feature custom files are not getting removed correctly.

SOLUTION:
The easy solution is obviously to install the features related to those files, but if you are in the same situation as me, you don't really need the features anymore and you just want the database to be clean and get rid of these events.

To safely remove the files, we need to be able to identify their specific location on the Farm, I have created an automated PowerShell script based on Phil's article (Thanks Phil), this script will get all the required information for you:

 param (  
   [string]$DBserver = $(throw "Missing server name (please use -dbserver [dbserver])"),  
   [string]$path = $(throw "Missing input file (please use -path [path\file.txt])")  
 )  
 #Set Variables  
 $input = @(Get-Content $path)  
 #Addin SharePoint2010 PowerShell Snapin  
 Add-PSSnapin -Name Microsoft.SharePoint.PowerShell  
 #Declare Log File  
 Function StartTracing  
 {  
   $LogTime = Get-Date -Format yyyy-MM-dd_h-mm  
   $script:LogFile = "MissingSetupFileOutput-$LogTime.txt"  
   Start-Transcript -Path $LogFile -Force  
 }  
 #Declare SQL Query function  
 function Run-SQLQuery ($SqlServer, $SqlDatabase, $SqlQuery)  
 {  
   $SqlConnection = New-Object System.Data.SqlClient.SqlConnection  
   $SqlConnection.ConnectionString = "Server =" + $SqlServer + "; Database =" + $SqlDatabase + "; Integrated Security = True"  
   $SqlCmd = New-Object System.Data.SqlClient.SqlCommand  
   $SqlCmd.CommandText = $SqlQuery  
   $SqlCmd.Connection = $SqlConnection  
   $SqlAdapter = New-Object System.Data.SqlClient.SqlDataAdapter  
   $SqlAdapter.SelectCommand = $SqlCmd  
   $DataSet = New-Object System.Data.DataSet  
   $SqlAdapter.Fill($DataSet)  
   $SqlConnection.Close()  
   $DataSet.Tables[0]  
 }  
 #Declare the GetFileUrl function  
 function GetFileUrl ($filepath, $DBname)  
 {  
     #Define SQL Query and set in Variable  
     $Query = "SELECT * from AllDocs where SetupPath = '"+$filepath+"'"  
     #Runing SQL Query to get information about the MissingFiles and store it in a Table  
     $QueryReturn = @(Run-SQLQuery -SqlServer $DBserver -SqlDatabase $DBname -SqlQuery $Query | select Id, SiteId, DirName, LeafName, WebId, ListId)  
     foreach ($event in $QueryReturn)  
       {  
         if ($event.id -ne $null)  
         {  
         $site = Get-SPSite -Limit all | where { $_.Id -eq $event.SiteId }  
         #get the URL of the Web:  
         $web = $site | Get-SPWeb -Limit all | where { $_.Id -eq $event.WebId }  
         #Write the SPWeb URL to host  
         Write-Host $filepath -nonewline -foregroundcolor yellow  
         Write-Host ";" -nonewline  
         write-host $web.Url -NoNewline -foregroundcolor green  
         #get the URL of the actual file:  
         $file = $web.GetFile([Guid]$event.Id)  
         #Write the relative URL to host  
         write-host "/" -nonewline -foregroundcolor green  
         write-host $file.Url -foregroundcolor green  
         }  
       }  
 }  
 #Start Logging  
 StartTracing  
 #Log the CVS Column Title Line  
 write-host "MissingSetupFile;Url" -foregroundcolor Red  
 foreach ($event in $input)  
   {  
   $filepath = $event.split(";")[0]  
   $DBname = $event.split(";")[1]  
   #call Function  
   GetFileUrl $filepath $dbname  
   }  
 #Stop Logging  
 Stop-Transcript  

Paste the above code in a PowerShellScript file, like [MissingSetupFileDetails.ps1] and copy it on your local SharePoint Server Drive.

Launch the script from a Windows Powershell Cmd Prompt using the following parametters:
.\MissingSetupFileDetails.ps1 -DBserver <SQLServerName> -path <fullpath\missingfiles.txt>
[path\missingfiles.txt] is a input file you need to create based on the [MissingServerFile] errors that you get on the SharePoint Health Analyzer

The input.txt file should be formated like this (FeaturesFilePath;ContentDatabase), example:

Features\DocumentReviewDocsForReviewList\docsforreview\repair.aspx;Sharepoint_80_Content_01
Features\DocumentReviewDocsForReviewList\docsforreview\Upload.aspx;Sharepoint_80_Content_01
Features\DocumentReviewModules\default.aspx;Sharepoint_80_Content_01
Features\InfoPathFormViewer_c9ca3d89-c724-4265-9246-70ef8bf8bdbf\InfoPathFormViewer.webpart;Sharepoint_80_Content_02
Features\KnowledgeBaseKnowledgeBaseList\kbase\repair.aspx;Sharepoint_80_Content_03
Features\KnowledgeBaseKnowledgeBaseList\kbase\Upload.aspx;Sharepoint_80_Content_03

After execution, the script generates a CSV output file in the same folder with details about the file location.

Note: If you want to get rid of all files unused, update the above script by adding the line $file.delete() after the line write-host $file.Url -foregroundcolor green  in the IF statement 
(to be used at your own risk - I would strongly recommend to first analyse the CSV generated (excel) before updating the script with the delete option, to avoid removing important files.)

------------

For [MissingWebPart] events, please check my next article
For [MissingAssembly] events, please check next article
For [MissingFeature] events, feel free to use the great tool FeatureAdmin, it can scan the whole farm and remove the corrupted feature references.

22 March 2011

Welcome !

I will use this blog to share the current SharePoint Project / Issues / tests scenarios that I'm working on.