PowerShell Arrays and that annoying number on the screen

When you create an array and populate it in PowerShell the number of elements is written out to the screen, very annoying.


function Array-Error() 
    $Array = New-Object System.Collections.ArrayList 
    $Return = "ReturnValue" 
    return $return 
$display = Array-Error 
write-host -ForegroundColor Red $display


0 1 2 3 ReturnValue

So how do we stop the numbers from being displayed to the screen, while Fix 2 is the proper way I like fix 1 as it reminds me of passing things over to “/dev/null” when I was a UNIX chap.

function Array-Fix1() 
    $Array = New-Object System.Collections.ArrayList 
    $Return = "ReturnValue" 
    $Array.Add("Value1") > $null 
    $Array.Add("Value2") > $null 
    $Array.Add("Value3") > $null 
    $Array.Add("Value4") > $null 
    return $return 
$display = Array-Fix1 
write-host -ForegroundColor Magenta $display




function Array-Fix2() 
    $Array = New-Object System.Collections.ArrayList 
    $Return = "ReturnValue" 
    $Array.Add("Value1") | Out-Null 
    $Array.Add("Value2") | Out-Null 
    $Array.Add("Value3") | Out-Null 
    $Array.Add("Value4") | Out-Null 
    return $return 
$display = Array-Fix2 
write-host -ForegroundColor Magenta $display



That x64 setting.

Today the following error was being thrown by a new SharePoint c# application,

“An unhandled exception of type ‘System.Runtime.InteropServices.COMException’ occurred in Microsoft.SharePoint.dll” with the following detail:

“Additional information: Retrieving the COM class factory for component with CLSID {BDEADF26-C265-11D0-BCED-00A0C90AB50F} failed due to the following error: 80040154 Class not registered (Exception from HRESULT: 0x80040154 (REGDB_E_CLASSNOTREG)).”

The application is being written in Visual Studio 2013, c# for SharePoint 2013 on a Microsoft Server 2012 R2 server.

To get over the issue just change the platform target in the build settings of the project to x64.


Virtual box to Hyper-V is one easy step

Tip of the day, this tip has been shamelessly stolen from Diary of a SharePoint consultant like many others I run Virtual Box and have lots of different environments ready to run to aid development & migration and the thought of re-creating them in Hyper-V has filled me me dread for quite a while now.

I do run Hyper-V on a couple of servers that I use but not on my portable development laptop that I carry around and I have been feeling increasingly hypercritical recommending that people run Hyper-V when I have not been running it on my laptop.

So a few days ago I decided to take the plunge and find a way forward moving over to Hyper-V on my laptop and I came across the posting that has made my life much easier, so I have put it on my blog so I can find it again.

'C:\Program Files\Oracle\Virtual Box\vboxmanage.exe' clonehd 'C:\VMs\VirtualBox\Server2012R2\SP2012r2.vdi' 'C:\VMs\Hyper-V\Server2012R2\SP2012r2.vhd' –format VHD

So far the conversions have gone well, I have converted:

Linux, IIS & MySQL, IIS & SQL Server 2012 / 2014, SharePoint 2003, SharePoint 2007, SharePoint 2010 & a SharePoint 2013 servers.

The only hiccup is that you will need to use Generation 1 hardware to mount the .vhd format disk but that is not a worry to me.

Office 365, ADFS & SSO

So you have made the move into Office 365, you have ADFS so you can have a single sign on so your user do not need to worry about logging in when they are in the office but it is not working, you check your ADFS setup and it looks good once you users logon in the morning they are good for the day so what are you missing?

There are a couple of setting that you may need to look at.

You will need to add the following into the “Local Intranet” zone in IE:

  • *.microsoftonline.com
  • *.office.com
  • Your ADFS server

You will also need to change one of the settings in the advance settings tab in IE.

Internet Options >> Security >> Click on the Local Internet zone >> click on the “Custom level…” >> User Authentication >> Logon >> Automatic log-on only in Intranet zone.

IE Settings for Office 365, ADFS & SSO
IE Settings for Office 365, ADFS & SSO (click for a bigger image)

This set of settings can be deployed via an AD group policy if needed.

Keeping your PowerShell tidy

I am quite a fan of Powershell but miss the ability to include other files in a simple and clean way.

I quite like the idea that as you run scripts in the different environments dev, test, UAT, staging and production they should never change, the only thing that should change are the variables in each environment to this end I will end up with at least three files per script.

  1. variables – This file contains the things that should and do change in each environment from service accounts, DNS names, environment names & descriptions.
  2. functions – This file will contain reusable blocks of code.
  3. process – This file will contain the process that should not change from environment to environment.

Now the chances are that the variables will be called variables.ps1, functions will be functions.ps1 & process will be called whatever is needed, and if the truth is told the functions.psq1 will be split into different files reflecting the different areas as needed.

The point of this post is not to bore you to death with the way I do work but to show you how to include the variables & functions files into your process script, and how to stop the script if there is an issue, copy and past the following into your Powershell script change as needed and you will be away.

