Second Life of a Hungarian SharePoint Geek

April 3, 2014

How to Use PowerShell to Delete Short-Term Locks from Documents Opened from SharePoint?

Filed under: PowerShell, SP 2010 — Tags: , — Peter Holpar @ 21:58

Recently one of our users complained, that he can not open an Excel document for editing from a SharePoint document library, as he gets a warning that the file is locked by himself. It should be a known issue, as documented here,  however in our case it did not help waiting 10 minutes, or even hours. I check the AllDocs table in the content database, and found very strange values in the record corresponding to the locked document (filtered for DirName and LeafName fields). The document was really locked by the complaining user (CheckoutUserId field), the CheckoutDate field contained the current time, and (that was the surprise) the CheckoutExpires field contained a date value that was about 2-3 month before the current date.

Although one could delete the dirty data from the database, as shown for example in this article or in this post, that would be an unsupported solution, so I wanted to find an officially acceptable way. The information I found in this post was closer to my expectations.

As I checked the status of the file from PowerShell I get an output similar to the screenshot below, that suggests that the file is not locked:

$web = Get-SPWeb
$list = $web.Lists["DocLib"]
$item = $list.GetItemById(2)
$file = $item.File


It could be a reason, why SharePoint does not try to delete the lock, although I found it still rather odd.

As I tried to call the ReleaseLock method on the $file I got an error stating that the file is not locked. What helped in this case was not less surprising as the former situation. I checked out the file and then made an undo for the checkout:

$ft = New-Object System.TimeSpan(10000)
$file.Lock([Microsoft.SharePoint.SPFile+SPLockType]::Exclusive, "test lock", $ft)

After this the lock status of the file was cleared in the database as well.

If the file happened to be locked by a user other than the current user, and we would like to release the lock, we get an error message stating that the file is locked by an other user. However, there is a way to release the lock even in this case using PowerShell and that is possible via impersonating the user that holds the lock:

$web = Get-SPWeb
$list = $web.Lists["DocLib"]
$item = $list.GetItemById(2)
$file = $item.File
$userId = $file.LockedByUser.ID
$user = $web.AllUsers.GetByID($userId)
$impSite= New-Object Microsoft.SharePoint.SPSite($web.Url, $user.UserToken);
$impWeb = $impSite.OpenWeb();
$impList = $impWeb.Lists[$list.Title]
$impItem = $impList.GetItemById($item.ID)
$impFile = $impItem.File

March 23, 2014

HTTP Error: ‘401–Unauthorized’ When Accessing Exchange Web Services via PowerShell 2.0

Filed under: Exchange, PowerShell — Tags: , — Peter Holpar @ 20:25

Last week I had to create a tool to automate the synchronization of an Exchange 2010 folder with a SharePoint 2010 list. Formerly I had some experience with Exchange Web Services and its Managed API, and downloaded the code samples for Exchange 2013 to re-use a few classes of the examples. As my developer account had no access to Exchange, I used explicit credentials of my standard account via the NetworkCredential class in the test (user name hardcoded, password read from the command line). The C# code in Visual Studio 2012 of the proof-of-concept application was running without error, but I thought it’s a good idea to migrate the code to PowerShell, as if the requirements happened to change (that is rather likely in this case), it would be easier to modify the functionality without a recompilation. This idea cost me a few hours debugging later.

I found a few samples on the web, like this one, that illustrate the basic steps in PowerShell. I used the EWS Managed API version 2.1, and PowerShell 2.0, that support .NET CLR version 2 (handy when we want to use the SharePoint 2010 server side object library in the same process). Rewriting the code in PowerShell seemed a trivial task, however when I started the tool I got a 401 Unauthorized error on the first command that tried to access an Exchange resource. I double checked the user name in the code, but it was OK. I found no significant difference when comparing the network traffic generated by the .NET version vs. the PowerShell version up to the point of the error message. I altered the code to use the three-string-parameter constructor of the NetworkCredential class (user name, password, domain name) instead of the two-string-parameter constructor version (domainname\username, password), as I had previously issues from using the two-string-parameter version. But the error remained, so I altered the code back. When I logged in with my other user, that has access to Exchange, and used the default credentials (service.UseDefaultCredentials = $true) the PowerShell code gave no error more.

