Powershell – Scripting

Collection of generic Powershell scripts that I use or have used.

SQL and Deadlocks – What, where and when are they exactly?

Working on for a customer where we were having some issues with some deadlocks. We couldn’t quite get the details on the events for a subset of reasons. Foremost, because the SQL Ring buffer events was recycled within one and a half hour during peak loads.
So we created a powershell script, which was set to run as a scheduled task, taking all deadlock events in the ring buffer and pairing it with relevant queries, one hour backwards.

Basically this script, run as a scheduled task every hour, will output all your deadlocks, for the past hour. If your system is not generating that much Ring buffer events, you can throttle it down or even just run the SQL query once in order to collect this data. Its just important to note, that these Ring buffer events will be overwritten by the good grace of the SQL server.

# Get todays date in order to stamp the output file correctly.
$Date = (Get-date).ToString("yyyy-MM-dd")

# The selected SQL instance to connect to
$SQLInstance = '<Servername>\<Instance>'
$Outputfile= "<Local folder location>"+$date+"Deadlocks.log"

# SQL Query to be performed
$SQLQuery = $("
  (SELECT xevent.query('(event/data/value/deadlock)[1]') AS DeadlockGraph,
          CAST (xevent.value('(event/@timestamp)[1]', 'nvarchar(30)') AS DATETIME) AS FullDate
     (SELECT xevent.query('.') AS XEvent
        (SELECT Cast(target_data AS XML) AS TargetData
         FROM sys.dm_xe_session_targets st
         JOIN sys.dm_xe_sessions s ON s.address = st.event_session_address
         WHERE s.NAME = 'system_health'
           AND st.target_name = 'ring_buffer') AS DATA CROSS apply targetdata.nodes ('RingBufferTarget/event[@name=""xml_deadlock_report""]') AS XEventData (xevent)) 
              AS src
WHERE RESULT.FullDate >= Dateadd(HOUR, -1, Getdate());

# Collect deadlock data every hour, using MaxCharLenght to get full output back. 
$DeadlockDataFromSQL=Invoke-Sqlcmd -Query $SQLQuery -ServerInstance $SQLInstance -MaxCharLength 10000 -QueryTimeout 0

# Output exception data to file using stream.
$stream = New-Object -TypeName System.IO.StreamWriter $OutPutFile, $true
foreach($line in $deadlockdataFROMSQL) 
      	$stream.WriteLine(($line.DeadlockGraph | Out-string))

Clean print servers of old jobs

Been working on some maintenance, cleanup, auditing, streamlining for a customer. During this workthrough, printjobs showed up and asking around they actually performed this task manually. Of course I would not hear of this and created a simple powershell script that would go through all print servers, look for jobs older than 7 days and delete them. All wrapped in standard remote powershell, so this can be run from a central batch server.
1: Make sure to add your printservers to the array.
2: This script requires remote powershell to work 🙂
3: You can change the number of days to reflect your own requirements.

$PrintServers = @("<PrintSRV01>","<PrintSRV02>", "<PrintSRV03>", "<PrintSRV04>")

Foreach($Printserver in $PrintServers) 
$Session = New-PSSession -Computername $PrintServer -Authentication Kerberos
$SC = { 
$OldJobs = get-wmiobject -class "Win32_PrintJob" -namespace "root\CIMV2" | Where-Object { $_.StartTime -lt $($(Get-Date).addDays(-7)) }
foreach ($job in $OldJobs) 

Invoke-Command -Session $Session -ScriptBlock $SC
Remove-PSSession $Session

Certificate Expiry – All Domain Servers – Powershell

This is a most lovely little piece of powershell.
“This script connects to the DC, gets a list of machines with the “OperatingSystem” value set to “*Server*”, if it can succesfully ping the machine(s), it will return a list of certificates that expire after the date specified and compile a CSV file of the results.”

Do check it out and give probs to the man producing the most awesome powershell and now finally with his own blog 🙂

Certificate Expiry

Disable unsafe ciphers and SSL 2.0/3.0 on your server

One step to improve the security on your servers, would be to disable SSL 2.0 and 3.0 as well as the unsafe ciphers RC4. This can be done using the following registry changes on your server.
Note: When you disable SSL 2.0 and 3.0 on your servers, clients will no longer be able to connect using that. See this post for additional reference.

RC4 Cipher

Windows Registry Editor Version 5.00
[HKEY_LOCAL_MACHINE\SYSTEM\CurrentControlSet\Control\SecurityProviders\SCHANNEL\Ciphers\RC4 128/128]
[HKEY_LOCAL_MACHINE\SYSTEM\CurrentControlSet\Control\SecurityProviders\SCHANNEL\Ciphers\RC4 40/128]
[HKEY_LOCAL_MACHINE\SYSTEM\CurrentControlSet\Control\SecurityProviders\SCHANNEL\Ciphers\RC4 56/128]

SSL 2.0 and 3.0

Windows Registry Editor Version 5.00
[HKey_Local_Machine\System\CurrentControlSet\Control\SecurityProviders\SCHANNEL\Protocols\SSL 3.0]
[HKey_Local_Machine\System\CurrentControlSet\Control\SecurityProviders\SCHANNEL\Protocols\SSL 2.0]


Windows 10 – Privacy settings – Automated

There has been quite some interest in the general privacy settings of the different applications and systems that we use in our daily life. Windows 10 is no exception to this, as Microsoft now by default, has chosen for you that you are very interested in sharing your traffic information with them. You can of course disable this tracking, however it is not always obvious how to do so. Once again my friend Mads Hjort Larsen has created a script to automate these privacy settings for you.

Have a look at the section “What should be changed” and make sure you change accordingly. ($false or $true)

Also note that once again WordPress has decided to mess with the formatting/syntax highlighting of the script. That is also why the third link in the comment section is surround by apostrophes.

    This script automates the changing of a lot of settings that are otherwise hard or tedious to change.
    This script consolidates a lot of Windows registry changes and changes to other operating system settings, 
    that affect the appearance and functionality of the Windows 10 operating system, with the intent of making
    it more userfriendly and increasing the privacy.
    Created by Mads Hjort Larsen
    email: mads.hjort.larsen@gmail.com

# What should be changed:
[bool]$privacySettings = $true  # change the settings related to privacy
[bool]$removeSoftware  = $false # remove Cortana, OneDrive and Metro Apps
[bool]$windowsUpdates  = $false # setup updates to NOT automatically reboot or download via P2P
[bool]$UIsettings      = $false # make win 10 look more like win 7/8/8.1

########### DO NOT EDIT BELOW THIS LINE #################################

# http://blogs.technet.com/b/heyscriptingguy/archive/2011/05/11/check-for-admin-credentials-in-a-powershell-script.aspx 
If (-NOT ([Security.Principal.WindowsPrincipal] [Security.Principal.WindowsIdentity]::GetCurrent()).IsInRole([Security.Principal.WindowsBuiltInRole] "Administrator")){
    Write-Warning "You do not have the Admin rights neccessary to run this script!`nPlease re-run this script as an Administrator!"

# http://webcache.googleusercontent.com/search?q=cache:FjmfLRRqNb4J:https://fortheloveofcode.wordpress.com/2008/06/08/what-no-hkcr-in-powershell/+&cd=1&hl=en&ct=clnk&gl=dk
New-PSDrive -Name HKCR -PSProvider Registry -Root HKEY_CLASSES_ROOT

if($privacySettings -eq $true){
# Disable Cortana
New-ItemProperty -Path 'HKLM:\SOFTWARE\Policies\Microsoft\Windows\Windows Search' -Name AllowCortana -PropertyType DWORD -Value 0 -ErrorAction SilentlyContinue

# Disable Data Logging Services
Get-Service diagtrack,dmwappushservice,RetailDemo | Stop-Service -PassThru | Set-Service -StartupType disabled
New-ItemProperty -Path HKLM:\SOFTWARE\Microsoft\Windows\CurrentVersion\Policies\DataCollection -Name AllowTelemetry -PropertyType DWORD -Value 0 -Force
New-ItemProperty -Path HKLM:\SOFTWARE\Wow6432Node\Microsoft\Windows\CurrentVersion\Policies\DataCollection -Name AllowTelemetry -PropertyType DWORD -Value 0 -Force

# Disable relevant scheduled tasks
schtasks /change /TN "\Microsoft\Windows\Application Experience\Microsoft Compatibility Appraiser" /DISABLE
schtasks /change /TN "\Microsoft\Windows\Application Experience\ProgramDataUpdater" /DISABLE
schtasks /change /TN "\Microsoft\Windows\Customer Experience Improvement Program\Consolidator" /DISABLE
schtasks /change /TN "\Microsoft\Windows\Customer Experience Improvement Program\KernelCeipTask" /DISABLE
schtasks /change /TN "\Microsoft\Windows\Customer Experience Improvement Program\UsbCeip" /DISABLE

# Erase the contents of AutoLogger-Diagtrack-Listener.etl
echo "" > C:\ProgramData\Microsoft\Diagnosis\ETLLogs\AutoLogger\AutoLogger-Diagtrack-Listener.etl

# Edit Hosts File
# http://www.dslreports.com/forum/r30222844-Stop-Windows-10-From-Spying-On-You-36-DNS-Addresses-to-host-file
$hostsPath = "$env:windir\System32\drivers\etc\hosts"
$hosts = get-content $hostsPath
[array]$urls = @(

foreach($url in $urls){
    $hostLine = ' '+$url

    if ($hosts -notcontains $hostLine){
        $newHosts = $hosts+$hostLine
        $newHosts | Out-File $hostsPath -Force

# WiFi Sense: HotSpot Sharing: Disable
Set-ItemProperty -Path HKLM:\Software\Microsoft\PolicyManager\default\WiFi\AllowWiFiHotSpotReporting -Name value -Type DWORD -Value 0

# WiFi Sense: Shared HotSpot Auto-Connect: Disable
Set-ItemProperty -Path HKLM:\Software\Microsoft\PolicyManager\default\WiFi\AllowAutoConnectToWiFiSenseHotspots -Name value -Type DWORD -Value 0

# Start Menu: Disable Bing Search Results
Set-ItemProperty -Path HKCU:\SOFTWARE\Microsoft\Windows\CurrentVersion\Search -Name BingSearchEnabled -Type DWORD -Value 0

# Privacy: Disable Edge suggestions
Set-ItemProperty -Path HKL:\SOFTWARE\Policies\Microsoft\MicrosoftEdge\SearchScopes -Name ShowSearchSuggestionsGlobal -Type DWORD -Value 0

# Privacy: Let apps use my advertising ID: Disable
Set-ItemProperty -Path HKCU:\SOFTWARE\Microsoft\Windows\CurrentVersion\AdvertisingInfo -Name Enabled -Type DWORD -Value 0

# Privacy: SmartScreen Filter for Store Apps: Disable
Set-ItemProperty -Path HKCU:\SOFTWARE\Microsoft\Windows\CurrentVersion\AppHost -Name EnableWebContentEvaluation -Type DWORD -Value 0

if($removeSoftware -eq $true){
# Uninstall OneDrive
Stop-Process -ProcessName *OneDrive*
if(Test-Path "$env:windir\SysWOW64\OneDriveSetup.exe" -eq $true){Start-Process "$env:windir\SysWOW64\OneDriveSetup.exe" -ArgumentList '/uninstall' -Wait}
else{Start-Process "$env:windir\System32\OneDriveSetup.exe" -ArgumentList '/uninstall' -Wait}
Stop-Process -ProcessName *OneDrive* 

Remove-Item "%USERPROFILE%\OneDrive" -Recurse -Force
Remove-Item "C:\OneDriveTemp" -Recurse -Force
Remove-Item "%LOCALAPPDATA%\Microsoft\OneDrive" -Recurse -Force
Remove-Item "%PROGRAMDATA%\Microsoft OneDrive" -Recurse -Force

# Remove Cortana
Get-Process -Name *cortana* | Stop-Process
Get-AppxPackage -AllUsers | ? {$_.Name -match 'Cortana'} | Remove-AppxPackage -ErrorAction SilentlyContinue

#region Windows 10 Metro App Removals
# Be gone, heathen!
Get-AppxPackage king.com.CandyCrushSaga | Remove-AppxPackage

# Bing Weather, News, Sports, and Finance (Money):
Get-AppxPackage Microsoft.BingWeather | Remove-AppxPackage
Get-AppxPackage Microsoft.BingNews | Remove-AppxPackage
Get-AppxPackage Microsoft.BingSports | Remove-AppxPackage
Get-AppxPackage Microsoft.BingFinance | Remove-AppxPackage

# Xbox:
Get-AppxPackage Microsoft.XboxApp | Remove-AppxPackage

# Windows Phone Companion
Get-AppxPackage Microsoft.WindowsPhone | Remove-AppxPackage

# Solitaire Collection
Get-AppxPackage Microsoft.MicrosoftSolitaireCollection | Remove-AppxPackage

# People
Get-AppxPackage Microsoft.People | Remove-AppxPackage

# Groove Music
Get-AppxPackage Microsoft.ZuneMusic | Remove-AppxPackage

# Movies & TV
Get-AppxPackage Microsoft.ZuneVideo | Remove-AppxPackage

# OneNote
Get-AppxPackage Microsoft.Office.OneNote | Remove-AppxPackage

# Photos
Get-AppxPackage Microsoft.Windows.Photos | Remove-AppxPackage

# Sound Recorder
Get-AppxPackage Microsoft.WindowsSoundRecorder | Remove-AppxPackage

# Mail & Calendar
Get-AppxPackage microsoft.windowscommunicationsapps | Remove-AppxPackage

# Skype (Metro version)
Get-AppxPackage Microsoft.SkypeApp | Remove-AppxPackage


if($windowsUpdates -eq $true){
# Change Windows Updates to "Notify to schedule restart"
# https://social.technet.microsoft.com/Forums/en-US/b8bf6607-99a0-441b-ab5f-f699ead7a56f/how-to-stop-windows-10-from-automatically-restarting?forum=WinPreview2014Feedback
Set-ItemProperty -Path 'HKLM:\SOFTWARE\Microsoft\Windows\CurrentVersion\WindowsUpdate\Auto Update' -Name AUOptions -Type DWORD -Value 4

# http://www.download3k.com/articles/How-to-Configure-Windows-Updates-in-Windows-10-01365
New-Item -Path HKLM:\SOFTWARE\Policies\Microsoft\Windows -Name WindowsUpdate
New-Item -Path HKLM:\SOFTWARE\Policies\Microsoft\Windows\WindowsUpdate -Name AU 
New-ItemProperty -Path HKLM:\SOFTWARE\Policies\Microsoft\Windows\WindowsUpdate\AU -Name AUOptions -Type DWORD -Value 4 -Force

# Disable P2P Update downloads outside of local network
Set-ItemProperty -Path HKLM:\SOFTWARE\Microsoft\Windows\CurrentVersion\DeliveryOptimization\Config -Name DODownloadMode -Type DWORD -Value 1
Set-ItemProperty -Path HKCU:\SOFTWARE\Microsoft\Windows\CurrentVersion\DeliveryOptimization -Name SystemSettingsDownloadMode -Type DWORD -Value 3

# To disable P2P update downloads completely:
#Set-ItemProperty -Path HKLM:\SOFTWARE\Microsoft\Windows\CurrentVersion\DeliveryOptimization\Config -Name DODownloadMode -Type DWORD -Value 0

if($UIsettings -eq $true){
# Change Explorer home screen back to "This PC"
Set-ItemProperty -Path HKCU:\SOFTWARE\Microsoft\Windows\CurrentVersion\Explorer\Advanced -Name LaunchTo -Type DWORD -Value 1

# Disable Quick Access: Recent Files
Set-ItemProperty -Path HKCU:\SOFTWARE\Microsoft\Windows\CurrentVersion\Explorer -Name ShowRecent -Type DWORD -Value 0

# Disable Quick Access: Frequent Folders
Set-ItemProperty -Path HKCU:\SOFTWARE\Microsoft\Windows\CurrentVersion\Explorer -Name ShowFrequent -Type DWORD -Value 0

# Disable the Lock Screen (the one before password prompt - to prevent dropping the first character)
If (-Not (Test-Path HKLM:\SOFTWARE\Policies\Microsoft\Windows\Personalization)) {New-Item -Path HKLM:\SOFTWARE\Policies\Microsoft\Windows -Name Personalization | Out-Null}
Set-ItemProperty -Path HKLM:\SOFTWARE\Policies\Microsoft\Windows\Personalization -Name NoLockScreen -Type DWORD -Value 1

# Use the Windows 7-8.1 Style Volume Mixer
If (-Not (Test-Path "HKLM:\SOFTWARE\Microsoft\Windows NT\CurrentVersion\MTCUVC")) {New-Item -Path "HKLM:\SOFTWARE\Microsoft\Windows NT\CurrentVersion" -Name MTCUVC | Out-Null}
Set-ItemProperty -Path "HKLM:\SOFTWARE\Microsoft\Windows NT\CurrentVersion\MTCUVC" -Name EnableMtcUvc -Type DWORD -Value 0

# Remove folders from MyPC
# https://pricklytech.wordpress.com/2013/10/17/windows-8-1-x64-removing-the-folders-from-file-explorer/
Remove-Item "HKLM:\SOFTWARE\Microsoft\Windows\CurrentVersion\Explorer\MyComputer\NameSpace\{1CF1260C-4DD0-4ebb-811F-33C572699FDE}" -Force # Music
Remove-Item "HKLM:\SOFTWARE\Microsoft\Windows\CurrentVersion\Explorer\MyComputer\NameSpace\{374DE290-123F-4565-9164-39C4925E467B}" -Force # Downloads
Remove-Item "HKLM:\SOFTWARE\Microsoft\Windows\CurrentVersion\Explorer\MyComputer\NameSpace\{3ADD1653-EB32-4cb0-BBD7-DFA0ABB5ACCA}" -Force # Pictures
Remove-Item "HKLM:\SOFTWARE\Microsoft\Windows\CurrentVersion\Explorer\MyComputer\NameSpace\{A0953C92-50DC-43bf-BE83-3742FED03C9C}" -Force # Videos
Remove-Item "HKLM:\SOFTWARE\Microsoft\Windows\CurrentVersion\Explorer\MyComputer\NameSpace\{A8CDFF1C-4878-43be-B5FD-F8091C1C60D0}" -Force # Documents

Remove-Item "HKLM:\SOFTWARE\Microsoft\Windows\CurrentVersion\Explorer\MyComputer\NameSpace\{088e3905-0323-4b02-9826-5d99428e115f}" -Force # Downloads 
Remove-Item "HKLM:\SOFTWARE\Microsoft\Windows\CurrentVersion\Explorer\MyComputer\NameSpace\{24ad3ad4-a569-4530-98e1-ab02f9417aa8}" -Force # Pictures
Remove-Item "HKLM:\SOFTWARE\Microsoft\Windows\CurrentVersion\Explorer\MyComputer\NameSpace\{3dfdf296-dbec-4fb4-81d1-6a3438bcf4de}" -Force # Music
Remove-Item "HKLM:\SOFTWARE\Microsoft\Windows\CurrentVersion\Explorer\MyComputer\NameSpace\{B4BFCC3A-DB2C-424C-B029-7FE99A87C641}" -Force # Desktop
Remove-Item "HKLM:\SOFTWARE\Microsoft\Windows\CurrentVersion\Explorer\MyComputer\NameSpace\{d3162b92-9365-467a-956b-92703aca08af}" -Force # Documents
Remove-Item "HKLM:\SOFTWARE\Microsoft\Windows\CurrentVersion\Explorer\MyComputer\NameSpace\{f86fa3ab-70d2-4fc7-9c99-fcbf05467f3a}" -Force # Videos
# Remove OneDrive from the Explorer Side Panel.
Remove-Item "HKCR:\CLSID\{018D5C66-4533-4307-9B53-224DE2ED1FE6}" -Force -Recurse
Remove-Item "HKCR:\Wow6432Node\CLSID\{018D5C66-4533-4307-9B53-224DE2ED1FE6}" -Force -Recurse

# Remove Compressed file/folder
Remove-Item "HKCR:\CABFolder\CLSID" -Force -Recurse
Remove-Item "HKCR:\CompressedFolder\CLSID" -Force -Recurse
Remove-Item "HKCR:\SystemFileAssociations\.cab\CLSID" -Force -Recurse
Remove-Item "HKCR:\SystemFileAssociations\.zip\CLSID" -Force -Recurse

# Explorer: Show all folders
New-ItemProperty -path HKCU:\Software\Microsoft\Windows\CurrentVersion\Explorer\Advanced -Name NavPaneShowAllFolders -PropertyType DWORD -Value 1

# Explorer: Show file extensions
New-ItemProperty -Path HKCU:\SOFTWARE\Microsoft\Windows\CurrentVersion\Explorer\Advanced -Name HideFileExt -PropertyType DWORD -Value 0

# Explorer: Show hidden files
New-ItemProperty -Path HKCU:\SOFTWARE\Microsoft\Windows\CurrentVersion\Explorer\Advanced -Name Hidden -PropertyType DWORD -Value 1

# Taskbar: Show all icons in taskbar
New-ItemProperty -Path HKLM:\Software\Microsoft\Windows\CurrentVersion\Explorer -Name EnableAutoTray -PropertyType DWORD -Value 0
New-ItemProperty -Path HKCU:\Software\Microsoft\Windows\CurrentVersion\Explorer\Advanced -Name NavPaneShowAllFolders -PropertyType DWORD -Value 1

# Taskbar: Hide Task View Button
New-ItemProperty -Path HKCU:\SOFTWARE\Microsoft\Windows\CurrentVersion\Explorer\Advanced -Name ShowTaskViewButton -PropertyType DWORD -Value 0

# Taskbar: Hide Search
New-ItemProperty -Path HKCU:\SOFTWARE\Microsoft\Windows\CurrentVersion\Search -Name SearchboxTaskbarMode -PropertyType DWORD -Value 0

# Bring back old Windows Update control panel app
# http://answers.microsoft.com/en-us/insider/forum/insider_wintp-insider_update/need-the-old-windows-update-not-the-new-windows/35bc83a7-3aa9-4408-b189-4aa2777e4e11
Set-ItemProperty -Path HKLM:\SOFTWARE\Microsoft\WindowsUpdate\UX -Name IsConvergedUpdateStackEnabled -Value 0 -Force
Set-ItemProperty -Path HKLM:\SOFTWARE\Microsoft\WindowsUpdate\UX\Settings -Name UxOption -Value 0 -Force

# Titlebar: Enable accent colors
# http://www.intowindows.com/how-to-change-title-bar-color-in-windows-10/ 
Copy-Item -Path $env:windir\Resources\Themes\aero -Recurse -Destination $env:windir\Resources\Themes\windows -Force -ErrorAction SilentlyContinue
Get-ChildItem -Path $env:windir\Resources\Themes\windows -Filter "aero.msstyles*" -Recurse | Rename-Item -NewName {$_.name -replace 'aero','windows' }
(Get-Content $env:windir\Resources\Themes\aero.theme).Replace('Path=%ResourceDir%\Themes\Aero\Aero.msstyles', 'Path=%ResourceDir%\Themes\windows\windows.msstyles') | Set-Content $env:TEMP\windows.theme
Start-Process $env:TEMP\windows.theme -Wait
(New-Object -comObject Shell.Application).Windows() | where-object {$_.LocationName -eq "Personalization"} | foreach-object {$_.quit()}
Remove-Item $env:TEMP\windows.theme

# Finishing touches
Write-Host "Please make sure you do the following as well:`nSelect Never in the first box, and Basic in the second box"
start ms-settings:privacy-feedback

Workflow Manager – Scripted Installation

Allrighty, I was sick and tired of NOT being able to install my Workflow Manager without the Web-platform Installer installation. Secondly I was tired of not being able to install Workflow Manager using my loved one, Powershell. So I called upon my good friend Mads Hjort Larsen and we started to work on it.
The script below will make use of offline msi files for the installation. So make sure you grab those before you run the script. They should be located in the same folder as the script before you run it.

The script

Set-Location (Split-Path -Parent -Path $MyInvocation.MyCommand.Definition)

#region Variables
$SQLserver = '<SQL SERVER>' # Name of the database server.
$DBprefix = '<Database Pre-fix>' # All databases will be prefixed with this string.

$SBRunAsAccount = '<Service Bus service account>' # The account under which the service runs. This account must be a domain account.
$SBRunAsPasswordString = '<Service Bus service Account passwprd>' # Password for RunAs account (in cleartext).
$SBAdminGroup = '<Service Bus Administrator group>' # The admin group for Service Bus. (Default "BUILTIN\Administrators")
$SBCertificateAutoGenerationKey = '<Certificate passphrase>' # This passphrase is required for certificate auto generation. This parameter is mandatory if you want certificates to be auto generated.
$TcpPort = <Service Bus port> # The port that the Service Bus for Windows Server uses for TCP. (Default 9354) 
$MessageBrokerPort = <Message Broker port> 9356 # The port that the Service Bus for Windows Server uses for MessageBroker communication. (Default 9356)
$InternalPortRangeStart = <Port Range> # The start of the port range that Service Bus for Windows Server uses for internal communication purposes. (Default 9000) 

$WFRunAsAccount = '<Service Bus service account>' # The account under which the service will be running. This account must be a domain account.
$WFRunAsPasswordString = '<Service Bus service account password>' # Password for RunAs account (in cleartext).
$WFAdminGroup = '<Service Bus Administrator group>' # The set of users who are considered workflow administrators. (Default BUILTIN\Administrators)
$WFCertificateAutoGenerationKey = '<Certificate Passphrase>' # This passphrase is required for certificate auto generation. This is a mandatory parameter if you want certificates to be auto generated.
$HttpsPort = <Workflow HTTPS Port> # The port that will be used by the workflow for HTTPS communication. (Default 12290)
$HttpPort = <Workflow HTTP Port> # The port that will be used by the workflow for HTTP communication. (Default 12291)

$SBNamespace = '<Service Bus Namespace>' # Specifies the name for the new Service Bus for Windows Server service namespace.
$AddressingScheme = 'Path' # Specifies the addressing scheme used in the service namespace. The possible values for this parameter are Path (default value) and DNSRegistered. If the value DNSRegistered is specified, the -DnsEntry parameter is required.
$ManageUsers = '<Workflow Namespace Administrator01>','<Workflow Namespace Administrator02>' # Specifies user or group names that will be managers of the service namespace.

$WF  = "WindowsFabric.msi"
$SB  = "Service_Bus.msi"
$WMC = "WorkflowManagerClient_x64.msi"
$WM  = "Workflow_Manager.msi"

## Can't touch this... or anything below this line ;P

$Sleep = 90 # Sleep duration is set to 90 seconds because that what all the cool kids online are doing.

    "IACCEPTEULA=yes" is required for quiet install of Windows Fabric, 
    "WEBPI=1" is required for installation of Service Bus and Workflow Manager outside of the Web Platform Installer, 
    "/QUIET" = Quiet mode, no user interaction, 
    "/NORESTART" = Do not restart after the installation is complete.

$fileNotFound = $null

$files = $WF,$SB,$WMC,$WM
foreach ($file in $files) {
    if (Test-Path -Path $file){
        Write-Host "✓ $file" -ForegroundColor Green
    }else{Write-Host "X $file" -ForegroundColor Red;$fileNotFound = $true}
}if ($fileNotFound -eq $true){Exit}
foreach ($file in $files) {
    Write-Host "Installing $file... " -NoNewline
    Start-Process $file -ArgumentList $ArgumentList -Wait
    Write-Host "Done"

[Environment]::SetEnvironmentVariable("PSModulePath", [Environment]::GetEnvironmentVariable("PSModulePath","Machine"))
Import-Module WorkflowManager

#region StringBuilding
# The strings are created like this to make it easier to put them on different SQL servers
function BuildString ($type, $dbName, $SQLserver) {
    New-Variable -Name $type'DBConnectionStringDataSource' -Value $SQLserver
    New-Variable -Name $type'DBConnectionStringInitialCatalog' -Value $DBprefix$dbName
    New-Variable -Name $type'DBConnectionString' -Scope Script -Value ("Data Source="+(Get-Variable -Name $type'DBConnectionStringDataSource' -ValueOnly)+";Initial Catalog="+(Get-Variable -Name $type'DBConnectionStringInitialCatalog' -ValueOnly)+";Integrated Security=True;Encrypt=False") -Force

# Example ConnectionString: Data Source=SQL01;Initial Catalog=Udv_service_SBManagement;Integrated Security=True;Encrypt=False
BuildString SBFarm SBManagement $SQLserver
BuildString GateWay SBGateway $SQLserver
BuildString MessageContainer SBMessageContainer $SQLserver
BuildString WFFarm WFManagement $SQLserver
BuildString Instance WFInstanceManagement $SQLserver
BuildString Resource WFResourceManagement $SQLserver

$SBCertificateAutoGenerationKey = ConvertTo-SecureString -AsPlainText -Force -String $SBCertificateAutoGenerationKey -Verbose
New-SBFarm -SBFarmDBConnectionString $SBFarmDBConnectionString -InternalPortRangeStart $InternalPortRangeStart -TcpPort $TcpPort -MessageBrokerPort $MessageBrokerPort -RunAsAccount $SBRunAsAccount -AdminGroup $SBAdminGroup -GatewayDBConnectionString $GatewayDBConnectionString -CertificateAutoGenerationKey $SBCertificateAutoGenerationKey -MessageContainerDBConnectionString $MessageContainerDBConnectionString -Verbose
New-WFFarm -WFFarmDBConnectionString $WFFarmDBConnectionString -RunAsAccount $WFRunAsAccount -AdminGroup $WFAdminGroup -HttpsPort $HttpsPort -HttpPort $HttpPort -InstanceDBConnectionString $InstanceDBConnectionString -ResourceDBConnectionString $ResourceDBConnectionString -CertificateAutoGenerationKey $SBCertificateAutoGenerationKey -Verbose

$SBRunAsPassword = ConvertTo-SecureString -AsPlainText -Force -String $SBRunAsPasswordString -Verbose
Add-SBHost -SBFarmDBConnectionString $SBFarmDBConnectionString -RunAsPassword $SBRunAsPassword -EnableFirewallRules $true -CertificateAutoGenerationKey $SBCertificateAutoGenerationKey -Verbose

Try {
    New-SBNamespace -Name $SBNamespace -AddressingScheme $AddressingScheme -ManageUsers $ManageUsers -Verbose
    for ($i=$Sleep; $i -gt 1; $i--) {
        Write-Progress -Activity "Creating new SB Namespace" -Status "Sleeping" -SecondsRemaining $i
        Start-Sleep 1
Catch [system.InvalidOperationException]{}

$SBClientConfiguration = Get-SBClientConfiguration -Namespaces $SBNamespace -Verbose
$WFRunAsPassword = ConvertTo-SecureString -AsPlainText -Force -String $WFRunAsPasswordString -Verbose
Add-WFHost -WFFarmDBConnectionString $WFFarmDBConnectionString -RunAsPassword $WFRunAsPassword -EnableFirewallRules $true -SBClientConfiguration $SBClientConfiguration -CertificateAutoGenerationKey (ConvertTo-SecureString $WFCertificateAutoGenerationKey -AsPlainText -Force) -Verbose

(The script is currently using a self-signed certificate.)

How to get MSI files
WebPICMD.exe /offline /products:”WindowsFabric,ServiceBus_1_1,WorkflowManager” /Path:c:\temp\

WebPICMD.exe /offline /products:WindowsFabric /Path:c:\temp\

WebPICMD.exe /offline /Products:ServiceBus_1_1 /Path:c:\temp\

WebPICMD.exe /offline /Products:WorkflowManager /Path:c:\temp\

WebPICMD.exe /offline /Products:WorkflowManager /Path:c:\temp\

Updated 17-June-2015: Incorporated Martin Sandersens comment into the script, removing the need for customized msi files.
Updated 07-August-2015: Updated script for fewer lines of code.

SharePoint – ConfigDB – Growing

Alright, so one of my developers was complaining that his config database on his development environment was more than 40GB(If your config database is larger than 10GB, you should continue reading), or actually it was the sysadmin that complained, but my developer was targeted. He asked if I could have a look at it. Sure of course, properly transaction logs gone wild…. So logging on the server, finding the trans logs were all good. All right further digging came up with the TimerJobHistory table being filled with around 100 million records. Alright, now what?

Some posts have some good Powershell scripts, that will incrementally delete your timerjob history, maybe also change your retention time. Sure I started to run those, but that didn’t quite do the trick. So what was wrong then?

Alright here is the deal. The “Delete Job History” job, has a timeout of 5 minutes. That means it will delete records, but if the delete is not completed within 5 minutes, it throws a timeout. It actually fails the timer job. (Timeout) This timer job is set to run only once a week. So guess what happens when you create more timer jobs per week than the Delete Timer job can remove in 5 minutes? – Yup, the TimerJobHistory table will grow…

I found the easiest fix, just to change the schedule for the “Delete Job History”. For the 100 million records, I changed it to every 7’th minute. I know that it will not run longer than 5 minutes, so a 2 minutes slack should be sufficient. So what should the setting be? Adjust according to your environment.

SharePoint ConfifDB growing beyond the normal 4-8GB. The data file, not the log file.

Root cause
The Delete Job History cannot delete more rows than created timer jobs per week within the 5 minute timeout. This can be related to heavy deployment or insufficient resources.

Change the schedule of the Delete Job History to run more frequently.

Get-SPTimerJob job-Delete-Job-History | Set-SPTimerJob -Schedule “daily at 05:00:00”

Which interval is required depends on your environment or the amount of rows in backlog. So set schedule accordingly and following the types for Set-SPTimerJob.
The type must be a valid SharePoint Timer service (SPTimer) schedule in the form of any one of the following schedules:
– Every 5 minutes between 0 and 59
– Hourly between 0 and 59
– Daily at 15:00:00
– Weekly between Fri 22:00:00 and Sun 06:00:00
– Monthly at 15 15:00:00
– Yearly at Jan 1 15:00:00

IIS – Authentication Settings – Powershell

Doing my work I had to setup some Kerberos authentication for an IIS site. The customer and the developer wanted it done using Powershell. I’m not all too impressed by the IIS powershell, it reminds me of WMI and I did not like that either.
Set that aside, I actually had a hard time finding something simple to get it to work.
All the below lines needs is the sitename, found from the IIS manager.

$SiteName = ‘’ #Example: 'Default Web Site'
$PSPath = “IIS:\Sites\$SiteName”

#-- ASP.NET Impersontation enable
Set-WebConfigurationProperty –filter system.web/identity –Name impersonate –Value True –PSPath $PSPath

#-- Windows Authentication enable
Set-WebConfigurationProperty –filter /system.webServer/security/authentication/WindowsAuthentication –Name enabled –value True –PSPath $PSPath