$thisScript = Split-Path -Path $MyInvocation.MyCommand.Definition -Parent
    #Load the common variables script. 
    . ($thisScript + '.\variables.ps1')
    write-host -f red "---------------------------------------------------------------"	
    write-host -f red "|       The variables.ps1 file can not be loaded.             |"
    write-host -f red "|             This script will stop as a result.              |"
    write-host -f red "---------------------------------------------------------------"	
    #Load the common variables script. 
    . ($thisScript + '.\functions.ps1')
    write-host -f red "---------------------------------------------------------------"	
    write-host -f red "|       The functions.psq file can not be loaded.             |"
    write-host -f red "|             This script will stop as a result.              |"
    write-host -f red "---------------------------------------------------------------"	


PowerShell script to create an AD group in an OU

Active directory you have got to love it, using it as a base to secure up access to SharePoint is quite an important thing to understand but who wants to create the groups, better pass it over to a script to do.

This script is a starter and will create an AD group in a specified OU priming it ready to be linked to the SharePoint group (I will post up a script to do this in the future – search for it and if I remember I will link to it), as with all thing sI tend to do this script is a building block, it can use used in isolation but it is better to use it  as part of an automated process when provisioning sharePoint sites.

The function CreateADGroup would be best housed in an global functions file that you can include into other PowerShell scripts as you need, giving you reusability, the function is included in this script just so it can be stand alone.

Download file : CreateADGroup as a text file

#Script name :- CreateADGroup.ps1
#Script author :- Ian Ballard
#Version history.
#Version number | Date | Author | Comments
#1.00 | 07/11/2014 | Ian Ballard | Initial version. 
$ver = $host | select version
if ($ver.Version.Major -gt 1) {$Host.Runspace.ThreadOptions = "ReuseThread"}
#Include the PowerShell snap-ins & imports that will be needed. 
Import-Module ActiveDirectory
function CreateADGroup( [string] $ADGroup, [string] $ADGroupDisplayName, [string] $ADGroupDescription, [string] $ADOUPath )
 get-adgroup $ADGroup | out-null 
 if ( $debug -eq $true )
 write-host -f $debugColourStepIgnored "The AD Group $ADGroup is already in Active directory, skipping this step."
 New-ADGroup -Name $ADGroup -GroupCategory Security -GroupScope Global -DisplayName $ADGroupDisplayName -Description $ADGroupDescription -path $ADOUPath
 if ( $debug -eq $true )
 write-host -f $debugColour "The AD Group $ADGroup has been created in Active directory."
$ConfirmPreference = "None"
#Debug option. 
$debug = $true
$debugColour = "green"
$debugColourStepIgnored = "yellow"
#AD Group information for the owners of the site. 
$ADGroup = "New AD group"
$ADDisplayName = "New AD group display name"
$ADDisplayDescription = "This AD group was created via a PowerShell script."
$ADOUPath = "OU=DEV,OU=Development-Managed Applications,OU=Security,OU=Groups,DC=development,DC=local"
CreateADGroup ($ADGroup) ($ADDisplayName) ($ADDisplayName) ($ADOUPath)

SQL Server Audit

Who did what and when did they do it?

SQL Servers has some fantastic audit capability but sometimes a client will want to hand craft their own and this script can act as a place to start.

The script will create audit tables that will audit what your users are up to.

The script has some options:

set @Author = SUSER_NAME()
set @SPPrefix = ‘usp_’
set @Schema = ‘dbo’

set @DisplaySQL = 1
set @ExecuteSQL = 0

–1 – Create only audit tables where no audit tables are in existence.
–2 – Create all audit tables by dropping existing audit tables.
–3 – Create all audit tables and place current audit tables into archive e.g. _Archive to _Archive_+@DateStyle

set @CreationStyle = 1

–The date style for the _Archive rename as follows.
set @DateStyle = convert(varchar, getdate(), 9)

Use the different creation styles to hive off audit tables on a regular basis you don’t want the size of the tables to become an issue, run them daily, weekly or monthly depending on the needs of your system & users but archive them off.

There are two scripts for this one, there is the creation script and the drop script, use them as needed.

Download file : Audit.sql as a text file

Download file : DropAudit.sql as a text file


Database documentation

Documentation is the bane of my life you design a fantastic database and someone tells you that they would like to know how it works, so the task begins.

This script will take a look at the database and do the graft for you, if you have been well behaved and added descriptions to objects you will be golden as the script will go over your database and document the thing for you, of you then export the output to Excel you have a nice document to put into the document folder.

Download file : DocumentDatabase.sql as a text file

Database CRUD scripts

The use of a database is always a good idea and not giving users any direct access to the tables is an even better idea but wrapping the tables with stored procedures that you can secure up by group membership is a little bit of a pain, this script will enable you to create all of the CRUD stored procedures, you can control how the script runs via the options.

You can set all of the following:

set @Author = SUSER_NAME()
set @SPPrefix = ‘usp_’
set @Schema = ‘dbo’