I read the password from the console using the Read-Host Cmdlet as described here (to tell the truth I simply copied the code and did not check how it works and what that might cause):

$password = Read-Host -assecurestring "Please enter your password"

I assumed that there might be a problem with the password I typed in, so decided to echo back it via Write-Host $password. To my great surprise instead of my password (or a mistyped version of that) the result was: “System.Net.NetworkCredential”. Then I realized the switch assecurestring used with Read-Host, that means not only that the text typed in will be replaced with asterisks, but also that it will be represented internally as SecureString, and not as String. After reading the password without assecurestring, the password was stored as string, but the error remained until I changed the NetworkCredential constructor to the three-string-parameter version again. So as usually, the mystic error was a result of a dual mistake, using SecureString instead of String, and using the wrong version of the NetworkCredential constructor.

The sample application from Microsoft (mentioned above) are working flawlessly with a NetworkCredential constructor that accepts a String for domainname\username, and a SecureString parameter for password, but these samples using the .NET version 4.0. After changing the .NET version of the project to 3.5 in Visual Studio, I had the same issues, but at least the IDE gave me a compilation error because of using a wrong constructor parameter. “Thanks” to the flexibility of PowerShell, it did not warned me because of the SecureString type (I assume it used the “System.Net.NetworkCredential” string as password) and did its best to perform the authentication and failed first there.

November 6, 2013

How to change the structure of sharePoint site in a site collection

Filed under: PowerShell, SP 2010, Tips & Tricks — Tags: , , — Peter Holpar @ 23:08

SharePoint sites can be created using a wrong URL name, or in an even worse case, under a wrong parent site. In both cases the site is located at a URL that is somehow not ideal for the users. If there is no content in the new site yet, it is probably the easiest to delete and re-create the site using the right name / at the right place. However, if the users created a lot of content in the site, we should find another solutions to handle the mistake. It is good to know that there are alternatives for such issues.

Solution 1: If the site is created under the correct parent site but with a wrong name, it is the easiest to fix the name via the SharePoint UI. As described in this post, you can change the name under Site Actions / Site Settings / Title, description, and icon. This method does not help to move sites between parent sites.


Solution 2: One of my colleagues, Verena has found another solution via PowerShell:

Get-SPWeb http://yoursite/siteX | Set-SPWeb -RelativeUrl subsite1/siteY

Using this method, we can not only rename the site, but we can move it under an existing subsite as well. It is important to point out, that based on our experience, this method does not work in the other direction, that means, we cannot move a site up in the hierarchy, like from under a subsite to the root site. Thanks Verena for this solution, and happy birthday! Birthday cake

Solution 3: Using the ServerRelativeUrl property of the SPWeb object we can alter the structure of the sites as well. This method seems to be pretty flexible, makes it possible to change the site relationships in both directions.

$web = Get-SPWeb http://yoursite/subsite1/siteX
$web.ServerRelativeUrl = “/yoursite/siteY”

It is an interesting question, how the methods 2 and 3 above handle the case, when the site permissions are configured to be inherited from the parent site. Do they keep the permissions inherited from the original parent site? Or the permissions of the new parent site will be inherited? Recently we had to move sites using these methods, but in our case the sites had always their own permission sets, so I cannot answer this question right now.

September 10, 2013

SharePoint reporting using a scheduled PowerShell script

Filed under: PowerShell, SP 2010 — Tags: , — Peter Holpar @ 20:49

We have same custom SharePoint application pages that enable custom reporting for our support group. Reports are created in plain text (comma separated values / CSV) format and can be opened from the UI via SharePoint application pages. Since some of the reports contain rather complex SharePoint queries, opening them “on-demand” takes some time and system resources as well. While the data in the reports isn’t very volatile, our goal was to automate the report creation, for example, scheduling the task for the midnight hours. We wanted the outcome of the reports to be stored in a SharePoint document library with folder names corresponding to the current date (+ postfix _1, _2, etc. if the folder already exists), the latest version of the reports stored in a dedicated folder called latest.

