SharePoint 2010

This post is part of the Second Wednesday Demo Session, Click here for more info about additional demo posts.

In a previous post we used Windows PowerShell to Create a Database, add a table and populate the table with alot of entries.

In the last part of this Demo we’ll use SharePoint 2010 to populate a List in SharePoint based on a Table in SQL and finally Script up alot of Computers based on a SharePoint List.
First let’s take a look at the SharePoint List.

The List contains three Columns that we will use: Title, Description and Create. Create in this example is a Yes/No column.

In a previous post we populated a Table in SQL with Computers. Let’s take a look at how we can read information from that list. The function below demonstrates how we can read data from a table.

function Get-SQLComputer {

  $Connection = New-Object System.Data.SqlClient.SqlConnection
  $Connection.ConnectionString = "server=SQL01;database=TestDB;trusted_connection=true;"

  # open Connection
  $Command = New-Object System.Data.SQLClient.SQLCommand
  $Command.Connection = $Connection
  # Add Query
  $Command.CommandText = "SELECT * FROM Computers"
  # read from table
  $Reader = $Command.ExecuteReader()
  $Counter = $Reader.FieldCount
  # itterate each row
  while ($Reader.Read()) {
      $SQLObject = @{}
      for ($i = 0; $i -lt $Counter; $i++) {
    # return hashTable
  # Close Connection

The function above returns the rows in the table. Now let’s add them to our custom SHarePoint list.

Note that the function above doesn’t require any input. If you write a function that takes input when performing a query you should consider using parameterized queries to avoid possible injections. here’s a short example:

function Get-SQL([string]$ComputerName) {

  $Connection = New-Object System.Data.SqlClient.SqlConnection
  $Connection.ConnectionString = "server=SQL01;database=TestDB;trusted_connection=true;"

  $Command = New-Object System.Data.SQLClient.SQLCommand
  $Command.Connection = $Connection

  $Command.CommandText = "SELECT * FROM Computers WHERE Computer = @ComputerName"

  # These two lines are added to avoid injections
  $SQLParameter = New-Object System.Data.SqlClient.SqlParameter("@ComputerName", $ComputerName)

  $Reader = $Command.ExecuteReader()
  $Counter = $Reader.FieldCount

  while ($Reader.Read()) {
      $SQLObject = @{}
      for ($i = 0; $i -lt $Counter; $i++) {



First we use Add-PSSNapin to add the SharePoint 2010 CmdLets.

PS > Add-PSSNapin Microsoft.SharePoint.PowerShell

If your not running in sta mode, set the threadoption to “ReuseThread”.

PS > $host.Runspace.ThreadOptions = "ReuseThread"

Next we use Get-SPWeb to return a specific Site.

PS > $spWeb = Get-SPWeb http://SP01

Now we get the “Computer” list using the GetList() method.

PS > $spList = $spWeb.GetList("/Lists/Computers")

The spList has a method, AddItem(), which we can use to add a new Item in the SharePoint List. the example below demonstartes how we use the Get-SQLComputer function
to add the computers from the SQL table to a list in SharePoint 2010.

PS > Get-SQLComputer | ForEach {
>> $item = $spList.AddItem()
>> $item["Title"] = $($_.Computer).TrimEnd()
>> $item["Description"] = $($_.Description).TrimEnd()
>> $item.Update()
>> }

Finally we dispose of the spWeb object.

PS > $spWeb.Dispose()

If we take a look at the list now, the Computers are added.

Finally, let’s create some of the computers in Active-Directory. The SharePoint List had a Yes/No Column named Create. Let’s only create computers where the field is set to Yes.

In this example I’ve checked the Create filed for: DT001, DT003 and DT005. Let’s see how we can retrieve does specific items from SharePoint 2010 wqithout retrieving the whole ListItem Collection.
First we get the specific list.

PS > $spWeb = Get-SPWeb http://SP01
PS > $spList = $spWeb.GetList("/Lists/Computers")

The spList contains a property, ItemCount, that show us how many ListItems that are in the list.

PS > $spList.ItemCount


The spList also contains a property, Items, that returns all items in the list. Note that this can consume alot of unnecesary memory if your working with large lists so a better way of retrieving listitems is by using a CAML query.
In this example we only want listItems where Create equals Yes. Here’s an example on how to write such a query.

PS > $spQuery = New-Object Microsoft.SharePoint.SPQuery;
PS > $query = "<Where><Eq><FieldRef Name='Create' /><Value Type='Boolean'>1</Value></Eq></Where>"
PS > $spQuery.Query = $query

An easy way of getting your CAML queries correct is by using a CAML query builder.

Now we can Get the specific listItems using the GetItems() method and the spQuery object as input.

PS > $listItems = $spList.GetItems($spQuery)

If we count the number of listItems in the ListItem Collection we’ll see that it only contains 3 items.

PS > $listItems.Count


We can use the Select-Object cmdlet to Select the properties that we want to work with.

PS > $listItems | Select-Object @{Name="Name";Expression={$_.Title}},
>> @{Name="Description";Expression={$_["Description"]}}

Name                  Description
----                  -----------
DT001                 Added Using PowerShell
DT003                 Added Using PowerShell
DT005                 Added Using PowerShell

Finally, let’s go ahead and create the computers in Active Directory. In this example well start a Rmote background job that does the work for us.

PS > $computers = $listItems | Select-Object @{Name="Name";Expression={$_.Title}},
>> @{Name="Description";Expression={$_["Description"]}}
>> Invoke-Command -ComputerName DC01 -ScriptBlock {
>>   Import-Module ActiveDirectory
>>   $args | Foreach {
>>     New-ADComputer -Name $($_.Name) -Description $($_.Description) -Path "OU=LabCenter,DC=PowerShell,DC=nu"
>>   }
>> } -ArgumentList $computers -AsJob

Id              Name            State      HasMoreData     Location             Command
--              ----            -----      -----------     --------             -------
7               Job7            Running    True            dc01                  ...

When the job completes the Computers are created in Active Directory

Rating 4.00 out of 5

Active Directory

This post is part of the Second Wednesday Demo Session, Click here for more info about additional demo posts.

In Server 2008 R2 you can import the ActiveDirectory Module to get a whole set of CmdLets that you can use to manage Active-Directory. One of the requirements is that the “Active Directory module for Windows PowerShell” feature is installed.
Let’s take a quick look at how to install the feature. First, we add the ServerManager module to our current session.

PS > Import-Module ServerManager

The Module adds three CmdLets to our Session:

PS > Get-Command -Module ServerManager

CommandType     Name
-----------     ----
Cmdlet          Add-WindowsFeature
Cmdlet          Get-WindowsFeature
Cmdlet          Remove-WindowsFeature

To add a new Feature we use Get-WindowsFeature to retrieve a specific feature and pipe it to Add-WindowsFeature.

PS > Get-WindowsFeature RSAT-AD-PowerShell | Add-WindowsFeature

Success Restart Needed Exit Code Feature Result
------- -------------- --------- --------------
True    No             Success   {Active Directory module for Windows Power...

Now we can use the ActiveDirectory module in our Session. To import the module we type:

PS > Import-Module ActiveDirectory

If we want to list all the Active Directory CmdLets available we use Get-Command with the Module parameter.

PS > Get-Command -Module ActiveDirectory

We can use these CmdLets to perform tasks when working against Active Directory such as Creating Computers, Users, Groups and much more.
If we wanted to create a new computer object we could simply type:

PS > New-ADComputer -Name Test01 -SAMAccountName Test01 -Path "OU=LabCenter,DC=powershell,DC=nu"

In the example above we create a new computer named Test01 in the path “OU=LabCenter,DC=powershell,DC=nu”.

We can use Get-ADComputer to retrieve information about the computer we just created.

PS > Get-ADComputer -Filter { Name -eq "Test01" }

DistinguishedName : CN=Test01,OU=LabCenter,DC=powershell,DC=nu
DNSHostName       :
Enabled           : True
Name              : Test01
ObjectClass       : computer
ObjectGUID        : ab02a68a-5e9d-42a9-b21c-81b609f9b151
SamAccountName    : Test01$
SID               : S-1-5-21-1355521696-3038714204-1100498938-2055
UserPrincipalName :

If we want to modify an existing Computer object we use Set-ADComputer as shown in the example below.

PS > Get-ADComputer -Filter { Name -eq "Test01" } | Set-ADComputer -Description "Yaay"

Rating 3.00 out of 5

setting accountExpires using PowerShell

Let’s take a look at how to set the accountExpires property on a user in Active-Directory. First bind to a user using [adsi] (You can use the Quest AD cmdlets or the AD cmdlets available in Server2008 R2, but in this post we’ll stick to [adsi])

PS > $user = [adsi]"LDAP://CN=Niklas Goude,CN=Users,DC=POWERSHELL,DC=NU"

Setting the account to never expire is easy, simply set the value to 0 as shown below.

PS > $user.accountExpires = 0
PS > $user.setInfo()

Setting account expires to a specific date is a little trickier, if we try to type a date (1/1/2010) an error occurs.

PS > $user.accountExpires = "1/1/2010"
PS > $user.setInfo()

Exception calling "setInfo" with "0" argument(s): "The attribute syntax specified to the directory service is invalid.
At line:1 char:14
+ $user.setInfo <<<< ()
    + CategoryInfo          : NotSpecified: (:) [], MethodInvocationException
    + FullyQualifiedErrorId : CatchFromBaseAdapterMethodInvokeTI

Instead, we have to convert the date to a Windows File Time using the ToFileTime() method supported by DateTime.

PS > (Get-Date 1/1/2010).ToFileTime()

The method returns a 64-bit integer which we use when setting a specific date as demonstrated below.

PS > $user.accountExpires = [string](Get-Date 1/1/2010).ToFileTime()
PS > $user.setInfo()

What about finding out which date an account expires? if we try to retrieve the value of the accountExpires property
from an object of the type DirectoryEntry “System.__ComObject” is returned

PS > $user = [adsi]"LDAP://CN=Niklas Goude,CN=Users,DC=POWERSHELL,DC=NU"
PS > $user.accountExpires

What we want to do is to use the DirectorySearcher class to retrieve a specific user.

$SearchRoot = [adsi]"LDAP://DC=POWERSHELL,DC=NU"
$searcher = New-Object System.DirectoryServices.DirectorySearcher($searchRoot)
$searcher.Filter = "(&(objectClass=user)(sAMAccountName=nigo))"
$user = $searcher.FindOne()

The SearchResult object returned displays the value of the accountexpires property.

PS > $user.Properties.accountexpires

The numeric values returned from the accountexpires property is a little hard to read so let’s convert it to an object of the type System.DateTime.

PS > [dateTime]::FromFileTime($($user.Properties.accountexpires))

Friday, January 01, 2010 12:00:00 AM

Finally, let’s take a look at comparing dates. Just to make it a little more fun lets say that the input string contains numeric values in the following form: YYYYMMDD.

PS > "20090101"

The string above represents January 01, 2009. A fun (and cool) way of working with strings is by using the -match operator. The –match and –notmatch operators try to match one or more of the set of string values on the left side of the operation using regular expressions. In the example below we match the first 4 numeric values and assign them to a property named Year, the following two digits are assigned to Month and the last two are assigned to Day.

PS > "20090101" -match "(?^\d{4})(?\d{2})(?\d{2})" | Out-Null
PS > $Matches

Name                           Value
----                           -----
Year                           2009
Day                            01
Month                          01
0                              20090101

We can retrieve the specific matches using the $Matches automatic variable.

PS > $Matches.Year
PS > $Matches.Day
PS > $Matches.Month

Next we use the Get-Date cmdlet to retrieve an object of the type DateTime.

PS > $dateOfExpiration = (Get-Date -year $matches.Year -month $matches.Month -day $matches.Day)
PS > $dateOfExpiration

Thursday, January 01, 2009 5:43:03 PM

We also store the users account expire date in a variable

PS > $currentDateOfExpiration = [dateTime]::FromFileTime($($user.Properties.accountexpires))
PS > $currentDateOfExpiration

Friday, January 01, 2010 12:00:00 AM

Now we can compare the dates. Since we don’t know the exact time we use the ToShortDateString() method.
The example below returns.

PS > $dateOfExpiration.ToShortDateString() -eq $currentDateOfExpiration.ToShortDateString()

Here’s what happens if the dates are the same.

PS > $dateOfExpiration = Get-Date 1/1/2010
PS > $dateOfExpiration.ToShortDateString() -eq $currentDateOfExpiration.ToShortDateString()

Rating 4.25 out of 5

Adding HomeFolder Through PowerShell

It’s been some time since my last post now, had alot of things to do at work, but now I’m back OnTrack with my blogging.

Last time we checked out how to add group membership through PowerShell, so now we should have a nice test environment in place, based on Star Trek.

In this post, we are going to script up Users homefolders and add each user to the correct folder. We’ll accomplish this through the following four steps:

  • Add a Share on the Server
  • Add unique folders for all Users
  • Add unique Permissions to the FOlders
  • Edit the User Objects in Active-Direcroty

I’m also going to re-use a script I wrote a couple of months ago, but we’ll get back to that.

Let’s start off by creating a Share. This can be done through the Create() method in the WMI class Win32_Share. The Win32_Share is well described in MSDN.

Since we want to make the script re-usable, we should check if the Share already exists. This is a simple procedure through PowerShell.

PS > $Share = "C:\Share"
PS > $ShareName = "Share"
PS > if ((gwmi Win32_Share | Where { $_.Path -eq $Share}).Path -eq $Share ) {
>>  Write-Host "Share: $ShareName already exists." -ForeGroundColor Red
>>  }

If the Share Already exists “Share: Share already exists” will be prompted, if not we can continue with the script.

Now that we know that the share doesn’t exist, we have to check that the folder exists, and if not, create the folder.

PS > if (!(Test-Path $Share)) {
>>  New-Item -Path $Share -type directory | Out-Null
>>  }

and finally, we can create our Share through WMI. Setting type to 0 creates a Disk Drive Share.

PS > $CreateShare = [wmiclass]"Win32_Share"
PS > $CreateShare.Create($Share,$ShareName,$Type) | Out-Null


Now that the share is up and running, we can create our HomeFolders. First we set up our HomeDrive and HomeFolder variables, we’ll also set up a User for the example.

PS > $Share = "Share"
PS > $User = "jeapic"
PS > $HomeDrive = "H"
PS > $HomeDirectory = "\\" + $env:COMPUTERNAME + "\" + $Share + "\" + $User

Since the script runs on the server where the Share is created, we can use the environment variable to retrieve the computername.

Next, we want to check if the user already has the homedrive and homedirectory set. We can use the Get-AD.ps1 script for this. I’m also adding the -ToObject switch since i want to use the object later on.

PS > $GetUser = ./Get-AD.ps1 -Domain $Domain -User $User -Filter sAMAccountName -ToObject
PS > if ($GetUser.homeDirectory -match $HomeDirecory -AND $GetUser.homeDrive -match $HomeDrive) {
>> Write-Host "User: $User HomeDrive Already Set" -ForeGroundColor Yellow
>> }

If the User already has the HomeDrive set, we won’t continue, if not, we can go ahead and add it. But before connecting the User to the folder, want to create and give the user FullControl of his HomeFolder. Here we can use the Set-FolderPermission.ps1 script

PS > $Domain = ""
PS > $DomainUser = $Domain + "\" + $User
PS > ./Set-FolderPermission.ps1 -Path $HomeDirectory -Access $DomainUser -Permission FullControl

The Set-FolderPermission.ps1 both created the folder and set up the permissions for us, now all we have to do is set HomeDrive and HomeDirectory to the User Object.

PS > $GetUser.Put("homeDirectory",$HomeDirectory)
PS > $GetUser.Put("homeDrive",$HomeDrive)
PS > $GetUser.SetInfo()

And that’s it.

Running the script Doesn’t require the Star Trek Csv file used in the other examples, it does however require you to loop through each Users that you want to add Homefolders to. In order to get a list of all Users within an OU you can use the Get-AD.ps1 script, as shown below.

PS > ./Get-AD.ps1 -domain "LDAP://OU=Star Trek: The Next Generation,DC=powershell,DC=nu" -User AllUsers -Property sAMAccountName | ForEach { 
>> ./Add-STHomeFolder.ps1 -Domain -User $_.sAMAccountName -Share Share -HomeDrive H
>> }

All I have to do now is change the LDAP path above and repeat the ForEach on each OU that contains Users that I want to add a HomeFolder to.

Here are a couple of screenshots on running the scripts:



Click Here to Download the Add-STShare.ps1 Script.

Click Here to Download the Add-STHomeFolder.ps1 Script.

The Get-AD.ps1 script is also required.

Here’s the Set-Foldepermission.ps1 Script that’s also required.

Rating 4.33 out of 5

Adding Group Membership Through PowerShell

Time to add some members to our groups. Following the steps in the previous posts, we should now have a couple of Users, groups and computers in our test environment. Group Names are based on the Character position in the Star Trek Csv file so now, all we have to do is match up the Characters with their Positions.

Starting of, Let’s collect the information we need from the Csv File.

There are 68 Characters in the file so I’m only going to select the first one in the eample below.

PS > $CsvFile = Import-Csv StarTrek.csv
PS > ($CsvFile | Select Character, Position, Series)[0] | fl

Character : Jean-Luc Picard
Position  : Commanding Officer
Series    : Star Trek: The Next Generation

What we want to do now is add each member to the correct group. Let’s take the first User, Captain Picard, as an example and see how this is done.

Since there are 4 different Commanding Officer groups, we want to make sure that we connect to the correct one. We know which Starship (OU) the Captain is in since and we have that information in the Csv file so we can use this in combination with the Get-AD.ps1 script.

First we have get the OU:s distinguishedName.

PS > $Domain = ""
PS > $Series = "Star Trek: The Next Generation"
PS > $OU = ./Get-AD.ps1 -Domain $Domain -OU $Series -property distinguishedName
PS > $OU

OU=Star Trek: The Next Generation,DC=powershell,DC=nu

PS > $DomainConnection = "LDAP://" + [string]$OU.distinguishedName
PS > $DomainConnection

LDAP://OU=Star Trek: The Next Generation,DC=powershell,DC=nu

Now that we have the connectionstring, we can use this to narrow down the search in the Get-AD.ps1 script. By typing -Domain followed by an OU:s distinguishedName forces the script to only search within the OU structure.

PS > $Character = "Jean-Luc Picard"
PS > $User = ./Get-AD.ps1 -Domain $DomainConnection -User $Character -property distinguishedName, Name
PS > $UserConnection = "LDAP://" + [string]$User.distinguishedName
PS > $UserConnection

LDAP://CN=Jean-Luc Picard,OU=Users,OU=Star Trek: The Next Generation,DC=powershell,DC=nu

PS > $UserdistinguishedName = [string]$User.distinguishedName
PS > $UserdistinguishedName

CN=Jean-Luc Picard,OU=Users,OU=Star Trek: The Next Generation,DC=powershell,DC=nu

So why bother to create one variable holding the distinguishedName and one holding the LDAP connection string ?? well, we’ll get to that in a while but first we need a connection to the group as well.

PS > $Position = "Commanding Officer"
PS > $Group = ./Get-AD.ps1 -Domain $DomainConnection -Group $Position -ToObject
PS > $Group | Format-List *

objectClass          : {top, group}
cn                   : {Commanding Officer}
description          : {Commanding Officer}
distinguishedName    : {CN=Commanding Officer,OU=Groups,OU...
instanceType         : {4}
whenCreated          : {4/15/2009 5:09:08 PM}
whenChanged          : {4/15/2009 5:09:08 PM}
uSNCreated           : {System.__ComObject}
uSNChanged           : {System.__ComObject}
name                 : {Commanding Officer}
objectGUID           : {74 231 168 162 107 193 157 74 161 
objectSid            : {1 5 0 0 0 0 0 5 21 0 0 0 50 71 101...
sAMAccountName       : {COMMANDIN}
sAMAccountType       : {268435456}
groupType            : {-2147483646}
objectCategory       : {CN=Group,CN=Schema,CN=Configuratio...
nTSecurityDescriptor : {System.__ComObject}

Now that we got the correct group, all we have to do is make a simple check so that the Captain isn’t already member of the group and if not, add him to it. Here’s where the distinguishedName and LDAP string come in handy.

PS > if ($Group.member -Contains $UserdistinguishedName) {
Write-Host “The Captain is already member”
} else {

Now let's how the complete script would handle this.


Repeating the script tells us that the Characters are already members of the groups.


And here's a quick check in the Active-Directory snapin.


Click Here to Download the Complete Script.

The Get-AD.ps1 script is also required.

Click here to download the Csv File

Rating 3.00 out of 5