set @createInsert = 1
set @createUpdate = 1
set @createDelete = 1
set @createSelect = 1
set @createSelectByPrimaryKey = 1

set @DisplaySQL = 0
set @ExecuteSQL = 1

Use the @ExecureSQL & @DisplaySQL options to generate a script that you can look at and run later.

Download file : CRUD.sql as a text file


What site template did that SharePoint site start as?

Sometimes you need to figure out what template was used when creating a SharePoint site,  you can mess about in the database (not recommended) you can look in the HTML of an output page as long as your master page has not changed some of the default options or you can open up the SharePoint management shell and use the following.

$web = Get-SPWeb -Identity "http://SharePointSiteURL"
$Template = $web.WebTemplate
$Template = $Template + "#"
$SiteTemplate = $Template + $web.WebTemplateID
Write-Host $SiteTemplate

Move that SharePoint SubSite into its own site collection.

So it seemed like a good idea at the time, all of the sites on the root site collection but now you are beginning to regret that the content database is getting a little on the large side and managing the security is getting to be a pain.

So how do you move this subsite into it’s own site collection?

You can go ahead and pick up a migration tool from ShareGate of Metalogix both very good tools but you only want to move the site, feel free to grab this PowerShell script that will do it for you.

All you need to do is change the “script options” to reflect your needs and you should be away.

Remember that you use this script at your own risk and  you should backup ad test everything before you commit to running it.

The script can run the commands or build a script for you to use at a later date, your choice just look at the $debug  & $execute  options.

Download file : ConvertSubSiteToSiteCollection as a text file


Finding that SharePoint Correlation ID

So you have an issue in SharePoint and a screenshot form a user in a production environment.

You have asked the production support team to put SharePoint log viewer onto the server and they have submitted the request to the change group and will get back to you in a couple of days and in the mean time your user is going up the wall.

So you have the Correlation ID and need to find out what is going on, you can use the Get-SPEventLog cmdlet and filter things but remembering the syntax is more that you can do after the day you are having.

Feel free to grab this script that you can put on the server pass the Correlation ID to it along with some other things and you will have the information you are looking for to help you get to the bottom of the problem.

Download file: GetSPError as a text file

Examples of usage:

GetSPError.ps1 -GUIDToFind 00000000-0000-0000-0000-000000000000 -NumberOfMinutes 10
This will only look in the last 10 minutes of the log.

GetSPError.ps1 -GUIDToFind 00000000-0000-0000-0000-000000000000 -NumberOfMinutes 0
This will look at all of the the log.

GetSPError.ps1 -GUIDToFind 00000000-0000-0000-0000-000000000000 -NumberOfMinutes 10 -FileName error.txt
This will only look in the last 10 minutes of the log and send the output to the error.txt file.

GetSPError.ps1 -GUIDToFind 00000000-0000-0000-0000-000000000000 -NumberOfMinutes 0 -FileName error.txt
This will look at all of the the log and send the output to the error.txt file.

Get-Help GetSPError.ps1
This will list the help for this file.

PowerShell script template

This file is the Powershell template file that I use when creating PowerShell scripts.

The code below is a functioning PowerShell script for you to build on, it has the syntax for the Get-Help cmdlet to function correctly and takes three parameters some mandatory some not, it also outputs to the screen the script execution start & end time along with elapsed time.

Download file : Powershell Template as a text file.

  1. <#
  3. This script will ....
  6. A Script description & version history.
  9. --------------------------------------------------------
  10. History
  11. Version number | Data | Comment
  12. 1.00 | <date> | Initial script.
  13. --------------------------------------------------------
  15. .LINK
  16. http://www.glossover.co.uk/archives/322
  18. .EXAMPLE
  19. <scriptName> -Param1 value -Param2 value
  20. <scriptName> -Param1 value -Param2 value -Param3 (optional) value
  22. #>
  25. Param (
  26. [Parameter(Mandatory=$true, HelpMessage="Please enter a string value")]
  27. [alias("Param1")]
  28. [ValidateNotNull()]
  29. #String value.
  30. [string]$stringValue,
  32. [Parameter(Mandatory=$true, HelpMessage="Please enter a numeric value.")]
  33. [alias("Param2")]
  34. [ValidateNotNull()]
  35. #Number value.
  36. [int]$intValue,
  38. [Parameter(Mandatory=$false, HelpMessage="Please enter a string value (Optional).")]
  39. [alias("Param3")]
  40. [ValidateNotNull()]
  41. #Optional string value.
  42. [string]$optionalStringValue
  44. )
  46. $startTime = Get-Date
  47. Write-Host "Start time : $startTime"
  50. #Passed values
  51. Write-Host "Param1 : " $stringValue
  52. Write-Host "Param2 : " $intValue
  53. Write-Host "Param3 (optional) : " $optionalStringValue
  56. $endTime = Get-Date
  57. Write-Host "Script execution time : $( New-TimeSpan $startTime $endTime)."
  58. Write-Host "End time : $endTime"