Use PowerShell to Check SharePoint Library Version History

Randomly ran across a library that a user had jacked versioning up to 99 versions.

So, how do you audit every list and library in a web app / site collection / site.

In the script below, I set the write-host to only be triggered if major or minor versions is greater than 20.

$siteURL = "http://webapp1.sharepointed.com/"
$rootSite = New-Object Microsoft.SharePoint.SPSite($siteUrl)
$spWebApp = $rootSite.WebApplication

foreach($site in $spWebApp.Sites)
{
	foreach($s in $site.AllWebs)
	{
		foreach($l in $s.lists)
		{
			$maj = $l.MajorVersionLimit
			$majMinor = $l.MajorWithMinorVersionsLimit
			
			if(($maj -gt 20) -or ($majMinor -gt 20))
			{
				Write-Host $s.Url
				Write-Host $l.Title
				Write-Host "Major: " $maj
				Write-Host "Minor: " $majMinor
			}
		}
	$s.Dispose()
	}
}

Use PowerShell to Compare Two SharePoint Lists

What if, for some random reason you wanted to compare two SharePoint Lists or Libraries and find the differences.

For the setup, I created two Lists (A and B).  I then added a few items to the Lists. Notice, that List A has two extra items, 6 and 7.

     

 


$mSite = Get-SPweb "http://sharepointed.com/site/taco"
$aList = $mSite.lists["A"]
$bList = $mSite.lists["B"]

$arrA = @()
$arrB = @()

foreach($iA in $aList.Items)
{
 $arrA += $iA["Title"]
}

foreach($iB in $bList.Items)
{
 $arrB += $iB["Title"]
}

$c = Compare-Object -ReferenceObject $arrA -DifferenceObject $arrB -PassThru
Write-Host $c

Output of the above script is: 6 7

More about the Compare-Object cmdlet:

http://technet.microsoft.com/en-us/library/ee156812.aspx

 

create a list of groups and the users in each group

As we all know, digging through SharePoint groups looking to see what users are in each, is no fun.

To solve this, I created a new List, and added the groups I wanted to monitor.  Then, added a little PowerShell to update a column in the List with the members in each group.

Setup:

Create a new List.

New Columns:

SPgroup 

Type: Person or Group

Allow selection of: People or Group

Users

Type: Multiple lines of text

Specify the type of text to allow: 

Update Column:

Title

Require that this column contains information: No

The PowerShell script below does the following:

Get the SharePoint Site and List.

Loop through each item in the List.

Retrieve the value of the SPgroup field.

Truncate the Users field.

Loop through the users in the SPgroup.

Update the Users field with the values from SPgroup.


if(-not(Get-PSSnapin | where { $_.Name -eq "Microsoft.SharePoint.PowerShell"}))
{
      Add-PSSnapin Microsoft.SharePoint.PowerShell;
}

$sSite = Get-SPweb "http://reinmoto.com/sites/taco"
$sList=$sSite.Lists["SP Group Permissions"]
$i = 0

foreach($item in $sList.Items)
{
	$sFieldValue = $item["SPgroup"].ToString().Split('#')[1]
	$sGroup = $sSite.SiteGroups[$sFieldValue]

	If($sGroup.Users.Count -ge 0)
	{
		$item["Users"] = ""
		$item.Update()

		foreach($usr in $sGroup.Users)
			{
				if($i -eq 0)
				{
					$item["Users"] = $usr.DisplayName
				}
				else
				{
					$item["Users"] = $item["Users"] + ", " + $usr.DisplayName
				}
				$item.Update()
				$i=$i+1
			}
		$i=0
	}
}
$sSite.dispose()

Save the PowerShell script to your server.

Create a scheduled task to run the script hourly, daily, weekly, monthly…

Export a list of all sites in a Site Collection

How do you use PowerShell to export a list of all the sites in a Site Collection?


if ((Get-PSSnapin "Microsoft.SharePoint.PowerShell" -ErrorAction SilentlyContinue) -eq $null) {
 Add-PSSnapin "Microsoft.SharePoint.PowerShell"
}

$site = Get-SPSite "http://reinmoto.com/customers"

foreach($s in $site.AllWebs)
 {
 Write-Host $s.title
 Add-Content -Path "C:\file.txt" -Value $s.URL
 $s.Dispose()
 }

The parent content type specified by content type identifier 0x01 does not exist

When trying to enable a feature on a site, I was greeted with this following error:

The parent content type specified by content type identifier 0x01 does not exist.

Long story short, the CTYPE feature was not enabled on the site.

Using PowerShell, I exported a list of all the available features in the site.


if(-not(Get-PSSnapin | where { $_.Name -eq "Microsoft.SharePoint.PowerShell"}))
{
 Add-PSSnapin Microsoft.SharePoint.PowerShell;
}

$web = Get-SPWeb http://yourSharePointsite/
Get-SPFeature | Sort -Property DisplayName > c:\Features.txt

Once exported,  I located the CTYPE feature, grabbed its GUID,  then enabled the feature.


$web = Get-SPWeb http://yourSharePointsite/
Enable-SPFeature "695b6570-a48b-4a8e-9ea5-26ea7fc1d163" -Url $web.Url

Now you should be able to enable the feature that started all this fun.

Why Wait for Document to be Unlocked by Document Editor Sucks

If you are using the SharePoint Designer action wait for document to be unlocked by document editor, you may be aware of it’s shortcomings.  Like what?  Well, if you submit a form or document to the library, this action fires.  If the form/document happens to be a little slow getting to the library, the workflow will pause for another 5 minutes!  ¡No bueno! 