The “business logic” for the reports was already encapsulated in a custom .NET assembly as static methods with some parameters, we planned the automation however using PowerShell.

I share the results here, as it has some interesting points that one might find useful, like:

- Creating / extending the folder structure in the SharePoint document library using the current date pattern.

- Loading the custom assembly into the context of the PowerShell script.

- Calling static methods of .NET assemblies with parameters from PowerShell.

- Converting the text (String) return value of the methods to byte array to enable uploading the content as a new file into the SharePoint library.

So here is the script with some values that should be updated to match the parameters of your system:

# URL of the SharePoint site
$sitePath = "http://yoursite"
# web of the Reports doc. lib
$webPath = "/ReportsWeb"
# name of the doc. lib.
$docLibName = "Reports"
# name of the ‘latest’ folder
$latestFolderName = "latest"

$site = Get-SPSite($sitePath)
$web = $site.OpenWeb($webPath)

$date = Get-Date -Format "yyyMMdd"
$folderName = $date

$docLib = $web.Lists[$docLibName]

$tryCount = 0

# get the first available folder name corresponding to our date pattern
  $newFolderName = $folderName
  $folder = $web.GetFolder( ($web.Url, $docLib.RootFolder.Url, $folderName -join "/") )
  $folderName = $date + "_" + $tryCount 
While ($folder.Exists)

Write-Host $newFolderName

#creating the new folder
$newFolder = $docLib.Folders.Add("",[Microsoft.SharePoint.SPFileSystemObjectType]::Folder, $newFolderName);

$enc = [system.Text.Encoding]::UTF8

# load the custom assembly (GACed!) into the PowerShell context

Write-Host "Generating report 1"
$rep1 = $enc.GetBytes([MyCompany.ReportHelper.ClassName]::GetReport1($site.ID, "report param 1"))
$newFile = $newFolder.Files.Add($newFolder.Url + "/" + "Report1.csv", $rep1)

Write-Host "Generating report 2"
$rep2 = $enc.GetBytes([MyCompany.ReportHelper.ClassName]::GetReport2($site.ID, "report param 2"))
$newFile = $newFolder.Files.Add($newFolder.Url + "/" + "Report2.csv", $rep2)

# delete the ‘latest’ folder if already exists
Write-Host "Updating latest folder"
$latestFolderUrl = $web.Url, $docLib.RootFolder.Url, $latestFolderName -join "/"
$latestFolder = $web.GetFolder($latestFolderUrl)
if ($latestFolder.Exists)

# re-create the ‘latest’ folder, copying the actual version


Write-Host Finished.

We scheduled the PowerShell script using the Task Scheduler component of Windows. A good introduction into scheduled PowerShell tasks can be found on Dmitry’s PowerBlog.

Restarting SharePoint Timer Service on remote servers using PowerShell

Filed under: PowerShell, SP 2010, Tips & Tricks — Tags: , , — Peter Holpar @ 20:45

Recently we installed a new version of a custom SharePoint timer job together with a SharePoint administrator of our company. After installation, the Timer Service must have been restarted, to enforce reloading of the new version of the assemblies. My colleague used a remote desktop connection tool to log in on each of our front end / application servers (we have a few ones) and restarted the service using Service Manager. I asked him (and myself): Wouldn’t it be much more easier to restart the services from a single console, for example, using PowerShell?

After a quick research I came up with the solution (you should update the server names in the script, of course):

$computers = ("SPFrontEnd1", "SPFrontEnd2", "SPApp1", "SPApp2")
$computers | % { Restart-Service -InputObject $(Get-Service -Computer $_ -Name SPTimerV4) }

Pretty simple, isn’t it?

July 16, 2013

Accessing group permissions from PowerShell using Reflection

Filed under: PowerShell, Reflection, Security, SP 2010 — Tags: , , , — Peter Holpar @ 22:31

This time I try to give an example how to call internal and private methods of the SharePoint object model from PowerShell. Reflection is not very well documented in the context of PowerShell, however just because something is not documented does not mean that it is not possible.

Yesterday I described how to inject an SPContext into your PowerShell scripts, and recently posted a sample C# code that enables accessing group permissions from C# through Reflection. The current post is a result of combining those codes + a few tricks around PowerShell and Reflection.

