Second Life of a Hungarian SharePoint Geek

June 25, 2014

How to restrict the properties of a single object in the results of Client Object Model requests sent from PowerShell on the server side

Filed under: Managed Client OM, PowerShell, Reflection, SP 2013 — Tags: , , , — Peter Holpar @ 21:49

In my recent posts I illustrated how can we restrict (either using expression trees or via dynamically compiled assemblies) on the server side the properties of entity collections returned by the client object model requests when using PowerShell. Although it has less effect on the network traffic, it may be interesting to see, how can we achieve a similar result when working with single objects (like a single list instance) instead of entity collections (for example, fields of a list).

A query without property restrictions might look like this in C#:

ClientContext ctx = new ClientContext("http://sp2013");
var list = ctx.Web.Lists.GetByTitle("Images");
ctx.Load(list);
ctx.ExecuteQuery();

The request contains no property restriction (see SelectAllProperties=true below),

image

and the response contains indeed dozens of properties.

image

In C# it is quite simple to specify using lambda expressions, which properties of the list we need:

ClientContext ctx = new ClientContext("http://sp2013");
var list = ctx.Web.Lists.GetByTitle("Images");
ctx.Load(list, l => l.Title, l => l.ItemCount);
ctx.ExecuteQuery();

The request will include the limitation,

image

and only the requested properties are returned in the response:

image

But how to achieve the same result from PowerShell?

Again, we can easily refactor the code with the lambda expressions into a static helper class:

public static class QueryHelper
{
    public static void LoadListWithLimtedFields(ClientContext ctx, List list)
    {
        ctx.Load(list, l => l.Title, l => l.ItemCount);
    }
}

In this case, the original method contains only the code below that is easy to translate to PowerShell:

ClientContext ctx = new ClientContext("http://sp2013");
var list = ctx.Web.Lists.GetByTitle("Images");
QueryHelper.LoadListWithLimtedFields(ctx, list);
ctx.ExecuteQuery();

The same functionality transformed to PowerShell is shown below:

  1. $url = "http://sp2013"
  2.  
  3. $referencedAssemblies = (
  4.     "Microsoft.SharePoint.Client, Version=14.0.0.0, Culture=neutral, PublicKeyToken=71e9bce111e9429c",
  5.     "Microsoft.SharePoint.Client.Runtime, Version=14.0.0.0, Culture=neutral, PublicKeyToken=71e9bce111e9429c",
  6.     "System.Core, Version=3.5.0.0, Culture=neutral, PublicKeyToken=b77a5c561934e089")
  7. $sourceCode = @"
  8. using Microsoft.SharePoint.Client;
  9. using System.Collections.Generic;
  10. using System.Linq;
  11.  
  12. public static class QueryHelper
  13. {
  14.     public static void LoadListWithLimtedFields(ClientContext ctx, List list)
  15.     {
  16.         ctx.Load(list, l => l.Title, l => l.ItemCount);
  17.     }
  18. }
  19. "@
  20.  
  21. Add-Type -ReferencedAssemblies $referencedAssemblies -TypeDefinition $sourceCode -Language CSharp;
  22. Add-Type -Path "C:\Program Files\Common Files\microsoft shared\Web Server Extensions\15\ISAPI\Microsoft.SharePoint.Client.dll"
  23. Add-Type -Path "C:\Program Files\Common Files\microsoft shared\Web Server Extensions\15\ISAPI\Microsoft.SharePoint.Client.Runtime.dll"
  24.  
  25. $ctx = New-Object Microsoft.SharePoint.Client.ClientContext($url)
  26. $list = $ctx.Web.Lists.GetByTitle("Images")
  27. [QueryHelper]::LoadListWithLimtedFields($ctx, $list)
  28. $ctx.ExecuteQuery()
  29.  
  30. Write-Host $list.Title, $list.ItemCount

Although more complex as the former solution, and recommended only for the hard-core developers, we can achieve the same result using expression trees built using Reflection. Let’s see how Visual Studio compiles the lambda expressions in our C# code into plain .NET objects and method calls. We have to build the code and open the assembly with a decompiler tool.

The original code as decompiled by JetBrains dotPeek:

ClientContext clientContext = new ClientContext("http://sp2013");
List byTitle = clientContext.Web.Lists.GetByTitle("Images");
clientContext.Load<List>(byTitle, new Expression<Func<List, object>>[2]
{
  (Expression<Func<List, object>>) (l => l.Title),
  (Expression<Func<List, object>>) (l => (object) l.ItemCount)
});
clientContext.ExecuteQuery();

The original code as decompiled by Reflector:

