Second Life of a Hungarian SharePoint Geek

October 16, 2017

How to query your working hours from Windows Event Log via PowerShell

Filed under: Tips & Tricks, PowerShell — Tags: , — Peter Holpar @ 19:49

At my company we have a kind of time reporting application. If I book the activities the same day, there is no problem. But after a week, it is not always straightforward to remember what I exactly did on a given day. To have a rough estimate, how many hours I overall and separated for projects worked, I usually make use of data sources like Event Viewer (first and last entries daily in the Windows Logs / System), Exchange (mails sent and receive), Internet Explore (sites visited in Browser History) and TFS (check-ins and task history).

To be able to query the Event Viewer Logs without starting the application and browsing through the entries, I wrote a PowerShell script that perform these tasks automatically for me. It’s nothing extra, but I thought it might be useful for others as well:

$startDay = Get-Date -Date ‘2017/09/01’
$endDay = Get-Date -Date ‘2017/09/11’

$days = New-Object System.Collections.Generic.List“1[System.DateTime]
For ($today = $startDay; $today -le $endDay; $today = $today.AddDays(1)) {

$days | % {
  $day = $_
  $events = Get-EventLog -Log System | ? { $_.TimeGenerated.Date -eq $day.Date }
  $maxDate = ($events | Measure-Object -Property TimeGenerated -Maximum).Maximum
  $minDate = ($events | Measure-Object -Property TimeGenerated -Minimum).Minimum
  select -Input $_ -Prop `
    @{ Name=’Day’; Expression={$day.ToShortDateString()} },
    @{ Name=’From’; Expression={ $minDate.ToLongTimeString() } },
    @{ Name=’To’; Expression={ $maxDate.ToLongTimeString() } },
    @{ Name=’Working Hours’; Expression={ $maxDate – $minDate } }
} | Export-Csv -Path C:\Temp\TimeReport.csv -Delimiter ";" -Encoding UTF8 -NoTypeInformation

The script writes the results in a .csv file, but without the last part (Export-Csv) you can direct the output to the screen as well.


July 20, 2017

How to Export a SharePoint List View to Excel Automatically Using PowerShell

Filed under: PowerShell, SP 2013, Tips & Tricks — Tags: , , — Peter Holpar @ 21:17

Note: This post is actually only a minor modification of the post I wrote recently about the URL of the Edit View page.

We can easily export the content of a SharePoint List View via the UI, simply by clicking the Export to Excel button on the ribbon:


You can achieve that automatically as well, for example from PowerShell.

Note: In the text below I describe the solution for a situation if you work locally on the server, but it is possible to apply the same technique to a remote solution as well, one should only transfer the code to the Managed Client Object Model.

Assume you have a list called YourList in a SharePoint site with URL http://YourSharePoint/Web/SubWeb.

It is easy to find out (for example, by monitoring the network traffic by Fiddler) that the URL generated when you click the Export to Excel button is like this:


The values of the List and View query string parameters are the encoded IDs of your list and list view respectively.

The following code generates the same URL from PowerShell:

$web = Get-SPWeb http://YourSharePoint/Web/SubWeb
$list = $web.Lists[‘YourList’]

# get the default view of the list
$view = $list.DefaultView
# or get an arbitrary view by its name
# $view = $list.Views[‘All Items’]
$viewId = $view.ID

function EscapeGuid($guid)
  return "{$guid}".ToUpper().Replace(‘-‘, ‘%2D’).Replace(‘{‘, ‘%7B’).Replace(‘}’, ‘%7D’)

$escapedListId = EscapeGuid $list.ID
$escapedViewId = EscapeGuid $view.ID
$escapedRootFolder = $list.RootFolder.ServerRelativeUrl.Replace(‘/’, ‘%2F’)

$url = $web.Url + "/_vti_bin/owssvr.dll?CS=65001&Using=_layouts/15/query.iqy&List=$escapedListId&View=$escapedViewId&RootFolder=$escapedRootFolder&CacheControl=1"

The URL above is actually no URL for the data or its schema, it’s a URL for a descriptor file (with the extension .iqy, see more about that here), that contains the URL for that list data and its schema.

The content of an .iqy file looks like this (you can capture it by Fiddler as well, or have a look at the content of the file we saved in our script further below) :



The URL we have in line 3 refers to the endpoint that returns the data schema and the data itself.. Based on this information, Excel can import and display the data of the list view.

Let’s save the file from the URL of the .icq file we have already from the first script, and start Excel to open the list view data. The script below assumes the extension .iqy is associated with Excel in your system:

$path = "C:\temp\owssvr.iqy"

$request = [System.Net.WebRequest]::Create($url)
$request.UseDefaultCredentials = $true
$request.Accept = "text/html, application/xhtml+xml, */*"

$response = $request.GetResponse()
$reader = New-Object System.IO.StreamReader $response.GetResponseStream()
$data = $reader.ReadToEnd()

$writer = [System.IO.StreamWriter] $path

# the .iqy file will be opened by Excel
Invoke-Expression $path
# optionally delete the file
# Remove-Item $path

July 16, 2017

Find Your Scripts in SharePoint within Seconds – the Effective, but Unsupported Way

Filed under: JavaScript, SP 2013, SQL, Tips & Tricks — Tags: , , , — Peter Holpar @ 18:41

The SharePoint environment I’m working on contains hundreds of webs. I create test sites for various tasks (like prototyping JSLink-based solutions) including the necessary lists, and store the .js and .css files typically in the Site Assets library of that web site to keep the solution artifacts (lists / files) together. It is rather common, that after I’ve finished the proof of concept, I don’t need it for months, then suddenly I should return to it, but I don’t find it anymore, as I don’t remember, which site I used for that solution.

For that kind of search I’ve created a simply SQL query to find the script directly in the content database of the site collection. Yes, I know it is officially unsupported to access the SharePoint databases directly, but I’m OK with that in my test system. Use it on your own risk.

  FROM [dbo].[AllDocs]
  WHERE LeafName LIKE ‘%.js’
  AND DirName LIKE ‘%SiteAssets%’
  ORDER BY TimeLastWritten DESC

This script lists the file name (LeadName) and path (DirName) of the scripts stored in various sites in their Site Assets library. The name of script and the date of last modification (TimeLastModified) is usually enough to identify the script I need. Note, that the records, that have a value other that 0x in the DeleteTransactionId column are recycled and located in the Recycle Bin. Of course, this method works only in the case of on-premise installations, and only as long as you have access to the SharePoint databases.

June 25, 2017

How to get the Url of the “Edit View” Page of a Specific SharePoint List View from PowerShell

Filed under: PowerShell, SP 2013, Tips & Tricks — Tags: , , — Peter Holpar @ 07:33

There might be cases when you can’t access the Edit View page of a specific list view from the SharePoint UI. For example, there is no such direct link in the case of Survey lists. There is no ribbon including the Manage Views group, and the Views area is missing from the List settings page as well.

You can, however access the Edit View page from your browser if you know its URL. The standard URL of this page has this pattern:


As you see, the ViewEdit.aspx page is responsible for this functionality. The encoded Ids (Guid) of the List and the View are passed as query string parameters (List and View respectively).

You can get the URL of the page using this PowerShell script easily:

$web = Get-SPWeb ‘http://YourSharePoint/Web/SubWeb’
$list = $web.Lists[‘YourList’]
# get the default view of the list
$view = $list.DefaultView
# or get an arbitrary view by its name
# $view = $list.Views[‘All Items’]
$viewId = $view.ID

function EscapeGuid($guid)
  return "{$guid}".ToUpper().Replace(‘-‘, ‘%2D’).Replace(‘{‘, ‘%7B’).Replace(‘}’, ‘%7D’)

$url = $web.Url + ‘/_layouts/15/ViewEdit.aspx?List=’ + (EscapeGuid $list.ID) + ‘&View=’ + (EscapeGuid $view.ID)

You can even start the page in Internet Explorer from PowerShell if you wish:

$ie = New-Object -ComObject InternetExplorer.Application
$ie.Visible = $true

June 13, 2017

A Quick and Dirty Solution to Create a Blank Site in SharePoint 2013

Filed under: Administration, SP 2013, Tips & Tricks — Tags: , , — Peter Holpar @ 18:36

Recently one of our clients requested a change in a custom-built SharePoint application. The original version of the application was built for SharePoint (MOSS) 2007 using Visual Studio 2008, then upgraded to SharePoint 2010 using Visual Studio 2010. Later the site was upgraded to SharePoint 2013, without any change in the code of the solution.

Now we had to create a replica of the site in our developer environment including the list data. We pulled a backup of the site using the Export-SPWeb cmdlet successfully in the productive system, and created a new team site in the development system as a target of the Import-SPWeb cmdlet. When executing the restore operation we’ve got this exception:

Import-SPWeb : Cannot import site. The exported site is based on the template STS#0 but the destination site is based on the template STS#1. You can import sites only into sites that are based on same template as the exported site.


In the error message STS#0 means the Team Site template, and STS#1 stands for the Blank Site template (see SharePoint site template IDs and their description here). Jason Warren suggests in this thread to use the -Force switch of the Import-SPWeb cmdlet to force the overwrite of the existing site, but we had the same issue even using this switch. How could we create a new web site using the Blank Site template? Solutions available using the server side, like using PowerShell or unhiding the Blank Site template are discussed in this thread. But what could we do, if we had no access to the server side, as this site template is not available on the web UI anymore?

We found a simply solution using only a single browser (Internet Explorer in our case) and the F12 Developer Tools.

Load the site creation page in the browser, then start the Developer Tools, and select the list of templates using the DOM Explorer.



Select an options in the select element, like the Team Site


… change its value attribute to STS#1


… and finally click the Create button on the web page to create the new blank site.

This solution is quick, but I consider it to be dirty, as users should perform it themselves and each time they need a blank site, so definitely not a user friendly option. But it might be handy if you need a simple way without access to the server side.

March 26, 2017

Generating Pseudo GUIDs for Your Project Server Entities

Filed under: PowerShell, Project Server, Tips & Tricks — Tags: , , — Peter Holpar @ 06:24

As you might have known, since the version 2013, Project Server utilizes pseudo-GUIDs to improve Project Server performance. These ones has the format of a “classical” GUID, but actually generated sequentially. As Microsoft states in this TechNet article:

"We handle GUIDs a little better in Project Server 2013 – and in many places they are sequential GUIDs which cause less index fragmentation"

This topic is quite good described in the Project Conference 2014 presentation Project Worst Practice – Learning from other peoples mistakes by Brian Smith. See the video recording between 6:08-13:54, or the slides 10-14.

One of the main components of the pseudo-GUID generation is the NewSequentialUid method of the Microsoft.Office.Project.Server.Library.PSUtility class:

public static Guid NewSequentialUid() 

  Guid guid; 
  if (NativeMethods.UuidCreateSequential(out guid) != 0) 
    return Guid.NewGuid(); 
  byte[] b = guid.ToByteArray(); 
  Array.Reverse((Array) b, 0, 4); 
  Array.Reverse((Array) b, 4, 2); 
  Array.Reverse((Array) b, 6, 2); 
  return new Guid(b); 

So if you want to use the same kind of pseudo-GUIDs for your own custom entities you create from code, you can get the IDs by invoking the method (for example, via PowerShell). The code sample below illustrates, how to get a single ID, or a batch of  IDs (in this case, 5 of them):

# load the necessary assembly
# generate a single sequential ID
# or generate a range of sequential IDs, in this case, five of them
(1..5) | % { [Microsoft.Office.Project.Server.Library.PSUtility]::NewSequentialUid().Guid }

October 1, 2015

The SharePoint Time Machine

Filed under: SP 2013, Tips & Tricks — Tags: , — Peter Holpar @ 22:06

Assume you have a SharePoint list with a lot of items. The list supports versioning and you should provide a snapshot of the items at a given time in the past.

As you know, the Versions property (of type SPListItemVersionCollection) of the SPListItem class contains the item versions. One can access a specific version via the indexer property of the collection, by the ID of the version (where the ID = 512 * major version number + minor version number), or by the version number (a.k.a. label, for example, 2.3), but there is no direct support to get the actual version at a specific time in the past.

To achieve my goal, I’ve implemented the GetVersionFromDate extension method, that iterates through the method, and returns the version we need based on its creation date:

  1. public static SPListItemVersion GetVersionFromDate(this SPListItemVersionCollection versions, DateTime localDate)
  2. {
  3.     SPListItemVersion result = null;
  5.     if (versions != null)
  6.     {
  7.         DateTime date = versions.ListItem.Web.RegionalSettings.TimeZone.LocalTimeToUTC(localDate);
  9.         SPListItemVersion prevVersion = null;
  11.         // versions[0] – current item version
  12.         // versions[versions.Count – 1] – first item version created
  13.         for (int i = versions.Count – 1; i >= 0; i–)
  14.         {
  15.             SPListItemVersion version = versions[i];
  16.             if (version.Created > date)
  17.             {
  18.                 result = prevVersion;
  19.                 break;
  20.             }
  21.             // if it is the last (actual) version and there is no result yet,
  22.             // then the date specified should be greater than the creation date of the last version
  23.             // we take the last version
  24.             else if (i == 0)
  25.             {
  26.                 result = version;
  27.             }
  29.             prevVersion = version;
  30.         }                
  32.     }
  34.     return result;
  35. }

Note, that the Created property stores the creation date as UTC time, that we should convert first.

Using this method accessing the specific version is so simple as:

  1. SPList list = web.Lists["Your List"];
  2. SPListItem item = list.Items.GetItemById(1);
  4. DateTime date = DateTime.Parse("2015/06/29 13:40");
  5. SPListItemVersion version = item.Versions.GetVersionFromDate(date);
  6. Console.WriteLine(version["APropertyName"]);

If you go through the items in the list and get the version of the specific time, you already have the required snapshot.

February 9, 2015

“Decoding” SharePoint Error Messages using PowerShell

Filed under: PowerShell, SP 2010, Tips & Tricks — Tags: , , — Peter Holpar @ 22:26

When working with SharePoint errors in ULS logs, you can find the error message near to the stack trace. In case of simple methods the stack trace may be enough to identify the exact conditions under which the exception was thrown. However, if the method is complex, with a lot of conditions and branches, it is not always trivial to find the error source, as we don’t see the exception message itself, as it is stored in language-specific resource files, and you see only a kind of keyword in the code.

For example, let’s see the GetItemById method of the SPList object with this signature:

internal SPListItem GetItemById(string strId, int id, string strRootFolder, bool cacheRowsetAndId, string strViewFields, bool bDatesInUtc)

There is a condition near to the end of the method:

if (this.IsUserInformationList)
    throw new ArgumentException(SPResource.GetString("CannotFindUser", new object[0]));
throw new ArgumentException(SPResource.GetString("ItemGone", new object[0]));

How could we “decode” this keyword to the real error message? It is easy to achieve using PowerShell.

For example, to get the error message for the “ItemGone”:



Item does not exist. It may have been deleted by another user.

Note, that since the second parameter is an empty array, we can simply ignore it when invoking the static GetString method.

If you need the language specific error message (for example, the German one):

$ci = New-Object System.Globalization.CultureInfo("de-de")
[Microsoft.SharePoint.SPResource]::GetString($ci, "ItemGone")

it is

Das Element ist nicht vorhanden. Möglicherweise wurde es von einem anderen Benutzer gelöscht.

Having the error message, it is already obvious most of the time, at which line of code the exception was thrown.

It can also help to translate the localized message to the English one, and use it to look up a solution for the error on the Internet using your favorite search engine, as there are probably more results when you search for the English text.

August 31, 2014

How to use PowerShell to check if a SharePoint Group with a specified ID or name exists–Without error handling

Filed under: PowerShell, SP 2013, Tips & Tricks — Tags: , , — Peter Holpar @ 23:18

Recently I created a PowerShell script that should delete a group that has a specific name. If the script runs the second time, it throws an exception since the group is already deleted.

If you want to get/delete/add a group from/to a SPGroupCollection (like SiteGroups or Groups of an SPWeb) the methods throw typically exceptions of different kinds if the group does not exist (or already does exist in case of addition):

$web.SiteGroups.Remove(12345) throws
Specified argument was out of the range of valid values.

$web.SiteGroups.Remove("YourGroupName") throws
Group cannot be found.

$web.SiteGroups.GetByID(12345) and
$web.SiteGroups.GetByName("YourGroupName") throw
Group cannot be found.

$web.SiteGroups.Add("YourGroupName", $usr, $null, "Group description") throws
The specified name is already in use.

I wanted to eliminate the error messages. If these commands were PowerShell Cmdlets, we could use the common ErrorAction parameter with the value SilentlyContinue (see more here), however with standard .NET object calls only the Try/Catch block would be available.

Throwing and handling exceptions has always a performance penalty. How could we check if the group exists before trying to get/delete/add it from/to the collection?

After a short search on the .NET based samples I found:

  • A generic- and lambda expression-based solution, that is nice, but not easy to transfer to PowerShell.
  • An interesting solution, that uses the Xml property of the SPGroupCollection object .
  • A solution that is based on the GetCollection method of the SPGroupCollection object.

I chose the third sample to transfer to PowerShell. The equivalent PowerShell condition to check the group by ID:

@($web.SiteGroups.GetCollection(@(12345))).Count -eq 1

To check the group by name, we can use:

@($web.SiteGroups.GetCollection(@("YourGroupName"))).Count -eq 1

The parameter of the GetCollection method is an array, so we can use the same method to check if all or any of multiple groups exists.

For example, to check by ID if both of the groups we need exist:

@($web.SiteGroups.GetCollection(@(12345, 54321))).Count -eq 2

To check by name if any of the groups we need exists:

@($web.SiteGroups.GetCollection(@("YourGroupName1", "YourGroupName2"))).Count –gt 0

April 23, 2014

Who Has Deployed This Solution?

Filed under: PowerShell, Reflection, SP 2010, Tips & Tricks — Tags: , , , — Peter Holpar @ 22:09

Recently we had a problem in one of our server farms that was caused by a sandboxed solution that was deployed by mistake as farm solution as well. I wanted to know who has deployed the farm solution, but in the Central Administration we can see only the time of the deployment, but there is no information regarding the person who performed the action:


If we submit the following SQL query in the configuration database of the farm (using the name of the solution as the filter)…

  1. SELECT [Id]
  2.       ,[ClassId]
  3.       ,[ParentId]
  4.       ,[Name]
  5.       ,[Status]
  6.       ,[Version]
  7.       ,[Properties]
  8.   FROM [SharePoint_Config].[dbo].[Objects]
  9.   WHERE [Name] = 'addispname.wsp'

… we should receive two records as result that include XML objects in the Properties fields like these ones:

  1. <object type="Microsoft.SharePoint.Administration.SPPersistedFile, Microsoft.SharePoint, Version=, Culture=neutral, PublicKeyToken=71e9bce111e9429c">
  2.     <sFld type="Int64" name="m_FileSize">4748</sFld>
  3.     <fld type="System.Collections.Hashtable, mscorlib, Version=, Culture=neutral, PublicKeyToken=b77a5c561934e089" name="m_UpgradedPersistedFields" />
  4.     <fld name="m_Properties" type="null" />
  5.     <sFld type="String" name="m_LastUpdatedUser">CONTOSO\Administrator</sFld>
  6.     <sFld type="String" name="m_LastUpdatedProcess">vssphost4 (5876)</sFld>
  7.     <sFld type="String" name="m_LastUpdatedMachine">DEMO2010A</sFld>
  8.     <sFld type="DateTime" name="m_LastUpdatedTime">2013-08-22T00:01:04</sFld>
  9. </object>
  10. <object type="Microsoft.SharePoint.Administration.SPSolution, Microsoft.SharePoint, Version=, Culture=neutral, PublicKeyToken=71e9bce111e9429c">
  11.     <sFld type="Guid" name="m_Id">31079555-a0db-4dce-8ca4-51e3e40cb6d1</sFld>
  12.     <sFld type="Boolean" name="m_WebPartPackage">False</sFld>
  13.     <sFld type="Guid" name="m_Wppid">00000000-0000-0000-0000-000000000000</sFld>
  14.     <fld type="Microsoft.SharePoint.Administration.SPServerRole, Microsoft.SharePoint, Version=, Culture=neutral, PublicKeyToken=71e9bce111e9429c" name="m_DeploymentServerType">WebFrontEnd</fld>
  15.     <sFld type="Boolean" name="m_HasWebAppResource">False</sFld>
  16.     <fld type="System.Collections.Hashtable, mscorlib, Version=, Culture=neutral, PublicKeyToken=b77a5c561934e089" name="m_UpgradedPersistedFields" />
  17.     <fld name="m_Properties" type="null" />
  18.     <sFld type="String" name="m_LastUpdatedUser">CONTOSO\Administrator</sFld>
  19.     <sFld type="String" name="m_LastUpdatedProcess">vssphost4 (5876)</sFld>
  20.     <sFld type="String" name="m_LastUpdatedMachine">DEMO2010A</sFld>
  21.     <sFld type="DateTime" name="m_LastUpdatedTime">2013-08-22T00:01:04</sFld>
  22. </object>

As you can see, one of the objects (SPSolution) describes the solution itself, the other one (SPPeristedFile) describes the persisted file that belongs to the solution. From the properties of the persisted file we can read the information we need, see m_LastUpdatedUser.

If you don’t like the idea to query the SharePoint content database directly, there is an other way to achieve the same information, for example using PowerShell and a bit of Reflection.

First we get a reference to our solution either by ID (as long it is known):

$solution = Get-SPSolution -Identity ‘31079555-a0db-4dce-8ca4-51e3e40cb6d1’

or by Name:

$solution = Get-SPSolution | ? { $_.Name -eq ‘addispname.wsp’ }

We can get the persisted file of the solution via the SolutionFile property, then using Reflection we can read its internal LastUpdateInfo property that contains the information regarding the user that deployed the solution:

$persistedFile = $solution.SolutionFile
$persistedFileType = $persistedFile.GetType()
$bindingFlags = [System.Reflection.BindingFlags]::NonPublic -bor [System.Reflection.BindingFlags]::Instance
$pi_LastUpdateInfo = $persistedFileType.GetProperty(‘LastUpdateInfo’, [System.Reflection.BindingFlags]($bindingFlags))
$lastUpdateInfo = $pi_LastUpdateInfo.GetValue($persistedFile, $null)
Write-Host $lastUpdateInfo

We should have an output similar to this one:

User: CONTOSO\Administrator
Process:vssphost4 (5876)
Time:August 22, 2013 12:01:04.0000

Beyond the user, one can find other potentially interesting information as well, including the host the solution was deployed from, and the process used to deploy the solution (executable + process ID a.k.a. PID). In the sample above the solution was deployed from Visual Studio, but in other cases the value can be for example powershell (4736) that means it was deployed using PowerShell.

Older Posts »

Create a free website or blog at