WARNING: The method below is not a supported solution, it serves only learning purposes. You should not use it in a production system, in other environments use it at your own risk.

# inject fake context
$site = Get-SPSite("")
$web = $site.OpenWeb()
$request = New-Object System.Web.HttpRequest("", $web.Url, "")
$response = New-Object System.Web.HttpResponse(New-Object System.IO.StreamWriter(New-Object System.IO.MemoryStream))
$dummyContext = New-Object System.Web.HttpContext($request, $response)
$dummyContext.Items["HttpHandlerSPWeb"] = [Microsoft.SharePoint.SPWeb]$web
[System.Web.HttpContext]::Current = $dummyContext
$groupPermissions = New-Object Microsoft.SharePoint.WebControls.GroupPermissions
# set your group here
$groupId = $web.SiteGroups["Team Site Owners"].ID
$groupPermissions.GroupId = $groupId
# set dummy Page
$groupPermissions.Page = New-Object System.Web.UI.Page
# invoke private CreateDataTable method
$groupPermissionsType = $groupPermissions.GetType()
$bindingFlags = [System.Reflection.BindingFlags]::NonPublic -bor [System.Reflection.BindingFlags]::Instance
$mi_CreateDataTable = $groupPermissionsType.GetMethod("CreateDataTable", [System.Reflection.BindingFlags]($bindingFlags))
$dataTable = $mi_CreateDataTable.Invoke($groupPermissions, $null)
#process results
$regExpPattern = [regex]‘<span dir="ltr">(?<scopeUrl>.*?)</span>’
$dataTable | % {
  $scopeUrl = $regExpPattern.match($_.ScopeUrl).groups[1].value
  Write-Host [$scopeUrl] [$_.Role]

That’s it. And the output of the run:


July 15, 2013

Injecting HttpContext and SPContext into your PowerShell scripts

Filed under: PowerShell, SP 2010 — Tags: , — Peter Holpar @ 22:10

Two years ago I already write a similar post that shows how to inject HttpContext and SPContext into event receivers or into other type of code running without such context by default.

This time I had to achieve the same from PowerShell to be able to call built-in SharePoint object model methods that were designed to run within an SPContext.

WARNING: The method below is probably not a supported solution. It is only presented here as a technological curiosity, use it at your own risk.

Having the original code it was pretty straightforward to translate the C# version to PowerShell. Here is the result:

# getting site and web references
$site = Get-SPSite("")
$web = $site.OpenWeb("/yourweb")
# injecting contexts
$request = New-Object System.Web.HttpRequest("", $web.Url, "")
$response = New-Object System.Web.HttpResponse(New-Object System.IO.StreamWriter(New-Object System.IO.MemoryStream))
$dummyContext = New-Object System.Web.HttpContext($request, $response)
$dummyContext.Items["HttpHandlerSPWeb"] = [Microsoft.SharePoint.SPWeb]$web
[System.Web.HttpContext]::Current = $dummyContext
# testing result

In a forthcoming post I plan to illustrate how to utilize a SharePoint OM using the freshly injected context information.

WARNING: Do not mix the code above with “standard” PowerShell – SharePoint code. I found that it can interfere with the standard functionality. The method above is intended only to inject context before calling SharePoint OM methods.

Approving all pending documents (and folders) of a specified library using PowerShell

Filed under: PowerShell, SP 2010 — Tags: , — Peter Holpar @ 21:56

Last week I wrote already about querying documents based on their moderation status. This time my goal was to approve all pending documents in a specified document library. I found a similar PowerShell script, but it does not handle approval of folders, so I created my own version displayed below:

$site = Get-SPSite("")
$web = $site.OpenWeb("/yourweb")

function approveItems($list) 
  Write-Host Processing $list
  $query = New-Object Microsoft.SharePoint.SPQuery
  $query.Query = "<Where><Eq><FieldRef Name=’_ModerationStatus’ /><Value Type=’ModStat’>2</Value></Eq></Where>"
  $query.ViewAttributes = "Scope = ‘RecursiveAll’"
  $items = $list.GetItems($query)

  $items | % {
    Write-Host Approving:  $_["Name"]
    $_["_ModerationStatus"] = 0

  Write-Host —————————

approveItems $web.Lists["YourList"]

As you can see, the key is the usage of RecursiveAll scope in ViewAttributes that include recursively both folders and documents. If you would like to approve all documents on your site, you need something like this:

$web.Lists | % { approveItems $_ }

How to access creation information of specific SharePoint Group or User

Filed under: PowerShell, SP 2010 — Tags: , — Peter Holpar @ 21:43

Sometimes it is useful to know when a specific SharePoint group or user was created, and who has created it. Although the standard SharePoint objects (SPGroup / SPUser) does not contain this type of information, we can access it through the hidden user information list, where each of these objects are represented as simple list items.

The PowerShell scripts below provide examples how to access the information for a group:

$usersList = $site.RootWeb.SiteUserInfoList
$groupName = "MyGroup"
$groupId = $site.RootWeb.SiteGroups[$groupName].ID
$group = $usersList.GetItemById($groupId)
Write-Host Created by: $group["Author"]
Write-Host Created on: $group["Created"]
Write-Host Last modified by: $group["Editor"]
Write-Host Last modified on: $group["Modified"]

And for a specific user:

$usersList = $site.RootWeb.SiteUserInfoList
$loginName = "domain\user"
$userId = $site.RootWeb.AllUsers | ? { $_.UserLogin -eq $loginName } | % { $_.ID }
$user = $usersList.GetItemById($userId)
Write-Host Created by: $user["Author"]
Write-Host Created on: $user["Created"]
Write-Host Last modified by: $user["Editor"]
Write-Host Last modified on: $user["Modified"]

As you can see, I write out the editing information as well, but it is important to point out, that this data does not reflect modifications for example in group membership, only changed information in the list item, for example, renaming of a group or change e-mail address for a user.

Querying the SharePoint audit log for membership changes of a specific Group

Filed under: Audit log, PowerShell, SP 2010 — Tags: , , — Peter Holpar @ 21:33

Recently I posted a sample PowerShell script that illustrates how to query the SharePoint audit log for membership changes of a specific user. This time I show the same thing from another perspective. Using the script below you can follow the membership changes of a specific group: track users added to / removed from the group.

$site = Get-SPSite("")

$startDate = Get-Date "1/1/2013 7:00 AM"
$groupName = "YourGroupName"
$groupId = $site.RootWeb.SiteGroups[$groupName].ID
$searchPattern = "*<groupid>$groupId</groupid>*"

function DumpEvents($site, $searchPattern, $startDate, $eventType, $eventName) { 
  $usersList = $site.RootWeb.SiteUserInfoList 

  $query = New-Object Microsoft.SharePoint.SPAuditQuery($site)
  $site.Audit.GetEntries($query) | ? { $_.EventData -like $searchPattern }| % {
   [xml]$eventData = "<eventData>" + $_.EventData + "</eventData>"
   $filter = if ($_.EventType -eq [Microsoft.SharePoint.SPAuditEventType]::SecGroupMemberAdd) { "//userid" } else { "//user" }
   $targetUserId = $eventData.SelectSingleNode($filter).InnerText 
   $targetUserName = $targetUserId 
   try { $targetUserName = $usersList.GetItemById($targetUserId).Name } catch { }
   $userName = $_.UserId
   try { $userName  = $usersList.GetItemById($_.UserId)["Name"] } catch { }
   Write-Host "User"  $targetUserName $eventName "on" $_.Occurred "by" $userName

Write-Host Changes in group membership of $groupName since $startDate
Write-Host ————————————————–

$eventType = [Microsoft.SharePoint.SPAuditEventType]::SecGroupMemberDel
DumpEvents $site $searchPattern $startDate $eventType "deleted"

Write-Host ————————————————–

$eventType = [Microsoft.SharePoint.SPAuditEventType]::SecGroupMemberAdd
DumpEvents $site $searchPattern $startDate $eventType "added"

Older Posts »

The Shocking Blue Green Theme Blog at


Get every new post delivered to your Inbox.

Join 50 other followers