What I did to get around this was, write a little inline PowerShell script as my first action in the Workflow.  The script loops every 5 seconds to see if the form/document has been unlocked.  5 seconds vs 5 minutes, there is a large difference.

How? If you don’t already have the iLove SharePoint Workflow Actions installed, do it! The actions can be downloaded here: http://ilovesharepoint.codeplex.com/ .  (read the documentation)

Once installed, modify your Worflow or create a new one. I replaced the lame Wait for Document to be Unlocked by Document Editor with a Execute PowerShell action. The PowerShell code is below.


if(-not(Get-PSSnapin | where { $_.Name -eq "Microsoft.SharePoint.PowerShell"}))
{
 Add-PSSnapin Microsoft.SharePoint.PowerShell;
}

$site = Get-SPweb "http://sharepointed.com/sites/taco/"
$mList=$site.Lists["The Library"]
$item = $mList.GetItemById([<strong><span style="text-decoration: underline;">%Current Item:ID%</span></strong>])

$Lock = $item.File.LockType

while($Lock -ne "None")
 {
 start-sleep -s 5
 $Lock = $item.File.LockType
 }

Please notice the line $item = $mList.GetItemById([%Current Item:ID%]), Current Item: ID is the ID of the item that started the workflow.  This is key to Workflow action working!

*update*

It’s also a good idea to modify your default view and other views to only show workflow items that have a completed status.  How?  Edit the view, scroll down to the Filter section, select the column that has your workflows name in it.  The next drop down should be ‘is equal to‘ and the text box, input the number five 5.  5 represents the Completed workflow status.

SharePoint Completed Workflow Status

SharePoint Completed Workflow Status

Monitor Content Database Size with Powershell

Simple question.
How large are the content databases?

To solve this, I created a list in Central Administration, and wrote a quick PowerShell script to update the list with my Content Database size(s).

List Name: Content db Size
List Columns: Title, db name, db size (Type = Single line of text)
View: Show Title, db name, db size

PowerShell Script:

if(-not(Get-PSSnapin | where { $_.Name -eq "Microsoft.SharePoint.PowerShell"}))
{
      Add-PSSnapin Microsoft.SharePoint.PowerShell;
}
$adminwebapp = Get-spwebapplication -includecentraladministration | where {$_.IsAdministrationWebApplication}

$siteUrl = $adminwebapp.Url
$listName = "Content db Size"
$spSite = new-object Microsoft.SharePoint.SPSite($siteurl)
$spWeb = $spSite.OpenWeb()
$spList = $spWeb.Lists[$listName]
foreach ($item in $spList.items) { $deaditem = $splist.GetItemById($item.ID); $deaditem.Delete(); }

$webapps = Get-SPWebApplication
foreach($webapp in $webapps)
{
    $ContentDatabases = $webapp.ContentDatabases
    foreach($ContentDatabase in $ContentDatabases)
    {
    $ContentDatabaseSize = [Math]::Round(($ContentDatabase.disksizerequired/1GB),2)
	$addItem = $spList.Items.Add()
	$addItem["Title"] = $webapp.DisplayName
	$addItem["db name"] = $contentdatabase.name
	$addItem["db size"] = $contentdatabasesize
	$addItem.Update()
    }
}

$spWeb.Dispose()

The script is doing the following actions:
Truncate the Content db Size list.
Looping through all the Web Apps.
Updating the Content db Size list with the Web App name, db name, and size.

Taking this to the next level:
Update the existing list item, opposed to truncating the entire list.
Setup a workflow to email you if a db size is greater than X.

*update*

What if you want to get all the Site Collections in a Content Database?
 

$sites = Get-SPSite -Limit All  -ContentDatabase YourContentDatabaseName

foreach($site in $sites)
{
	$siteSize = [math]::round(($site.usage.storage/1GB),2)
	Write-Host $site.url "--" $siteSize
}

is locked for

Two different errors, same fix!

File is locked for editing by domain\username
is locked for shared use by domain\username

Before you do this, make sure you have a copy of the file that is locked. Open the Library in Explorer View and create a copy of the file, or save a copy to your desktop.

Bottom line, you will need to either write a PowerShell script to update the CheckoutExpires field in the Content Database or go directly into the Content Database and update the record. I know, you should never play with the SharePoint databases, great!

First we will need to get the correct Content Database and ID for the locked file.

if(-not(Get-PSSnapin | where { $_.Name -eq "Microsoft.SharePoint.PowerShell"}))
{
      Add-PSSnapin Microsoft.SharePoint.PowerShell;
}
 
$site = Get-SPweb "http://sharepointed.com/taco"

$spList = $site.lists["Soft Taco"]
$i = $spList.GetItemById("14")

$SQLconn = $i.Web.Site.ContentDatabase.DatabaseConnectionString

$id = $i.UniqueId

What I’m doing is getting the database connection string and the ID of the file.

Now, crack open SQL Server Management Studio. Navigate to the Content Database that we identified in the $SQLconn string above. Create a new Stored Procedure and paste in the following script.

Now you will need the value from $id.

Select * from dbo.AllDocs 
WHERE Id = '89c87af8-758b-4186-ffaa-c78a13695aec'

Once you run the above query, you should see the record that is locked. Make note of the CheckoutExpires field, the is what we are going to update.

Next you will want to run the update script that will unlock the file.

UPDATE dbo.AllDocs SET CheckoutExpires = '2000-01-22 13:24:47.000' 
WHERE Id = '89c87af8-758b-4186-ffaa-c78a13695aec'

Refresh your browser, and the file should now be unlocked.