Update ConfigMgr packages with Hotfix-information

When you install a hotfix and/or a cumulative update in ConfigMgr you can select the option to let the installer create some packages.

But, those packages are missing some info… For instance Manufacturer and Version.

A quick and dirty SQL update will do the trick.

UPDATE SMSPackages_G
SET
	Version = LEFT(Replace(Source, '\\server.domain.com\SMS_ABC\hotfix\', ''), 9),
	Manufacturer = 'Microsoft',
	Language = 'All'
WHERE Source LIKE '\\server.domain.com\SMS_ABC\Hotfix%'

This is of course not supported… but afaik it works. πŸ˜€

If you want to see what would be updated, run this first

SELECT
	PkgID,
	Version AS OrgVersion,
	LEFT(Replace(Source, '\\server.domain.com\SMS_ABC\hotfix\', ''), 9) AS NewVersion,
	Manufacturer AS OrgManufacturer,
	'Microsoft' AS NewManufacturer,
	Language AS OrgLanguage,
	'All' AS NewLanguage
FROM SMSPackages_G
WHERE Source LIKE '\\server.domain.com\SMS_ABC\hotfix\%'

(And yes, you need to replace servername and sitecode)

Get-ADSites and create Site Based Collections

Missing an easy way to get AD-Sites from Powershell?

They are listed under Sistes/Configuration with the objectClass = Site. So a simple LDAP-query does the trick.

Get-ADSites {
	Return Get-AdObject -LdapFilter "(ObjectClass=site)" -SearchBase "CN=Sites,CN=Configuration,$((Get-AdDomain).DistinguishedName)"
}

Then, with these sites it’s easy to create Device Collections in ConfigMgr based on what AD-Site the client reports.

Get-ADSites | ForEach-Object {
	$newCollection = New-CMDeviceCollection -Name "AD-Site $($_.Name)" -LimitingCollectionID "SMS00001" -RefreshType Periodic -RefreshSchedule (New-CMSchedule -RecurInterval Days -RecurCount 1 -Start (Get-Date))
	$query = "SELECT * FROM SMS_R_System WHERE SMS_R_System.ADSiteName = '$($_.Name)'"
	Add-CMDeviceCollectionQueryMembershipRule -CollectionId $newCollection.CollectionID -QueryExpression $query -RuleName "ADSite-$($_.Name)"
}

Reset local admin password

You probably want to have random passwords on all your local admin accounts… Wrote a function that generates a complex (and readable) password and another function that sets the local admin password.

Function Get-RandomPassword {
	PARAM (
		$pwdMask = "####-####-####-####-####",
		$pwdCharacters = "abcdefghjkmnopqrstuvwxy23456789ABCDEFGHJKLMNPQRTUVWXYZ"
	)

	$newPassword = ""
	(0 .. (($pwdMask.Length)-1) ) | ForEach-Object  {
		If ( $pwdMask.Chars($_) -eq "#" ) {
			$rndChar = Get-Random -Minimum 0 -Maximum $pwdCharacters.Length
	 		$newPassword += $pwdCharacters.Chars($rndChar)
		} else {
			$newPassword += $pwdMask.Chars($_)
		}
	}

	Return $newPassword
}


Function Set-LocalAdminPassword {
	PARAM (
		[string] $computerName,
		[string] $newPassword
	)

	$adminAccountName = (Get-WmiObject Win32_UserAccount -Filter "LocalAccount = True AND SID LIKE 'S-1-5-21-%-500'" -ComputerName $computerName | Select-Object -First 1 ).Name
	TRY {
		Write-Verbose "Reset password for $($computerName)\$($adminAccountName) to $($newPassword)"
		$adminAccount = [adsi]"WinNT://$($computerName)/$($adminAccountName),user"
		$adminAccount.setPassword($newPassword)
		Return $true
	}
	CATCH {
		Return $false
	}
}

This is how to reset password on a single computer

Set-LocalAdminPassword -computerName "SOMEPC" -newPassword (Get-RandomPassword)

And if you want to process a list of computers (that are online) from the AD

Import-Module ActiveDirectory
$newPwds = @{}
Get-ADComputer -LDAPFilter "(name=PC00*)" | ForEach-Object {
	$computerName = $_.Name
	If (Test-Connection -ComputerName $computerName -Count 1 -ErrorAction SilentlyContinue) {
		$randomPwd = Get-RandomPassword
		If (Set-LocalAdminPassword -computerName $computerName -newPassword $randomPwd ) {
			$newPwds.Add($computerName, $randomPwd)
		}
	}
}

$newPwds | Format-Table -AutoSize

Remove all direct memberships from collections

If you are using direct memberships to speed up ConfigMgr, you probably want to clean it up when your collection query is good to go…

The problem is to find all direct memberships since Get-CMUserCollectionDirectMembershipRule doesn’t allow wildcards. But there is a WMI-class with all members…

Function Remove-AllDirectMemberships {
	PARAM (
		[string] $sccmSite = "ABC",
		[string] $collectionNameFilter = "SomePrefix*"
	)

	Get-CMUserCollection -Name $collectionNameFilter | ForEach-Object {
		$Collection = $_
		Write-host "$($Collection.Name)"
		Get-WmiObject -Class $_.MemberClassName -Namespace "ROOT\SMS\Site_$($sccmSite)" | Foreach-Object {
			If ($_.IsDirect -eq $true ) {
				Write-host "- Removing $($_.ResourceID)"
				Remove-CMUserCollectionDirectMembershipRule -CollectionId $Collection.CollectionID -ResourceId $_.ResourceID -Force
			}
		}
	}
}

Default Powershell Execution Policy

You can use a GPO to set the ExecutionPolicy to a static value on all machines.

But what if you want to default it to something and then let the users have the ability to change it?

Group Policy Preferences is the easy answer.

Create a GPO targeting your machines and then create a entry under Computer Configuration -> Preferences -> Windows Settings -> Registry that looks like this:
130917-execpolicy-1

130917-execpolicy-2

The paths are:
Key: SOFTWARE\Microsoft\PowerShell\1\ShellIds\Microsoft.PowerShell
Value Name: ExecutionPolicy
ValueData: RemoteSigned

With this setup a machine that doesn’t have any specific exec policy (then the reg key doesn’t exist) setup will get RemoteSigned.
(Another way is to do this with a startup script for the computer)

Calendar in Powershell CLI

Working on a nice way of creating informational background images… Stay tuned, it might be more posts on that later on… πŸ™‚

Anyway, started with a small snippet that creates a calendar in CLI

PARAM (
	[DateTime] $StartDate = (Get-Date)
)

$startDay = Get-Date (Get-Date $StartDate -Format "yyyy-MM-01")

Write-Host (Get-Date $startDate -Format "MMMM yyyy")
Write-Host "Mo Tu We Th Fr Sa Su"
For ($i=1; $i -lt (get-date $startDay).dayOfWeek.value__; $i++) {
	Write-Host "   " -noNewLine
}

$processDate = $startDay
while ($processDate -lt $startDay.AddMonths(1)) {
	Write-Host (Get-Date $processDate -Format "dd ") -NoNewLine
    
    if ((get-date $processDate).dayOfWeek.value__ -eq 0) { Write-Host "" }
	$processDate = $processDate.AddDays(1)
}
Write-Host ""

Will return something like this:

C:\snowland> .\CalendarCreator.ps1 -StartDate "2013-02-01"
february 2013
Mo Tu We Th Fr Sa Su
            01 02 03
04 05 06 07 08 09 10
11 12 13 14 15 16 17
18 19 20 21 22 23 24
25 26 27 28
C:\snowland>
Written by Comments Off on Calendar in Powershell CLI Posted in PowerShell

Bitlocker Info

I’m playing around with pipelining information to functions… and since I needed a function to read Bitlocker information from Active Directory, why not create one. πŸ™‚

Function Get-BitlockerInfo {
	PARAM (
		[Parameter(Mandatory=$true,Position=0,ValueFromPipeline=$true)] $Computer
	)

	BEGIN {
		$bitLockerInfo = @()
	}
	
	PROCESS {
		Write-Verbose "Searching $($Computer.DistinguishedName) ..."
		Get-ADObject -LdapFilter "(msFVE-Recoverypassword=*)" -Searchbase $Computer.DistinguishedName -properties msFVE-RecoveryPassword | ForEach-Object {
			$Bitlocker = $_.Name.Split("{")
			$retObj = New-Object -TypeName System.Object
			$retObj | add-Member -memberType NoteProperty -name ComputerDistinguishedName -Value $Computer.DistinguishedName
			$retObj | add-Member -memberType NoteProperty -name BitlockerTime -Value $Bitlocker[0]
			$retObj | add-Member -memberType NoteProperty -name PasswordID -Value $Bitlocker[1].Replace("}", "")
			$retObj | add-Member -memberType NoteProperty -name RecoveryPassword -Value $_."msFVE-RecoveryPassword"

			$bitLockerInfo += $retObj
		}
	}
	
	END {
		Return $bitLockerInfo
	}
}

# Here is how to use it
Get-AdComputer -LdapFilter "(name=WKS012*)" | Get-BitlockerInfo | Format-List

Change settings on all shares

We are enabling BranchCache on a few servers… and with that you probably want to change the settings on the shares you have.

Why not use some Powershell when you have the chance. πŸ™‚

Get-WmiObject -Class Win32_Share | Where-Object -FilterScript {$_.Name -notlike '?$' -and $_.Name -ne "ADMIN$" -and $_.Path -ne ""} | ForEach-Object {
	Write-Host "Updating $($_.Name) - $($_.Path)"
	$netShare = NET SHARE $_.Name /Cache:BranchCache
	
	If ($netShare -notlike "*completed successfully*") {
		Write-Host $netShare -ForegroundColor Red
	}
}

Change /Cache:BranchCache to wahtever you want to do with the share.

Oh, btw… this works fine to do remote with something like this:

Invoke-Command -ComputerName SOMEREMOTESERVER -scriptblock {
	Get-WmiObject -Class  ... and the same code here ...
}

Your Online/Offline state for a offline files enabled share

Want to know the online/offline state of a DFS-share that is in your offline files?

There is a WMI class called Win32_OfflineFilesConnectionInfo that you can use. Of course it’s not as easy as just querying that one…

You can do it one one compressed line…

((Get-WmiObject Win32_OfflineFilesItem -Filter "ItemType = 2 AND ItemPath = '\\\\SNOWLAND.SE\\MyRoot'").ConnectionInfo).ConnectState

Or a bit more elegant with a function…

Function Get-DfsRootConnectionState {
	PARAM (
		[string] $rootName,
		[switch] $asText
	)
	$ConnectStates = @("Unknown", "Offline", "Online")
	$OfflineReasons = @("Unknown", "Not applicable", "Working offline", "Slow connection", "Net disconnected", "Need to sync item", "Item suspended")

	$rootName = $rootName.Replace("\", "\\")
	$root = Get-WmiObject Win32_OfflineFilesItem -Filter "ItemType = 2 AND ItemPath = '$($rootName)'"

	$stateAsText = "$($ConnectStates[$root.ConnectionInfo.ConnectState]) - $($OfflineReasons[$root.ConnectionInfo.OfflineReason])"
	Write-Verbose $stateAsText
	
	If ($asText) {
		Return $stateAsText
	} else {
		Return $root.ConnectionInfo.ConnectState
	}
}

Get-DfsRootConnectionState -rootName "\\SNOWLAND.SE\MyRoot" -asText

User pictures in AD

Adding pictures to Active Directory is kind of nice, it gives you pictures in Outlook, Lync and few other products…

To add it you need to load a picture as binary and put it in to the attribute thumbnailPhoto on the user.

Function Add-AdThumbnailPhoto {
    PARAM (
        [ValidateScript({Test-Path $_ -PathType Leaf})] [string] $PicturePath,
        $UserAccount
    )
    
    If (!(Test-IsModuleLoaded "ActiveDirectory")) {
        Throw "You need to run: Import-Module ActiveDirectory"
    }
    Write-Verbose "Adding $($PicturePath) to $($UserAccount)"
    $pictureBinary = [byte[]](Get-Content $PicturePath -Encoding byte)
    
    If ([System.Text.Encoding]::ASCII.GetString($pictureBinary).Length -ge 100Kb) {
        Throw "Picture to large, max size is 100Kb"
    }
    
    
    Try {
        Set-AdUser $UserAccount -Replace @{ thumbnailPhoto = $pictureBinary }
    }
    Catch {
        Throw $error[0]
    }
}

Then you can use the function like this:

Add-AdThumbnailPhoto -PicturePath "C:\MyPicture.jpg" -UserAccount "MyUserAccount"

If you like to do it on a oneliner… here is a bit more compressed version of the same code:

Set-AdUser "MyUserAccount" -Replace @{ thumbnailPhoto = ([byte[]](Get-Content "C:\MyPicture.jpg" -Encoding byte) }

Load ConfigMgr 2012 Powershell Modules

If you want to load the modules for Configuration Manager 2012 outside of the Admin Console there are a few prereqs… You need a 32 Bit Powershell prompt and you need to run is at admin and of course you need the module.

When writing a script it’s probably a good idea to check that the user that runs the script have the prereqs fulfilled.

This is one way to do it:

# Check if you are a local admin
If ((New-Object Security.Principal.WindowsPrincipal ([Security.Principal.WindowsIdentity]::GetCurrent())).IsInRole([Security.Principal.WindowsBuiltInRole]::Administrator) -eq $false) {
	Throw "Need to run as Administrator"
}

# Check that youre not running X64
if ([Environment]::Is64BitProcess -eq $True) {
	Throw "Need to run at a X86 PowershellPrompt"
}

# Test the path and load ConfigMgr cmdlets
If ($Env:SMS_ADMIN_UI_PATH -eq $null) {
	Throw "Missing SMS_ADMIN_UI_PATH environment variable"
}
$cmModulePath = $Env:SMS_ADMIN_UI_PATH.Substring(0,$Env:SMS_ADMIN_UI_PATH.Length-5) + "\ConfigurationManager.psd1"
If ((Test-Path $cmModulePath) -eq $false) {
	Throw "Can't find $($cmModulePath)"
}
Write-Verbose "Loading ConfigMgr Modules"
Import-Module $cmModulePath

# Now you are good to go...
Get-Command -Module ConfigurationManager

# You probably need to set location to the site before you start to play around...
Set-Location "ABC:"

And here is the version i use in my profile to load the modules… A bit more compressed and without the Throw lines so I can use the same profile with or without the prereqs fulfilled.

If ( `
		((New-Object Security.Principal.WindowsPrincipal ([Security.Principal.WindowsIdentity]::GetCurrent())).IsInRole([Security.Principal.WindowsBuiltInRole]::Administrator) -eq $true) `
		-and `
		([Environment]::Is64BitProcess -eq $false) `
		-and `
		($Env:SMS_ADMIN_UI_PATH -ne $null)
	) {
	$cmModulePath = $Env:SMS_ADMIN_UI_PATH.Substring(0,$Env:SMS_ADMIN_UI_PATH.Length-5) + "\ConfigurationManager.psd1"
	If ((Test-Path $cmModulePath)) {
		Write-Verbose "Loading ConfigMgr Modules"
		Import-Module $cmModulePath
	}
}

Not sure where youre profile is located?

$profile | Format-List -Force
# Easy way to edit it:
PowerShell_ISE.exe $profile

Auto load command history

With these few lines in your Powershell Profile you will auto load the command history from the last session.

Run “notepad $profile” and add this:

Function Save-CommandHistory {
	$xmlPath = Join-Path (Split-Path $profile.CurrentUserAllHosts) "commandHistory.xml"
	Write-Verbose "Saving to $($xmlPath)"
	Get-History | Export-Clixml -Path $xmlPath
}

Function Load-CommandHistory {
	$xmlPath = Join-Path (Split-Path $profile.CurrentUserAllHosts) "commandHistory.xml"
	Write-Verbose "Loading from $($xmlPath)"
	Add-History -InputObject (Import-Clixml -Path $xmlPath)
}


Function Prompt {
	# Save the history
	Save-CommandHistory
	
	# Show a prompt
	Write-Host "PS $($PWD.Path.Replace('Microsoft.PowerShell.Core\FileSystem::', ''))>" -NoNewline
}

Write-Host "Loading command history..."
Load-CommandHistory

πŸ™‚

SQL in Powershell without cmdlets

I am currently playing around with ConfigMgr 2012 cmdlets… and they only run in X86 (*doh*).

Anyway, I need to query a database with information about some collections and other stuff I need to create… and running SQL Server cmdlets in X86 doesn’t work.

So to get this to work I found a Powershell function that uses SqlClient from .NET instead… I rewrote the function a bit and here is the result:

Function Run-SqlQuery {
	PARAM(
		[string] $Server = ".",
		[string] $Database = "master",
		[string] $Query,
		[Int32]  $QueryTimeout=60
	)
	$sqlConnection = New-Object System.Data.SqlClient.SQLConnection
	$sqlConnection.ConnectionString = "Data Source=$($Server);Initial Catalog=$($Database);Integrated Security=True;"
	$sqlConnection.Open()
	$sqlCommand = New-Object System.Data.SqlClient.SqlCommand($Query,$sqlConnection)
	$sqlCommand.CommandTimeout = $QueryTimeout
	$sqlDataSet = New-Object System.Data.DataSet
	$sqlDataAdapter = New-Object System.Data.SqlClient.SqlDataAdapter($sqlCommand)
	[void]$sqlDataAdapter.fill($sqlDataSet)
	$sqlConnection.Close()

	return $sqlDataSet.Tables[0]
}

Then you can use it like this:

Run-SqlQuery -Query "EXEC SP_HelpDB"
Run-SqlQuery -Query "SELECT Col1, Col2 FROM MyTable" -Database "MyDatabase" -Server "SomeServer" | Format-Table -AutoSize

This can probably be useful if you need to run a SQL command from a box where your’e not sure if you have SQL cmlets or not.

Logon Scripts in Powershell – Part4: Trigger ConfigMgr client actions

With the ConfigMgr agent in place on your client’s you probably want to force it to start some client actions on logon to speed up deployment of new applications.

This function will work with the ConfigMgr 2007 (on x86) and 2012 agent. (The reason for not working on a Win7 x64 and ConfigMgr 2007 is the lack of X64 support).

Function Run-ConfigMgrActions {
	<#
	.SYNOPSIS 
		Trigger ConfigMgr client actions
	#>
	PARAM (
		[string] $actionFilter1 = "Application Global Evaluation Task*",
		[string] $actionFilter2 = "Request ? Evaluate*"
	)

	Write-Verbose "Run-ConfigMgrActions: Start actions with filter '$($actionFilter1)' or '$($actionFilter2)'"
	TRY {
		(New-Object -ComObject CPApplet.cpAppletMgr).GetClientActions() | Where-Object {$_.Name -like $actionFilter1 -or $_.Name -like $actionFilter2} | Sort-Object Name | ForEach-Object {
			Write-Verbose "Run-ConfigMgrActions: Starting ConfigMgr action: $($_.Name)"
			$_.PerformAction()
		}
	}
	CATCH {
		Write-Verbose "Run-ConfigMgrActions: Can't find and/or trigger ConfigMgr Agent"
	}
}

Logon Scripts in Powershell – Part3: Storing and retrieving shares to map to/from AD

To keep you logon scripts static one way of storing information on who sould get what mapped is to use the Active Directory.

First of, talk to the AD-guys and “reserve” 3 attributes on groups (or extend the schema and add your own attributes). You need attributes for:
– Share Path
– Display Name
– Type of mapping

In the example I will use:
– displayName = Share Path
– displayNamePrintable = Display Name
– extensionAttribute3 = Type of mapping (I will use a 1 for “Network Location”, 0 for not in use and letters to map to a specific drive)

Then, populate the groups with information:

Import-Module ActiveDirectory

Get-Adgroup "MyGroupName" | Set-ADGroup -Replace @{
	DisplayName="\\SERVER\PathToMapAsNetworkLocation";
	DisplayNamePrintable="Some Description";
	extensionAttribute3=1
}
Get-Adgroup "SomeOtherGroupName" | Set-ADGroup -Replace @{
	DisplayName="\\SERVER\PathToMapAsDrive";
	DisplayNamePrintable="Some Description";
	extensionAttribute3=X
}

Get-Adgroup "ProjXDocs" | Set-ADGroup -Replace @{
	DisplayName="\\SERVER\Projects\ProjectXdocuments";
	DisplayNamePrintable="ProjectX Documents";
	extensionAttribute3=1
}

Then, retrieve the groups for the current user with a recursive LDAP-query.

Function Get-SharesToMap {
	<#
	.SYNOPSIS
		Read groups in AD for a user, then collect information to use when mapping disk
	#>
	PARAM (
		$UserDN = (Get-UserDN)
	)

	$mappedShares = @()
	$ldapFilter = "(&(member:1.2.840.113556.1.4.1941:=$($UserDN))(displayName=*)(extensionAttribute3=*)(!extensionAttribute3=0))"

	Run-LdapQuery -ldapFilter $ldapFilter | ForEach-Object {
		Write-Verbose "Get-SharesToMap: $($ssabMappedSharePath) - Mapping: $($ssabMappedShare)"
		$shareInfo = New-Object -TypeName System.Object
		$shareInfo | add-Member -memberType NoteProperty -name Group -Value $_.Properties.Item("cn")
		$shareInfo | add-Member -memberType NoteProperty -name Path -Value $_.Properties.Item('displayName')
		$shareInfo | add-Member -memberType NoteProperty -name DisplayName -Value $_.Properties.Item('displayNamePrintable')
		$shareInfo | add-Member -memberType NoteProperty -name MappedShare -Value $_.Properties.Item('extensionAttribute3')
		
		$mappedShares += $shareInfo
	}

	Return $mappedShares
}

Now you have a list of all shares to take care of…

Get-SharesToMap | Sort-Object Path | ForEach-Object {
	$Path        = $_.Path
	$MappedShare = $_.MappedShare
	$DisplayName = $_.DisplayName

	# Map UNC path once
	If ($lastMappedPath -ne $Path) {
		Switch -regex ($MappedShare) {
			0        { Write-Verbose "Loginscript:Main: Do not map UNC path $($UncPath)" }
			1        { Create-NethoodShortcut -LinkTarget $Path -LinkName $DisplayName -Description $DisplayName }
			"[A-Z]"  { Map-UncPathToDrive -UncPath $Path -DriveLetter $MappedShare }
			default  { Write-Verbose "Loginscript:Main: $($MappedShare) Do not map path $($Path)" }
		}
		$lastMappedPath = $Path
	}
}

With this in place, no one needs to edit the logonscript to change mappings. πŸ™‚

Sidenote 1:
To speed up the LDAP-queries add indexes on the attributes.
Start MMC
Add the snapin “Active Directory Schema”
Search for the attribute(s)
On the properties-page, check the box “Index this attribute”

Sidenote 2:
To make it easy to search the attributes, check the box “Ambigous Name Resolution (ANR)” in the properties page for the attribute
With ANR enabled you can do a LDAP-query like:
(ANR=SomeNameOfTheShare)
So, if someone wants to be member of the group for the share “ProjectX” just query like (ANR=ProjectX) and the group for that share will show up.