ParameterExpression CS$0$0001;
ClientContext ctx = new ClientContext("http://sp2013&quot;);
List list = ctx.Web.Lists.GetByTitle("Images");
ctx.Load<List>(list, new Expression<Func<List, object>>[] { Expression.Lambda<Func<List, object>>(Expression.Property(CS$0$0001 = Expression.Parameter(typeof(List), "l"), (MethodInfo) methodof(List.get_Title)), new ParameterExpression[] { CS$0$0001 }), Expression.Lambda<Func<List, object>>(Expression.Convert(Expression.Property(CS$0$0001 = Expression.Parameter(typeof(List), "l"), (MethodInfo) methodof(List.get_ItemCount)), typeof(object)), new ParameterExpression[] { CS$0$0001 }) });
ctx.ExecuteQuery();

The decompiled code helps us to create the same expression tree from PowerShell using Reflection:

  1. $url = "http://sp2013&quot;
  2.  
  3. # load the required client object model assemblies
  4. Add-Type -Path "C:\Program Files\Common Files\microsoft shared\Web Server Extensions\15\ISAPI\Microsoft.SharePoint.Client.dll"
  5. Add-Type -Path "C:\Program Files\Common Files\microsoft shared\Web Server Extensions\15\ISAPI\Microsoft.SharePoint.Client.Runtime.dll"
  6.  
  7. $ctx = New-Object Microsoft.SharePoint.Client.ClientContext($url)
  8. $list = $ctx.Web.Lists.GetByTitle("Images")
  9.  
  10. $expressionType = [System.Linq.Expressions.Expression]
  11. $parameterExpressionArrayType = [System.Linq.Expressions.ParameterExpression].MakeArrayType()
  12.  
  13. $lambdaMethod = $expressionType.GetMethods() | ? { $_.Name -eq "Lambda" -and $_.IsGenericMethod -and $_.GetParameters().Length -eq 2  -and $_.GetParameters()[1].ParameterType -eq $parameterExpressionArrayType }
  14. $lambdaMethodGeneric = $lambdaMethod.MakeGenericMethod([System.Func“2[Microsoft.SharePoint.Client.List,System.Object]])
  15.  
  16. $listType = [Microsoft.SharePoint.Client.List]
  17.  
  18. # query the Title propery of type System.String
  19. $param1 = [System.Linq.Expressions.Expression]::Parameter($listType, "l")
  20. $name1 = [System.Linq.Expressions.Expression]::Property($param1, "Title")
  21. $expression1 = $lambdaMethodGeneric.Invoke($Null, [System.Object[]] @($name1, [System.Linq.Expressions.ParameterExpression[]] @($param1)))
  22.  
  23. # query the ItemCount of type System.Int32
  24. $param2 = [System.Linq.Expressions.Expression]::Parameter($listType, "l")
  25. $name2 = [System.Linq.Expressions.Expression]::Property($param2, "ItemCount")
  26.  
  27. # convert the Int32 type to object
  28. $body2 = [System.Linq.Expressions.Expression]::Convert($name2, [System.Object])
  29.  
  30. $expression2 = $lambdaMethodGeneric.Invoke($Null, [System.Object[]] @($body2, [System.Linq.Expressions.ParameterExpression[]] @($param2)))
  31.  
  32. $loadMethod = [Microsoft.SharePoint.Client.ClientRuntimeContext].GetMethod("Load")
  33. $loadMethodGeneric = $loadMethod.MakeGenericMethod([Microsoft.SharePoint.Client.List])
  34.  
  35. # call Load with both of the expressions
  36. $loadMethodGeneric.Invoke($ctx, [System.Object[]] @($list, [System.Linq.Expressions.Expression`1[System.Func“2[Microsoft.SharePoint.Client.List,System.Object]][]] @($expression1, $expression2)))
  37.  
  38. $ctx.ExecuteQuery()
  39.  
  40. Write-Host $list.Title, $list.ItemCount

Using the methods described in this and in the former posts we can achieve the same network traffic limiting results from PowerShell as we got used to in the case of the C# code.

June 17, 2014

How to restrict the properties and Filter the results of Client Object Model requests sent from PowerShell – The alternative solution without expression trees

Filed under: Managed Client OM, PowerShell, SP 2013 — Tags: , , — Peter Holpar @ 23:24

Recently I blogged about how to implement the Where and Include functionality of the Client Object Model requests sent from PowerShell. That is great, however I don’t really like the idea to create Expression trees from scratch manually and call a lot of generic methods using Reflection to achieve this goal.

Isn’t there a simpler solution out there? Yes, there is.

In the solution illustrated below I split the functionality into two separate components. I create a static helper class that includes all of the linguistic features that are problematic to achieve form the standard PowerShell toolset.

Note: The sample below includes the Take function beyond the Where and Include methods, so only the first matching child element will be returned.

  1. public static class QueryHelper
  2. {
  3.     public static IQueryable<Field> FilterFields(this FieldCollection fields)
  4.     {
  5.         return fields.Where(f => f.TypeAsString == "Guid").Take(1).Include(f => f.Id, f => f.InternalName);
  6.     }
  7. }

The second component calls the helper method introduced above. This code contains only simple client object model features, all of the available via simple PowerShell method calls.

  1. ClientContext ctx = new ClientContext("http://sp2013&quot;);
  2. var list = ctx.Web.Lists.GetByTitle("Images");
  3. var fieldsQuery = list.Fields;
  4. var fields = ctx.LoadQuery(QueryHelper.FilterFields(fieldsQuery));
  5. ctx.ExecuteQuery();

In the next step we translate the second component to PowerShell, however we leave the C# code for the first part. From the C# code we build a temporary assembly in runtime, and call the helper method from PowerShell.

I’ve created two alternative solutions, in the first one, the code is compiled via the native Add-Type PowerShell cmdlet:

  1. $url = "http://sp2013&quot;
  2.  
  3. $referencedAssemblies = (
  4.     "Microsoft.SharePoint.Client, Version=14.0.0.0, Culture=neutral, PublicKeyToken=71e9bce111e9429c",
  5.     "Microsoft.SharePoint.Client.Runtime, Version=14.0.0.0, Culture=neutral, PublicKeyToken=71e9bce111e9429c",
  6.     "System.Core, Version=3.5.0.0, Culture=neutral, PublicKeyToken=b77a5c561934e089")
  7. $sourceCode = @"
  8. using Microsoft.SharePoint.Client;
  9. using System.Collections.Generic;
  10. using System.Linq;
  11. public static class QueryHelper
  12. {
  13.   public static IQueryable<Field> FilterFields(this FieldCollection fields)
  14.   {
  15.     return fields.Where(f => f.TypeAsString == "Guid").Take(1).Include(f => f.Id, f => f.InternalName);
  16.   }
  17. }
  18. "@
  19.  
  20. Add-Type -ReferencedAssemblies $referencedAssemblies -TypeDefinition $sourceCode -Language CSharp;
  21. Add-Type -Path "C:\Program Files\Common Files\microsoft shared\Web Server Extensions\15\ISAPI\Microsoft.SharePoint.Client.dll"
  22. Add-Type -Path "C:\Program Files\Common Files\microsoft shared\Web Server Extensions\15\ISAPI\Microsoft.SharePoint.Client.Runtime.dll"
  23.  
  24. $ctx = New-Object Microsoft.SharePoint.Client.ClientContext($url)
  25. $listFields = $ctx.Web.Lists.GetByTitle("Images").Fields
  26. $fieldQuery = [QueryHelper]::FilterFields($listFields)
  27. $fields = $ctx.LoadQuery($fieldQuery)
  28. $ctx.ExecuteQuery()
  29.  
  30. $fields | % { Write-Host $_.InternalName: $_.Id }

In the second one, the code is compiled directly via the CompileAssemblyFromSource method of the CSharpCodeProvider class:

  1. $url = "http://sp2013&quot;
  2.  
  3. Add-Type -Path "C:\Program Files\Common Files\microsoft shared\Web Server Extensions\15\ISAPI\Microsoft.SharePoint.Client.dll"
  4. Add-Type -Path "C:\Program Files\Common Files\microsoft shared\Web Server Extensions\15\ISAPI\Microsoft.SharePoint.Client.Runtime.dll"
  5.  
  6. $sourceCode = @"
  7. using Microsoft.SharePoint.Client;
  8. using System.Collections.Generic;
  9. using System.Linq;
  10. public static class QueryHelper
  11. {
  12.   public static IQueryable<Field> FilterFields(this FieldCollection fields)
  13.   {
  14.     return fields.Where(f => f.TypeAsString == "Guid").Take(1).Include(f => f.Id, f => f.InternalName);
  15.   }
  16. }
  17. "@
  18.  
  19. $parameters = New-Object System.CodeDom.Compiler.CompilerParameters
  20. $runtimeCompiler = New-Object Microsoft.CSharp.CSharpCodeProvider
  21. # supress command output by casting the command to void
  22. [void]$parameters.ReferencedAssemblies.Add("System.Core.dll");
  23. [void]$parameters.ReferencedAssemblies.Add("C:\Program Files\Common Files\microsoft shared\Web Server Extensions\15\ISAPI\Microsoft.SharePoint.Client.dll");
  24. [void]$parameters.ReferencedAssemblies.Add("C:\Program Files\Common Files\microsoft shared\Web Server Extensions\15\ISAPI\Microsoft.SharePoint.Client.Runtime.dll");
  25. $parameters.GenerateExecutable = $False
  26. $parameters.GenerateInMemory = $True
  27. $parameters.IncludeDebugInformation = $False
  28. $errCount = $compRes.Errors.Count
  29. $compRes = $runtimeCompiler.CompileAssemblyFromSource($parameters, $sourceCode)
  30. Write-Host Build error count: $errCount
  31.  
  32. If ($errCount -eq 0)
  33. {
  34.   $ctx = New-Object Microsoft.SharePoint.Client.ClientContext($url)
  35.   $listFields = $ctx.Web.Lists.GetByTitle("Images").Fields
  36.   $fieldQuery = [QueryHelper]::FilterFields($listFields)
  37.   $fields = $ctx.LoadQuery($fieldQuery)
  38.   $ctx.ExecuteQuery()
  39.  
  40. $fields | % { $_.InternalName }
  41. }

The request (as checked using Fiddler) now includes the necessary filters,

image

and the response includes only a single entry, and the two public properties (Id and InternalName) we requested.

image

The output on the console:

image

Note: Based on my experience, the temporary assemblies are cached in the PowerShell process. That means if you alter the C# code, you should restart the PowerShell console to let the changes get reflected.

Conclusion: I prefer this approach to the former ones, because we can write the lambda expressions as we got used to in C#. There is no need to write overcomplicated Expression method calls.

June 15, 2014

How to restrict the properties in the results of Client Object Model requests sent from PowerShell on the server side, implementing the Include method

Filed under: Managed Client OM, PowerShell, Reflection, SP 2013 — Tags: , , , — Peter Holpar @ 22:51

In my recent post I illustrated how one can limit the entities returned by the server when responding a Client Object Model request sent from PowerShell. Although that method restricts the entities to the ones we really need, it still returns all of the properties for those items.

See the SelectAllProperties = true attribute of the ChildItemQuery in the request captured by Fiddler:

image

And the response includes really all of the properties…

image

However, we need typically only a few properties, the others only increase the size of the response package sent by the server unnecessarily.

In the C# syntax we can limit the fields using the Include method (see Retrieving only specified properties of lists):

  1. ClientContext ctx = new ClientContext("http://sp2013&quot;);
  2. var list = ctx.Web.Lists.GetByTitle("Images");
  3. var fieldsQuery = list.Fields.Include(f => f.InternalName, f => f.Id);
  4. // var fieldsQuery = list.Fields;
  5. var fields = ctx.LoadQuery(fieldsQuery);
  6. ctx.ExecuteQuery();

Remark: The JavaScript version supports a kind of Include as well (see Retrieving Only Specified Properties of Lists Using JavaScript).

Following the same strategy as in the case of the Where method, we compile our code to an assembly, and decompile it using JetBrains dotPeek:

  1. ClientContext clientContext = new ClientContext("http://sp2013&quot;);
  2. IQueryable<Field> clientObjects = ClientObjectQueryableExtension.Include<Field>((IQueryable<Field>)clientContext.Web.Lists.GetByTitle("Images").Fields, new Expression<Func<Field, object>>[2]
  3.   {
  4.     (Expression<Func<Field, object>>) (f => f.InternalName),
  5.     (Expression<Func<Field, object>>) (f => (object) f.Id)
  6.   });
  7. clientContext.LoadQuery<Field>(clientObjects);
  8. clientContext.ExecuteQuery();

Decompiling the code with Reflector reveals the internals of the lambda expression query:

IQueryable<Field> fieldsQuery = ctx.Web.Lists.GetByTitle("Images").Fields.Include<Field>(new Expression<Func<Field, object>>[] { Expression.Lambda<Func<Field, object>>(Expression.Property(CS$0$0001 = Expression.Parameter(typeof(Field), "f"), (MethodInfo) methodof(Field.get_InternalName)), new ParameterExpression[] { CS$0$0001 }), Expression.Lambda<Func<Field, object>>(Expression.Convert(Expression.Property(CS$0$0001 = Expression.Parameter(typeof(Field), "f"), (MethodInfo) methodof(Field.get_Id)), typeof(object)), new ParameterExpression[] { CS$0$0001 }) });

Transferring this quite complicate expression to a bit more readable format our method looks like this:

  1. ClientContext ctx = new ClientContext("http://sp2013&quot;);
  2. var list = ctx.Web.Lists.GetByTitle("Images");
  3.  
  4. // query the InternalName propery of type System.String
  5. var param1 = Expression.Parameter(typeof(Field), "f");
  6. var name1 = Expression.Property(param1, "InternalName");
  7.  
  8. var expression1 = Expression.Lambda<Func<Field, object>>(name1, param1);
  9.  
  10. // query the Id of type System.Guid
  11. var param2 = Expression.Parameter(typeof(Field), "f");
  12. var name2 = Expression.Property(param2, "Id");
  13. // convert the Guid type to object
  14. var body2 = Expression.Convert(name2, typeof(object));
  15.  
  16. var expression2 = Expression.Lambda<Func<Field, object>>(body2, param2);
  17.  
  18. // call Include with both of the expressions
  19. var fieldsQuery = ClientObjectQueryableExtension.Include<Field>(list.Fields, expression1, expression2);
  20.  
  21. var fields = ctx.LoadQuery(fieldsQuery);
  22. ctx.ExecuteQuery();

The PowerShell version of the same functionality:

  1. $url = "http://sp2013&quot;
  2.  
  3. # load the required client object model assemblies
  4. Add-Type -Path "C:\Program Files\Common Files\microsoft shared\Web Server Extensions\15\ISAPI\Microsoft.SharePoint.Client.dll"
  5. Add-Type -Path "C:\Program Files\Common Files\microsoft shared\Web Server Extensions\15\ISAPI\Microsoft.SharePoint.Client.Runtime.dll"
  6.  
  7. $ctx = New-Object Microsoft.SharePoint.Client.ClientContext($url)
  8. $list = $ctx.Web.Lists.GetByTitle("Images")
  9.  
  10. $expressionType = [System.Linq.Expressions.Expression]
  11. $parameterExpressionArrayType = [System.Linq.Expressions.ParameterExpression].MakeArrayType()
  12.  
  13. $lambdaMethod = $expressionType.GetMethods() | ? { $_.Name -eq "Lambda" -and $_.IsGenericMethod -and $_.GetParameters().Length -eq 2  -and $_.GetParameters()[1].ParameterType -eq $parameterExpressionArrayType }
  14. $lambdaMethodGeneric = $lambdaMethod.MakeGenericMethod([System.Func“2[Microsoft.SharePoint.Client.Field,System.Object]])
  15.  
  16. $fieldType = [Microsoft.SharePoint.Client.Field]
  17.  
  18. # query the InternalName propery of type System.String
  19. $param1 = [System.Linq.Expressions.Expression]::Parameter($fieldType, "f")
  20. $name1 = [System.Linq.Expressions.Expression]::Property($param1, "InternalName")
  21. $expression1 = $lambdaMethodGeneric.Invoke($Null, [System.Object[]] @($name1, [System.Linq.Expressions.ParameterExpression[]] @($param1)))
  22.  
  23. # query the Id of type System.Guid
  24. $param2 = [System.Linq.Expressions.Expression]::Parameter($fieldType, "f")
  25. $name2 = [System.Linq.Expressions.Expression]::Property($param2, "Id")
  26.  
  27. # convert the Guid type to object
  28. $body2 = [System.Linq.Expressions.Expression]::Convert($name2, [System.Object])
  29.  
  30. $expression2 = $lambdaMethodGeneric.Invoke($Null, [System.Object[]] @($body2, [System.Linq.Expressions.ParameterExpression[]] @($param2)))
  31.  
  32. $includeMethod = [Microsoft.SharePoint.Client.ClientObjectQueryableExtension].GetMethod("Include")
  33. $includeMethodGeneric = $includeMethod.MakeGenericMethod([Microsoft.SharePoint.Client.Field])
  34.  
  35. # call Include with both of the expressions
  36. $fieldsQuery = $includeMethodGeneric.Invoke($Null, [System.Object[]] @($list.Fields, [System.Linq.Expressions.Expression`1[System.Func“2[Microsoft.SharePoint.Client.Field,System.Object]][]] @($expression1, $expression2)))
  37.  
  38. $fields = $ctx.LoadQuery($fieldsQuery)
  39. $ctx.ExecuteQuery()
  40.  
  41. $fields | % { Write-Host $_.InternalName: $_.Id }

Testing the script we found in Fiddler, that the request is limited to the properties we need:

image

and the response contains only the requested properties as well:

image

The output of the script should be similar to this one:

image

Note: In the case above, the child items are not restricted via a QueryableExpression, however you can combine the Where method described in the former post and the Include method to restrict both the child entities and their properties returned by the server:

fields.Where(f => f.TypeAsString == "Guid").Include(f => f.Id, f => f.InternalName)

In the PowerShell equivalent we chain our expression to achieve the same result:

  1. $url = "http://sp2013&quot;
  2.  
  3. # load the required client object model assemblies
  4. Add-Type -Path "C:\Program Files\Common Files\microsoft shared\Web Server Extensions\15\ISAPI\Microsoft.SharePoint.Client.dll"
  5. Add-Type -Path "C:\Program Files\Common Files\microsoft shared\Web Server Extensions\15\ISAPI\Microsoft.SharePoint.Client.Runtime.dll"
  6.  
  7. $ctx = New-Object Microsoft.SharePoint.Client.ClientContext($url)
  8. $list = $ctx.Web.Lists.GetByTitle("Images")
  9.  
  10. $expressionType = [System.Linq.Expressions.Expression]
  11. $parameterExpressionArrayType = [System.Linq.Expressions.ParameterExpression].MakeArrayType()
  12.  
  13. $lambdaMethod = $expressionType.GetMethods() | ? { $_.Name -eq "Lambda" -and $_.IsGenericMethod -and $_.GetParameters().Length -eq 2  -and $_.GetParameters()[1].ParameterType -eq $parameterExpressionArrayType }
  14.  
  15. $fieldType = [Microsoft.SharePoint.Client.Field]
  16.  
  17. # call the Where method first
  18. $lambdaMethodGenericBool = $lambdaMethod.MakeGenericMethod([System.Func“2[Microsoft.SharePoint.Client.Field,System.Boolean]])
  19.  
  20. $param = [System.Linq.Expressions.Expression]::Parameter($fieldType, "f")
  21. $name = [System.Linq.Expressions.Expression]::PropertyOrField($param, "TypeAsString")
  22. $body = [System.Linq.Expressions.Expression]::Equal($name, [System.Linq.Expressions.Expression]::Constant("Guid"))
  23. $expression = $lambdaMethodGenericBool.Invoke($Null, [System.Object[]] @($body, [System.Linq.Expressions.ParameterExpression[]] @($param)))
  24.  
  25. $whereMethod = [System.Linq.Queryable].GetMethods() | ? { $_.Name -eq "Where" -and $_.ToString() -like "*TSource,System.Boolean*" }
  26. $whereMethodGeneric = $whereMethod.MakeGenericMethod([Microsoft.SharePoint.Client.Field])
  27.  
  28. $fieldsQuery = $whereMethodGeneric.Invoke($Null, [System.Object[]] @($list.Fields, $expression))
  29.  
  30. # call the Include method next on the result of the Where method
  31. $lambdaMethodGenericObj = $lambdaMethod.MakeGenericMethod([System.Func“2[Microsoft.SharePoint.Client.Field,System.Object]])
  32.  
  33. # query the InternalName propery of type System.String
  34. $param1 = [System.Linq.Expressions.Expression]::Parameter($fieldType, "f")
  35. $name1 = [System.Linq.Expressions.Expression]::Property($param1, "InternalName")
  36. $expression1 = $lambdaMethodGenericObj.Invoke($Null, [System.Object[]] @($name1, [System.Linq.Expressions.ParameterExpression[]] @($param1)))
  37.  
  38. # query the Id of type System.Guid
  39. $param2 = [System.Linq.Expressions.Expression]::Parameter($fieldType, "f")
  40. $name2 = [System.Linq.Expressions.Expression]::Property($param2, "Id")
  41.  
  42. # convert the Guid type to object
  43. $body2 = [System.Linq.Expressions.Expression]::Convert($name2, [System.Object])
  44.  
  45. $expression2 = $lambdaMethodGenericObj.Invoke($Null, [System.Object[]] @($body2, [System.Linq.Expressions.ParameterExpression[]] @($param2)))
  46.  
  47. $includeMethod = [Microsoft.SharePoint.Client.ClientObjectQueryableExtension].GetMethod("Include")
  48. $includeMethodGeneric = $includeMethod.MakeGenericMethod([Microsoft.SharePoint.Client.Field])
  49.  
  50. # call Include with both of the expressions
  51. $fieldsQuery2 = $includeMethodGeneric.Invoke($Null, [System.Object[]] @($fieldsQuery, [System.Linq.Expressions.Expression`1[System.Func“2[Microsoft.SharePoint.Client.Field,System.Object]][]] @($expression1, $expression2)))
  52.  
  53. $fields = $ctx.LoadQuery($fieldsQuery2)
  54. $ctx.ExecuteQuery()
  55.  
  56. $fields | % { Write-Host $_.InternalName: $_.Id }

When checking the network traffic using Fiddler we found that both the request

image

and the response fulfills our expectations.

The output of the script contains only the two returned fields due to the filtering on the server side:

image

How to filter the results of Client Object Model requests sent from PowerShell on the server side, implementing the Where method

Filed under: Managed Client OM, PowerShell, Reflection, SP 2013 — Tags: , , , — Peter Holpar @ 00:31

There are cases when we have to use the Managed Client Object Model from PowerShell. One of the most well-known cases is when we would like to manage our Office 365 environment (or access data stored in it) from our PowerShell scripts.

The Managed Client Object Model itself supports the server side filtering of data requested from the client using the Where method (see Applying filters to list retrieval), in contrary to the ECMAScript Client Object Model, where it is possible only via an unsupported workaround.

What it makes easy to use from advanced .NET programming languages like C# are the concepts of static methods, LINQ and lambda expression, all of them is quite far away from the standard PowerShell samples. No surprise, that the examples we can find on the web do not apply this filtering technique at all. If you have a look at this example, you can see, that it first downloads the whole list of site columns over the wire, then filters them on the client side just to get the two columns we need. If we have hundreds of columns, it can be pretty an overkill.

Note: Yes, I know that REST makes it possible (via the $filter query option) to filter the items returned, however it has its own limitation, like the lack of the batch requests – e.g. aggregating requests on the client side and sending them in batches via executeQuery – that is (at least, IMHO) one of the best features of the Client Object Model. Furthermore, I feel REST does not fit so good to the concept of PowerShell as the usage of the Managed Client Object Model.

Since we are able to use the same .NET libraries from PowerShell as from C#, it should be possible to achieve the same filtering result as well. But how to start?

First, let’s see a simple C# sample of a Managed Client Object Model call. In this sample we get the list of those fields of the Images list, that are of type Guid.

  1. ClientContext ctx = new ClientContext("http://sp2013&quot;);
  2. var list = ctx.Web.Lists.GetByTitle("Images");
  3. var fieldsQuery = list.Fields.Where(f => f.TypeAsString == "Guid");
  4. var fields = ctx.LoadQuery(fieldsQuery);
  5. ctx.ExecuteQuery();

How could we translate this one to a version that (although maybe less readable for us, humans) better reflects the object model and method calls behind the syntax sugar of C# (like static methods, and so on)? If we compile our code into an assembly, and open that assembly with a decompiler tool, like JetBrains dotPeek, the result may help us a step further:

  1. ClientContext clientContext = new ClientContext("http://sp2013&quot;);
  2. IQueryable<Field> clientObjects = Queryable.Where<Field>((IQueryable<Field>)clientContext.Web.Lists.GetByTitle("Images").Fields, (Expression<Func<Field, bool>>)(f => f.TypeAsString == "Guid"));
  3. clientContext.LoadQuery<Field>(clientObjects);
  4. clientContext.ExecuteQuery();

That seems already far better, but what can we do with the lambda expression?

Opening the assembly with another decompiler tool, Reflector reveals further details about the internal working of this functionality:

IQueryable<Field> fieldsQuery = Queryable.Where<Field>(ctx.Web.Lists.GetByTitle("Images").Fields, Expression.Lambda<Func<Field, bool>>(Expression.Equal(Expression.Property(CS$0$0000 = Expression.Parameter(typeof(Field), "f"), (MethodInfo) methodof(Field.get_TypeAsString)), Expression.Constant("Guid", typeof(string)), false, (MethodInfo) methodof(string.op_Equality)), new ParameterExpression[] { CS$0$0000 }));

As this decompiled code suggest, and as learned from this forum answer, we can substitute the lambda expression with an equivalent expression tree built dynamically.

The version we receive after this transformation:

  1. ClientContext ctx = new ClientContext("http://sp2013&quot;);
  2. var list = ctx.Web.Lists.GetByTitle("Images");
  3.  
  4. var param = Expression.Parameter(typeof(Field), "f");
  5. var name = Expression.PropertyOrField(param, "TypeAsString");
  6. var body = Expression.Equal(name, Expression.Constant("Guid"));
  7.  
  8. var expression = Expression.Lambda<Func<Field, bool>>(body, param);
  9. var fieldsQuery = Queryable.Where<Field>(list.Fields, expression);
  10.  
  11. var fields = ctx.LoadQuery(fieldsQuery);
  12. ctx.ExecuteQuery();

All we now have yet to do is to translate the C# code to PowerShell. The translation is not extremely complicated, it is mainly a mechanical process, although includes a lot of Type objects and Reflection calls, and we need a few tricks as well to achieve our goal.

For example, in the code above we use the static generic Lambda method of the Expression class with parameter types Expression and ParameterExpression[]. If we liked to get this method by name and parameter types directly using the GetMethod method of the Type class, we would receive an “Ambiguous match found” exception. The cause is that the Expression class has two methods with the same name: one generic one, and a non-generic version, and the GetMethod method does not support to restrict the filter to only one of these. Probably we can get the right one using the FindMembers method as well, but I found it easier to use the GetMethods method to get all of the methods of the Expression class and filter the results in my PowerShell code. In the code below we filter the methods, first by name, next we restrict the results to generic methods, and take the override that has two parameters, and the second parameter is of type ParameterExpression[]:

$lambdaMethod = $expressionType.GetMethods() | ? { $_.Name -eq "Lambda" -and $_.IsGenericMethod -and $_.GetParameters().Length -eq 2  -and $_.GetParameters()[1].ParameterType -eq $parameterExpressionArrayType }

I had similar problems with the Where method of the Query class. Although this method has only two overrides, and these ones have different parameter signatures, the parameters are rather complicated generic parameters, like Expression<Func<TSource, int, bool>>, so I decided to filter the methods again from my code, taking the methods called Where, and selecting the one that has a textural representation with the right parameter types:

$whereMethod = [System.Linq.Queryable].GetMethods() | ? { $_.Name -eq "Where" -and $_.ToString() -like "*TSource,System.Boolean*" }

The result of the translation is:

  1. $url = "http://sp2013&quot;
  2.  
  3. $ctx = New-Object Microsoft.SharePoint.Client.ClientContext($url)
  4. $list = $ctx.Web.Lists.GetByTitle("Images")
  5.  
  6. $expressionType = [System.Linq.Expressions.Expression]
  7. $parameterExpressionArrayType = [System.Linq.Expressions.ParameterExpression].MakeArrayType()
  8.  
  9. $lambdaMethod = $expressionType.GetMethods() | ? { $_.Name -eq "Lambda" -and $_.IsGenericMethod -and $_.GetParameters().Length -eq 2  -and $_.GetParameters()[1].ParameterType -eq $parameterExpressionArrayType }
  10. $lambdaMethodGeneric = $lambdaMethod.MakeGenericMethod([System.Func“2[Microsoft.SharePoint.Client.Field,System.Boolean]])
  11.  
  12. $fieldType = [Microsoft.SharePoint.Client.Field]
  13. $param = [System.Linq.Expressions.Expression]::Parameter($fieldType, "f")
  14. $name = [System.Linq.Expressions.Expression]::PropertyOrField($param, "TypeAsString")
  15. $body = [System.Linq.Expressions.Expression]::Equal($name, [System.Linq.Expressions.Expression]::Constant("Guid"))
  16. $expression = $lambdaMethodGeneric.Invoke($Null, [System.Object[]] @($body, [System.Linq.Expressions.ParameterExpression[]] @($param)))
  17.  
  18. $whereMethod = [System.Linq.Queryable].GetMethods() | ? { $_.Name -eq "Where" -and $_.ToString() -like "*TSource,System.Boolean*" }
  19. $whereMethodGeneric = $whereMethod.MakeGenericMethod([Microsoft.SharePoint.Client.Field])
  20.  
  21. $fieldsQuery = $whereMethodGeneric.Invoke($Null, [System.Object[]] @($list.Fields, $expression))
  22.  
  23. #$whereMethod = [System.Linq.Queryable].GetMethod("Where", [System.Type[]] @([System.Linq.IQueryable[TSource]], [System.Linq.Expressions.Expression[System.Func[TSource,bool]]]))
  24.  
  25. $fields = $ctx.LoadQuery($fieldsQuery)
  26. $ctx.ExecuteQuery()

Note: Before using this code, you should load the necessary client object model assemblies via:

Add-Type -Path "C:\Program Files\Common Files\microsoft shared\Web Server Extensions\15\ISAPI\Microsoft.SharePoint.Client.dll"
Add-Type -Path "C:\Program Files\Common Files\microsoft shared\Web Server Extensions\15\ISAPI\Microsoft.SharePoint.Client.Runtime.dll"

When validating by Fiddler, we can see that the request contains the filter we need:

image

And when checking the result, only the matching fields are returned, and all without filtering the results on the client side.

image

Conclusion: I don’t want to suggest at all that you should always use this kind of filtering when requesting data from the server. It may be unnecessary or even inefficient if you have only a few or a few dozens of entities to return from the server. However, if it is really a large number of items, it may be useful to know how to get only the entities you really need.

Note: I’ve tested my solution with SharePoint 2013 on-premise, but it should work with SharePoint 2010 and Office 365 as well. You can however apply this technique not only to the SharePoint object model as well. For example, I used it first with the Project Server 2013 Client Object Model.

May 31, 2014

How to get the SharePoint application Pool accounts from code

Filed under: PowerShell, Reflection, SharePoint — Tags: , , — Peter Holpar @ 09:01

In the past months I had a lot of trouble because of the incorrectly specified accounts on various SharePoint web and service applications, most frequently due to their missing permissions on other resources.

If you need to determine the user account configured for a web application, it is not very complicated to find out via the user interface, as described here.

You can check the account used for service application similarly using the Service Account page in the Central Administration as illustrated below.

image

Assume, you need the same information from code (C# or PowerShell) to be able to automate the verification, or simply make things to be performed faster. I’ll show you how to achieve that. It is not complicated as the UI-version at all, once you know the right path in the object model structure to dig down into the requested piece of information.

Let’s see the application pool account of a SharePoint web application first.

In the first case I assume that the code runs in the SharePoint context (like a web part):

string userName = SPContext.Current.Site.WebApplication.ApplicationPool.Username;

If you don’t have access to the context, you have to find another way to the web application:

string userName = SPWebApplication.Lookup(new Uri(http://YourSharePoint)).ApplicationPool.Username;

The PowerShell script is very simple and straightforward:

$wa = Get-SPWebApplication http://YourSharePoint
$userName = $wa.ApplicationPool.Username

Next I show, how to get the identity of a service application. If the .NET type of the service application, like the type BdcService (defined in namespace Microsoft.SharePoint.BusinessData.SharedService) for the business data connectivity service, this task is not very tricky:

BdcService bdcService = SPFarm.Local.Services.GetValue<BdcService>();
SPIisWebServiceApplication bdcApp = bdcService.Applications.FirstOrDefault() as SPIisWebServiceApplication;
// if you have multiple instances of the same type of service application, you can filter for example based on their name
// SPIisWebServiceApplication bdcApp = bdcService.Applications.FirstOrDefault(a => a.Name == "Business Data Connectivity Service") as SPIisWebServiceApplication;
string serviceAccountName = bdcApp.ApplicationPool.ProcessAccount.LookupName();

Things get a little bit more complicated, if the .NET type of the service application is defined as internal, as in the case of UserProfileService. The task can be completed using Reflection, see a similar problem and solution in my former post. Some extra lines (like checking for null values) were removed in sake of readability, but it should not affect the functionality in the standard case.

  1. // hack to get the Microsoft.Office.Server.UserProfiles assembly
  2. Assembly userProfilesAssembly = typeof(UserProfile).Assembly;
  3. // UserProfileService is an internal classes,
  4. // so you cannot get them directly from Visual Studio
  5. // like I do with the SPServiceCollection type
  6. Type userProfileServiceType = userProfilesAssembly.GetType("Microsoft.Office.Server.Administration.UserProfileService");
  7. Type spServiceCollectionType = typeof(SPServiceCollection);
  8.  
  9. // first we call
  10. // SPFarm.Local.Services.GetValue<UserProfileService>()
  11. MethodInfo mi_GetValue = spServiceCollectionType.GetMethod("GetValue",
  12.         BindingFlags.Public | BindingFlags.Instance, null, new Type[0], null
  13.         );
  14. // get the generic version of GetValue method
  15. MethodInfo mi_GetValueGeneric = mi_GetValue.MakeGenericMethod(userProfileServiceType);
  16. Object userProfileService = mi_GetValueGeneric.Invoke(SPFarm.Local.Services, null);
  17.  
  18. System.Reflection.PropertyInfo pi_Applications = userProfileServiceType.GetProperty("Applications", BindingFlags.NonPublic | BindingFlags.Instance);
  19. // userProfileApplicationCollection is of type Microsoft.Office.Server.Administration.UserProfileApplicationCollection
  20. IEnumerable<SPIisWebServiceApplication> userProfileApplicationCollection = pi_Applications.GetValue(userProfileService, null) as IEnumerable<SPIisWebServiceApplication>;
  21. SPIisWebServiceApplication userProfileApplication = userProfileApplicationCollection.FirstOrDefault();
  22. // if you have multiple instances of the same type of service application, you can filter for example based on their name
  23. //SPIisWebServiceApplication userProfileApplication = userProfileApplicationCollection.FirstOrDefault(a => a.Name == "User Profile Service Application");
  24. string serviceAccountName = userProfileApplication.ApplicationPool.ProcessAccount.LookupName();

PowerShell provides a simple solution, independently of the external visibility of the service class type. It is due to the fact that the microsoft.sharepoint.powershell assembly is defined as a friend assembly in the microsoft.sharepoint assembly, so the former one has access to the internal members of the latter one.

$ups = Get-SPServiceApplication | ? { $_.TypeName -eq "User Profile Service Application" }
$serviceAccountName  = $ups.ApplicationPool.ProcessAccount.Name

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:

image

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=14.0.0.0, Culture=neutral, PublicKeyToken=71e9bce111e9429c">
  2.     <sFld type="Int64" name="m_FileSize">4748</sFld>
  3.     <fld type="System.Collections.Hashtable, mscorlib, Version=2.0.0.0, 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=14.0.0.0, 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=14.0.0.0, 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=2.0.0.0, 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)
Machine:DEMO2010A
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.

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 http://intranet.contoso.com
$list = $web.Lists["DocLib"]
$item = $list.GetItemById(2)
$file = $item.File
$file

image

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)
$file.UndoCheckOut()

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 http://intranet.contoso.com
$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
$impFile.ReleaseLock($impFile.LockId)

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.

image

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”
$web.Update()

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
Do
{
  $newFolderName = $folderName
  $folder = $web.GetFolder( ($web.Url, $docLib.RootFolder.Url, $folderName -join "/") )
  $tryCount++
  $folderName = $date + "_" + $tryCount 
}
While ($folder.Exists)

Write-Host $newFolderName

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

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

# load the custom assembly (GACed!) into the PowerShell context
[void][System.Reflection.Assembly]::LoadWithPartialName("MyCompany.ReportHelper.AssemblyName")

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)
{
  $latestFolder.Delete()
}

# re-create the ‘latest’ folder, copying the actual version
$newFolder.CopyTo($latestFolderUrl)

$web.Dispose()
$site.Dispose()

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.

Older Posts »

The Shocking Blue Green Theme. Blog at WordPress.com.

Follow

Get every new post delivered to your Inbox.

Join 54 other followers