Tag Archives: powershell

Just enough Administration & RDS

Reblogged from my company blog: https://tech.xenit.se/just-enough-administration-rds/

The Problem?

Microsoft RDS has limitations when delegating access, in fact there is no built-in access delegation whatsoever!

The solution? Powershell!

A Just enough Administration (JEA) endpoint, also known as a Constrained Powershell Endpoint.
The endpoint is restricted to only access List collection, list users and subsequently force logoff users. The endpoint configuration to only accept users from a certain AD group.

To configure endpoint, run the following code on all connection brokers. The script is somewhat generalized for easy adaptability. The main parts are in the parameters where we configure who can run, using which account and also what cmdlet are available in the constrained session.

<#
.Synopsis
    Short description
.DESCRIPTION
    
.NOTES
    Name: Create-JEA-RDS-Endpoint.ps1
    Author: Vikingur Saemundsson
    Date Created: 2017-11-20
    Version History:
        2017-11-20 - Vikingur Saemundsson
            Initial Creation

    Xenit AB
#>

[cmdletbinding()]
Param(
    [String]$Name = 'Xenit.JEA.RDS.Servicedesk',
    [String]$Path = 'C:\Xenit\RDS\LogoffEndpoint',
    [Array]$VisibleCmdlets = $("Get-RDSessionCollection","Get-RDSessionHost","Get-RDUserSession","Invoke-RDUserLogoff","Where-object"),
    [String]$AdGroup = 'Servicedesk Ad group',
    [Parameter(Mandatory=$true)]
    [String]$ServiceAccount,
    [Array]$Modules = 'RemoteDesktop',
    [String]$Author = 'Vikingur Saemundsson',
    [String]$Company = 'Xenit AB'
)
Try{
    $ADGroupSID = (Get-ADGroup $AdGroup).SID

    If(-not(Test-Path -Path $Path -PathType Container)){
        New-Item -Path $Path -ItemType Directory -Force
    }

    $LockdownScript = @'
    Get-Command | Where Visibility -eq 'Public' | ForEach-Object {
        if ( $_.Name -notin $CmdsToExclude ) {
            $_.Visibility = 'Private'
        }
    }
'@ 
    '$CmdsToExclude = @("Get-Command", "Out-Default", "Exit-PSSession", "Measure-Object", "Select-Object" , "Get-FormatData"{0})' -f (($VisibleCmdlets | ForEach-Object{",`"$_`""}) -join '') | Out-File "$Path\LockdownScript.ps1"
    $LockdownScript | Out-File "$Path\LockdownScript.ps1" -Append

    $Modules += 'Microsoft.PowerShell.Core'
    $Modules += 'Microsoft.PowerShell.Management'
    $Modules += 'Microsoft.PowerShell.Security'
    $Modules += 'Microsoft.PowerShell.Utility'
    $Modules += 'Microsoft.PowerShell.Diagnostics'
    $Modules += 'Microsoft.PowerShell.Host'

    $ConfigFileParams = @{
        Path ="$Path\$Name.pssc"
        ModulesToImport = $Modules
        ScriptsToProcess = "$Path\LockdownScript.ps1"
        CompanyName = $Company
        Author = $Author
        Copyright = "(c) $((Get-Date).Year) $Company. All rights reserved."
        SessionType = 'Default'
        LanguageMode = "ConstrainedLanguage"
        ExecutionPolicy = 'Bypass'
    }

    $SessionConfigParams = @{
        RunAsCredential = $ServiceAccount
        Name = $Name
        Path = $ConfigFileParams.Path
        UseSharedProcess = $true
        SecurityDescriptorSddl = "O:BAG:DUD:AI(A;;GX;;;$ADGroupSID)"
        Confirm = $false
    }

    If(Get-PSSessionConfiguration -Name $SessionConfigParams.Name -ErrorAction SilentlyContinue){
        Unregister-PSSessionConfiguration -Name $SessionConfigParams.Name -Confirm:$false
    }

    New-PSSessionConfigurationFile @ConfigFileParams

    Register-PSSessionConfiguration @SessionConfigParams
}
Catch{
    Write-Error $_
}

The frontend application code is not posted in the blog.

Recursively search Azure Ad group members

Reblogged from my company blog: https://tech.xenit.se/recursively-search-azure-ad-group-members/

When working with on-premise Active Directory an administrator often has to recursively search AD groups, this is easy using the ActiveDirectory module with cmdlet “Get-AdGroupMember <Group> -Recusive”.
For the AzureAD equivalent this is no longer an option, the cmdlet Get-AzureADGroupMember has three parameters.

PARAMETERS
-All <Boolean>
If true, return all group members. If false, return the number of objects specified by the Top parameter
-ObjectId <String>
Specifies the ID of a group in Azure AD.
-Top <Int32>
Specifies the maximum number of records to return.

As we can see there is no -recursive, in order to search recursively I’ve written the function below.

Function Get-RecursiveAzureAdGroupMemberUsers{
[cmdletbinding()]
param(
   [parameter(Mandatory=$True,ValueFromPipeline=$true)]
   $AzureGroup
)
    Begin{
        If(-not(Get-AzureADCurrentSessionInfo)){Connect-AzureAD}
    }
    Process {
        Write-Verbose -Message "Enumerating $($AzureGroup.DisplayName)"
        $Members = Get-AzureADGroupMember -ObjectId $AzureGroup.ObjectId -All $true
        
        $UserMembers = $Members | Where-Object{$_.ObjectType -eq 'User'}
        If($Members | Where-Object{$_.ObjectType -eq 'Group'}){
            $UserMembers += $Members | Where-Object{$_.ObjectType -eq 'Group'} | ForEach-Object{ Get-RecursiveAzureAdGroupMemberUsers -AzureGroup $_}
        }
    }
    end {
        Return $UserMembers
    }
}

The function accepts groups by parameter or by pipeline and returns only the object type ‘User’

To run a recursive AzureAD Group member search simply pipe a normal ADgroup search as below

Get-AzureADGroup -SearchString 'AzureADGroupName' | Get-RecursiveAzureAdGroupMemberUsers

Azure Automation – Running scripts locally on VM through runbooks

Reblogged from my company blog: https://tech.xenit.se/azure-automation-running-scripts-locally-vm-runbooks/

I was tasked to create a powershell script to run on a schedule on a Azure VM. Normally this would be running as a scheduled task on the VM but seeing as we’re working with AzureVM and schedule tasks are legacy I wanted to explore the possibilities of running the schedule and script in Azure to keep the VM clean and the configuration scalable.

After some research the best option would be running the powershell script as a CustomScriptExtension on the VM, and the schedule would be handled by a Process Automation Runbook (using Automation Accounts).

What I ended up with is the script below. It’s fairly easy to configure and contains almost all the required configuration in the parameters.

How does it work? Simple!

Prerequisites

  1. Create a Storage Account
    • Create a Private Blob container
  2. Create a Automation Account
    • Make sure a RunAsAccount is created

Runbook configuration

  1. Navigate to the Automation Account you intend to use
  2. Create a Powershell runbook and press Edit
  3. Copy the below script into the runbook and save
    • Fill out the parameters with relevant information (subscription, resourcegroup etc)
    • Make sure to set the name of the StorageContainer to the one where you want to host the scripts
    • Extension name should be unique for the job, as well as the ScriptName
    • The scriptblock parameter takes the script you intend to run on the VMs
  4. On the first run, go into the Test Pane from edit view and edit the UploadScript parameter to $True
    • This will make the runbook actually save the script to the Container, allowing the VM download and run the script
  5. Done!

Now simply register schedules to the runbook. If you want to run the script on several VM you have two options. Either specify multiple VM when creating schedules or create a schedule per machine you want to run the script against.

[cmdletbinding()]
Param(
    $SubscriptionID = '<SubscriptionID>',
    $ResourceGroup = 'RG-NorthEU',
    $VMNames = @('vmname1','vmname2'),
    $StorageAccount = '<StorageAccount>',
    $StorageAccountKey = '<Storagekey>',
    $StorageContainer = 'sc-scripts', # Create this manually before first execution!
    $ExtensionName = 'Xenit.<job>',
    $ScriptName = 'scriptname.ps1',
    
    $UploadScript = $false,
    $ScriptBlock = {
        <Script to run locally on VMs>
    }
)


################
################
#
# DO NOT CHANGE BELOW
#
################
################

function Invoke-AzureRmVmScript {
<#
    
    # Vikingur Saemundsson @ Xenit AB
    # Credit to source https://github.com/RamblingCookieMonster/PowerShell/blob/master/Invoke-AzureRmVmScript.ps1
    # Made som small modifications to allow control over if the scriptfile is uploaded or not to avoid extra data to containers and shorten the executiontime
    #

    .FUNCTIONALITY
        Azure
#>
    [cmdletbinding()]
    param(
        # todo: add various parameter niceties
        [Parameter(Mandatory = $True,
                    Position = 0,
                    ValueFromPipelineByPropertyName = $True)]
        [string[]]$ResourceGroupName,
        
        [Parameter(Mandatory = $True,
                    Position = 1,
                    ValueFromPipelineByPropertyName = $True)]
        [string[]]$VMName,
        
        [Parameter(Mandatory = $True,
                    Position = 2)]
        [scriptblock]$ScriptBlock, #todo: add file support.
        
        [Parameter(Mandatory = $True,
                    Position = 3)]
        [string]$StorageAccountName,

        [string]$StorageAccountKey, #Maybe don't use string...

        $StorageContext,
        
        [string]$StorageContainer = 'sc-scripts',
        
        [string]$Filename, # Auto defined if not specified...
        
        [string]$ExtensionName, # Auto defined if not specified

        [bool]$UploadScript = $true,

        [switch]$ForceExtension,
        [switch]$ForceBlob,
        [switch]$Force
    )
    begin
    {
        if($Force)
        {
            $ForceExtension = $True
            $ForceBlob = $True
        }
    }
    process
    {
        Foreach($ResourceGroup in $ResourceGroupName)
        {
            Foreach($VM in $VMName)
            {
                if(-not $Filename)
                {
                    $GUID = [GUID]::NewGuid().Guid -replace "-", "_"
                    $FileName = "$GUID.ps1"
                }
                if(-not $ExtensionName)
                {
                    $ExtensionName = $Filename -replace '.ps1', ''
                }

                $CommonParams = @{
                    ResourceGroupName = $ResourceGroup
                    VMName = $VM
                }

                Write-Verbose "Working with ResourceGroup $ResourceGroup, VM $VM"
                # Why would Get-AzureRMVmCustomScriptExtension support listing extensions regardless of name? /grumble
                Try
                {
                    $AzureRmVM = Get-AzureRmVM @CommonParams -ErrorAction Stop
                    $AzureRmVMExtended = Get-AzureRmVM @CommonParams -Status -ErrorAction Stop
                }
                Catch
                {
                    Write-Error $_
                    Write-Error "Failed to retrieve existing extension data for $VM"
                    continue
                }

                # Handle existing extensions
                Write-Verbose "Checking for existing extensions on VM '$VM' in resource group '$ResourceGroup'"
                $Extensions = $null
                $Extensions = @( $AzureRmVMExtended.Extensions | Where {$_.Type -like 'Microsoft.Compute.CustomScriptExtension'} )
                if($Extensions.count -gt 0)
                {
                    Write-Verbose "Found extensions on $VM`:`n$($Extensions | Format-List | Out-String)"
                    if(-not $ForceExtension)
                    {
                        Write-Warning "Found CustomScriptExtension '$($Extensions.Name)' on VM '$VM' in Resource Group '$ResourceGroup'.`n Use -ForceExtension or -Force to remove this"
                        continue
                    }
                    Try
                    {
                        # Theoretically can only be one, so... no looping, just remove.
                        $Output = Remove-AzureRmVMCustomScriptExtension @CommonParams -Name $Extensions.Name -Force -ErrorAction Stop
                        if($Output.StatusCode -notlike 'OK')
                        {
                            Throw "Remove-AzureRmVMCustomScriptExtension output seems off:`n$($Output | Format-List | Out-String)"
                        }
                    }
                    Catch
                    {
                        Write-Error $_
                        Write-Error "Failed to remove existing extension $($Extensions.Name) for VM '$VM' in ResourceGroup '$ResourceGroup'"
                        continue
                    }
                }
                
                if(-not $StorageContainer)
                {
                    $StorageContainer = 'scripts'
                }
                if(-not $Filename)
                {
                    $Filename = 'CustomScriptExtension.ps1'
                }
                if(-not $StorageContext)
                {
                    if(-not $StorageAccountKey)
                    {
                        Try
                        {
                            $StorageAccountKey = (Get-AzureRmStorageAccountKey -ResourceGroupName $ResourceGroup -Name $storageAccountName -ErrorAction Stop)[0].value
                        }
                        Catch
                        {
                            Write-Error $_
                            Write-Error "Failed to obtain Storage Account Key for storage account '$StorageAccountName' in Resource Group '$ResourceGroup' for VM '$VM'"
                            continue
                        }
                    }
                    Try
                    {
                        $StorageContext = New-AzureStorageContext -StorageAccountName $StorageAccountName -StorageAccountKey $StorageAccountKey
                    }
                    Catch
                    {
                        Write-Error $_
                        Write-Error "Failed to generate storage context for storage account '$StorageAccountName' in Resource Group '$ResourceGroup' for VM '$VM'"
                        continue
                    }
                }
                If($UploadScript){
                    Write-Verbose "Uploading script to storage account $StorageAccountName"
                    Try
                    {
                        $Script = $ScriptBlock.ToString()
                        $LocalFile = [System.IO.Path]::GetTempFileName()
                        Start-Sleep -Milliseconds 500 #This might not be needed
                        Set-Content $LocalFile -Value $Script -ErrorAction Stop
            
                        $params = @{
                            Container = $StorageContainer
                            Context = $StorageContext
                        }

                        $Existing = $Null
                        $Existing = @( Get-AzureStorageBlob @params -ErrorAction Stop )

                        if($Existing.Name -contains $Filename -and -not $ForceBlob)
                        {
                            Write-Warning "Found blob '$FileName' in container '$StorageContainer'.`n Use -ForceBlob or -Force to overwrite this"
                            continue
                        }
                        $Output = Set-AzureStorageBlobContent @params -File $Localfile -Blob $Filename -ErrorAction Stop -Force
                        if($Output.Name -notlike $Filename)
                        {
                            Throw "Set-AzureStorageBlobContent output seems off:`n$($Output | Format-List | Out-String)"
                        }
                    }
                    Catch
                    {
                        Write-Error $_
                        Write-Error "Failed to generate or upload local script for VM '$VM' in Resource Group '$ResourceGroup'"
                        continue
                    }
                }

                # We have a script in place, set up an extension!
                Write-Verbose "Adding CustomScriptExtension to VM '$VM' in resource group '$ResourceGroup'"
                Try
                {
                    $Output = Set-AzureRmVMCustomScriptExtension -ResourceGroupName $ResourceGroup `
                                                                    -VMName $VM `
                                                                    -Location $AzureRmVM.Location `
                                                                    -FileName $Filename `
                                                                    -Run $Filename `
                                                                    -ContainerName $StorageContainer `
                                                                    -StorageAccountName $StorageAccountName `
                                                                    -StorageAccountKey $StorageAccountKey `
                                                                    -Name $ExtensionName `
                                                                    -TypeHandlerVersion 1.1 `
                                                                    -ErrorAction Stop

                    if($Output.StatusCode -notlike 'OK')
                    {
                        Throw "Set-AzureRmVMCustomScriptExtension output seems off:`n$($Output | Format-List | Out-String)"
                    }
                }
                Catch
                {
                    Write-Error $_
                    Write-Error "Failed to set CustomScriptExtension for VM '$VM' in resource group $ResourceGroup"
                    continue
                }

                # collect the output!
                Try
                {
                    $AzureRmVmOutput = $null
                    $AzureRmVmOutput = Get-AzureRmVM @CommonParams -Status -ErrorAction Stop
                    $SubStatuses = ($AzureRmVmOutput.Extensions | Where {$_.name -like $ExtensionName} ).substatuses
                }
                Catch
                {
                    Write-Error $_
                    Write-Error "Failed to retrieve script output data for $VM"
                    continue
                }

                $Output = [ordered]@{
                    ResourceGroupName = $ResourceGroup
                    VMName = $VM
                    Substatuses = $SubStatuses
                }

                foreach($Substatus in $SubStatuses)
                {
                    $ThisCode = $Substatus.Code -replace 'ComponentStatus/', '' -replace '/', '_'
                    $Output.add($ThisCode, $Substatus.Message)
                }

                [pscustomobject]$Output
            }
        }
    }
}
Try{
    #region Connection to Azure
    write-verbose "Connecting to Azure"
    $connectionName = "AzureRunAsConnection"

    try
    {
        # Get the connection "AzureRunAsConnection "
        $servicePrincipalConnection = Get-AutomationConnection -Name $connectionName         

        "Logging in to Azure..."
        Add-AzureRmAccount `
            -ServicePrincipal `
            -TenantId $servicePrincipalConnection.TenantId `
            -ApplicationId $servicePrincipalConnection.ApplicationId `
            -CertificateThumbprint $servicePrincipalConnection.CertificateThumbprint 
    }
    catch {
        if (!$servicePrincipalConnection)
        {
            $ErrorMessage = "Connection $connectionName not found."
            throw $ErrorMessage
        } else{
            Write-Error -Message $_.Exception.Message
            throw $_.Exception
        }
    }

    Select-AzureRmSubscription -SubscriptionId $SubscriptionID
    $RG = Get-AzureRmResourceGroup -Name $ResourceGroup
    Foreach($VMName in $VMNames){
        $AzureVM = Get-AzureRmVM -Name $VMName -ResourceGroupName $RG.ResourceGroupName
    
        $Params = @{
            ResourceGroupName = $ResourceGroup
            VMName = $AzureVM.Name
            StorageAccountName = $StorageAccount
            StorageAccountKey = $StorageAccountKey
            StorageContainer = $StorageContainer
            FileName = $ScriptName
            ExtensionName = $ExtensionName
            UploadScript = $UploadScript
        }

        Invoke-AzureRmVmScript @Params -ScriptBlock $ScriptBlock -Force -Verbose
    }
}
Catch{
    Write-Error -Message $_.Exception.Message
    Exit Throw $_
}

Generate-Key

To compliment my earlier post I have created a short function to generate keys.

Function Generate-key{
Param(
    [validateset(128,192,256)]
    $bits,
    [switch]$COUT,
    $seed
)
    $bytes = $bits/8
    $array = @()
    If($seed){
        1..$bytes | ForEach-Object{
            $array += Get-Random -Minimum 0 -Maximum 255 -SetSeed $seed
        }
    }
    Else{
        1..$bytes | ForEach-Object{
            $array += Get-Random -Minimum 0 -Maximum 255
        }
    }
    If($COUT){
        [string]$retval = ($array -join ',') 
        Return $retval
    }
    Else{
        Return [Byte[]]$Key = $array
    }
}

Encrypting strings with custom keys in powershell

Since the ConvertTo-SecureString is not really secure, and neither is the EncodedCommand (base64string) I made two short functions to encrypt and decrypt strings in order to send them across the void unharmed.

Function Encrypt-String{
Param(
    
    [Parameter(
        Mandatory=$True,
        Position=0,
        ValueFromPipeLine=$true
    )]
    [Alias("String")]
    [String]$PlainTextString,
    
    [Parameter(
        Mandatory=$True,
        Position=1
    )]
    [Alias("Key")]
    [byte[]]$EncryptionKey
)
    Try{
        $secureString = Convertto-SecureString $PlainTextString -AsPlainText -Force
        $EncryptedString = ConvertFrom-SecureString -SecureString $secureString -Key $EncryptionKey

        return $EncryptedString
    }
    Catch{Throw $_}

}

this function accepts a string, a key and outputs the encryptedstring.

To encrypt call the function with a plaintext string and a key. the key must be a byte array with 16,24 or 32 bytes (128,192 or 256 bits)

Calling the function looks like this.

$string = @'
I am about to become encrypted!
'@

[Byte[]]$Key = 117,9,103,192,133,20,53,149,81,95,108,34,81,224,226,220,56,68,133,120,139,241,176,239,171,54,231,205,83,57,51,255

$EncryptedString = Encrypt-String -PlainTextString $string -Key $Key

The output becomes

PS> $encryptedString
76492d1116743f0423413b16050a5345MgB8AG8AVABnAGcAagArAFYAUgBoAGkAYwBOAFIAZgBTAGEAQQB1AGsAMQBmAHcAPQA9
AHwAZQBiADIAOAA4ADQAYwA5ADEAMABlAGMAOABmADUAOAAyAGIANQAzADIAOABjADIAOAAxADUAZgAxADYAMgAyAGQAMgA2AGUA
ZQA2ADYANAA2AGUAYQA0AGMAMgBmADMAYgAwADIAMgAxAGQAOQAxADcANwBhADgANwAxADcAZgBjADEANwAxADcANwA1ADgANwBh
ADMAMgA3AGEAMABmADcAYQAzADcAYQBiADgANAAwADkAOQA4ADgANgA1AGYAMwA5ADMANAA3ADkAZgAwAGIAZAA1AGQAZgA2AGEA
MABmADIANwBmADkANQBhAGYAOAAxAGYANgA0ADAAOAAxAA==

Now to the decrypt. This function needs the exact encryptedstring and the exact key that the string was encoded with.

Function Decrypt-String{
Param(
    [Parameter(
        Mandatory=$True,
        Position=0,
        ValueFromPipeLine=$true
    )]
    [Alias("String")]
    [String]$EncryptedString,

    [Parameter(
        Mandatory=$True,
        Position=1
    )]
    [Alias("Key")]
    [byte[]]$EncryptionKey
)
    Try{
        $SecureString = ConvertTo-SecureString $EncryptedString -Key $EncryptionKey
        $bstr = [Runtime.InteropServices.Marshal]::SecureStringToBSTR($SecureString)
        [string]$String = [Runtime.InteropServices.Marshal]::PtrToStringAuto($bstr)
        [Runtime.InteropServices.Marshal]::ZeroFreeBSTR($bstr)

        Return $String
    }
    Catch{Throw $_}

}

To call this function simply input the encryptedString and key and you get the pre-encrypted string as the return value.

PS> Decrypt-String -EncryptedString $EncryptedString -EncryptionKey $Key
I am about to become encrypted!

Both functions accept value by pipeline so a Get-content can be used like this.

Get-Content C:\temp\encrypted.txt | Decrypt-String -EncryptionKey $Key

An overkill version of this is to double encode/encrypt. First convert the string to a base64 (encodedCommand style) then encode it with the key. This really adds no further security except to obfuscate the encrypted package and in case someone gets the key to decrypt they have to convert the encodedcommand to a string from base 64

Function Encrypt-String{
Param(
    
    [Parameter(
        Mandatory=$True,
        Position=0,
        ValueFromPipeLine=$true
    )]
    [Alias("String")]
    [String]$PlainTextString,
    
    [Parameter(
        Mandatory=$True,
        Position=1
    )]
    [Alias("Key")]
    [byte[]]$EncryptionKey
)
    Try{
        $bytes = [System.Text.Encoding]::Unicode.GetBytes($PlainTextString)
        $encodedCommand = [Convert]::ToBase64String($bytes)
        $secureString = Convertto-SecureString $encodedCommand -AsPlainText -Force
        $EncryptedString = ConvertFrom-SecureString -SecureString $secureString -Key $EncryptionKey

        return $EncryptedString
    }
    Catch{Throw $_}

}


Function Decrypt-String{
Param(
    [Parameter(
        Mandatory=$True,
        Position=0,
        ValueFromPipeLine=$true
    )]
    [Alias("String")]
    [String]$EncryptedString,

    [Parameter(
        Mandatory=$True,
        Position=1
    )]
    [Alias("Key")]
    [byte[]]$EncryptionKey
)
    Try{
        $SecureString = ConvertTo-SecureString $EncryptedString -Key $EncryptionKey
        $bstr = [Runtime.InteropServices.Marshal]::SecureStringToBSTR($SecureString)
        [string]$String = [Runtime.InteropServices.Marshal]::PtrToStringAuto($bstr)
        [Runtime.InteropServices.Marshal]::ZeroFreeBSTR($bstr)

        $data = @()
        [convert]::FromBase64String($encodedCommand) | ForEach-Object{
            If($_ -ne 0){
                $data += [char]$_
            }
        }

        Return ($data -join '')
    }
    Catch{Throw $_}

}

Counting in roman

1 to 100 looks like this

I, II, III, IV, V, VI, VII, VIII, VIV, X, XI, XII, XIII, XIV, XV, XVI, XVII, XVIII, XVIV, XX, XXI, XXII, XXIII, XXIV, XXV, XXVI, XXVII, XXVIII, XXVIV, XXX, XXXI, XXXII, XXXIII, XXXIV, XXXV, XXXVI, XXXVII, XXXVIII, XXXVIV, XC, XCI, XCII, XCIII, XCIV, XCV, XCVI, XCVII, XCVIII, XCVIV, L, LI, LII, LIII, LIV, LV, LVI, LVII, LVIII, LVIV, LX, LXI, LXII, LXIII, LXIV, LXV, LXVI, LXVII, LXVIII, LXVIV, LXX, LXXI, LXXII, LXXIII, LXXIV, LXXV, LXXVI, LXXVII, LXXVIII, LXXVIV, XC, XCI, XCII, XCIII, XCIV, XCV, XCVI, XCVII, XCVIII, XCVIV, LXC, LXCI, LXCII, LXCIII, LXCIV, LXCV, LXCVI, LXCVII, LXCVIII, LXCVIV, C
Function To-Roman{
Param([Parameter(Mandatory=$true)][int]$int)
    [int]$M = 1000
    [int]$D = 500
    [int]$C = 100
    [int]$L = 50
    [int]$X = 10
    [int]$V = 5
    [int]$I = 1
    If($int -eq 0){Throw "Zero does not exist in roman culture.";Return 404}
    $done = $false
    While($done -eq $false){

        #$Roman = $roman+(addup $int $m "M")
        If($int -ge $m){
            $n = Loopy $int $m
            $int = $n[0]
            1..$n[1] | %{$Roman = $Roman+"M"}
            
        }

        If($int -ge $D){
            $n = Loopy $int $D
            $int = $n[0]
            1..$n[1] | %{$Roman = $Roman+"D"}
        }

        If($int -ge $C){
            $n = Loopy $int $C
            $int = $n[0]
            1..$n[1] | %{$Roman = $Roman+"C"}
            
        }

        If($int -ge $L){
            $n = Loopy $int $L
            $int = $n[0]
            1..$n[1] | %{$Roman = $Roman+"L"}
        }

        If($int -ge $X){
            $n = Loopy $int $X
            $int = $n[0]
            1..$n[1] | %{$Roman = $Roman+"X"}
            
        }

        If($int -ge $V){
            $n = Loopy $int $V
            $int = $n[0]
            1..$n[1] | %{$Roman = $Roman+"V"}
        }

        If($int -ge $I){
            $n = Loopy $int $I
            $int = $n[0]
            1..$n[1] | %{$Roman = $Roman+"I"}
            
        }
        If($roman.Contains("CCCC")){$roman=$Roman.Replace("CCCC","CD")}
        If($roman.Contains("XXXX")){$roman=$Roman.Replace("XXXX","XC")}
        If($roman.Contains("LLLL")){$roman=$Roman.Replace("LLLL","XL")}
        If($roman.Contains("LXXX")){$roman=$Roman.Replace("LXXX","XC")}
        If($roman.Contains("VIV")){$roman=$Roman.Replace("VIV","IX")}
        If($roman.Contains("IIII")){$roman=$Roman.Replace("IIII","IV")}

        If($int -eq 0){$done = $true}
    }
    Return $Roman
}

Function Loopy{
Param(
    [int]$int,
    [int]$number
)
    $notDone = $true
    $loops = 0
    While($notDone){
        If($int-$number -ge 0){$int=$int-$number;$loops++}
        ElseIf($int-$number -lt $number){$notDone = $false}
    }
    return $int,$loops
}

1..1000 | %{"$(To-Roman $_) $_"}


Threaded GUI module

Function Invoke-GUI {
<#
    .SYNOPSIS
        Invoke-GUI creates a customizable threaded GUI for powershell
    .DESCRIPTION
        Creates a GUI from XAML file through Windows Presentation Foundation.
        All XAML data that require changeable data requires a "Name" node which sets the variable name
        Any XML nodes with name will get a variable assigned under $Code. and all subsequent data is accessible from there.
        The main GUI will be assigned $Code.GUI
    .PARAMETER File
        Sets a file with the XAML data used to build the interface.
    .PARAMETER XAML
        Input object in form on an already created XML variable.
    .PARAMETER OnTick
        Code to be executed on each tick
    .PARAMETER InitializationScript
        Code to be performed one time before the GUI shows.
    .PARAMETER TimerInterval
        Timer interval
    .PARAMETER LogFile
        LogFile location
    .Example
        Invoke-GUI -File C:\Application\GUI.xaml
        This loads the GUI without any tick action or custom initialization script.
    .Example
        Invoke-GUI -XAML $XAML -LogFile C:\Application\dump.log
        Loads the GUI from an already compiled XAML object and outputs logs to dump.log
    .Example
        Invoke-GUI -XAML $XAML -OnTick {$Code.MessageBox.Content = "Random number: $(Get-Random)"} -TimerInterval "0:1:0.00" -LogFile C:\Application\dump.log
        As example two but updates a MessageBox variable with a random number once per minute.
    .Example
        Invoke-GUI -XAML $XAML -TimerInterval "0:1:0.00" -LogFile C:\Application\dump.log -OnTick {
            $Code.MessageBox.Content = "Random number: $(Get-Random)"
        } -InitializationScript {
            $Code.Company.Content = "Contoso"
            $Code.Agreement.Value = 0
        }
        As example three but adds some company and agreement information at startup.
#>
[cmdletbinding()]
Param (
    [Parameter(Mandatory=$True,ParameterSetName="File")]
    [String]$File,
    [Parameter(Mandatory=$True,ParameterSetName="XAML")]
    [XML]$XAML,
    [Parameter(Mandatory=$False)]
    [ScriptBlock]$OnTick = {},
    [Parameter(Mandatory=$False)]
    [ScriptBlock]$InitializationScript = {},
    [Parameter(Mandatory=$False)]
    [TimeSpan]$TimerInterval = "0:0:1.00",
    [Parameter(Mandatory=$False)]
    [String]$LogFile
)
    
    If($LogFile){
        $time = get-date -Format "yyyy-MM-dd HH:mm:ss"
        "$Time | Starting GUI script" | Out-File $LogFile -Append
    }
    
    $Script:Code = [hashtable]::Synchronized(@{})
    $Code.TimerInterval = $TimerInterval
    $Code.OnTick = $OnTick
    $Code.InitializationScript = $InitializationScript
    $Code.ParameterSet = $PSCmdlet.ParameterSetName
    If($PSCmdlet.ParameterSetName -eq "File"){
        $Code.XAMLFile = $File
    }
    Else{
        $Code.XAML = $XAML
    }

    If($LogFile){
        $Code.LogFile = $LogFile
    }

    $Script:Runspacehash = [hashtable]::Synchronized(@{})
    $Runspacehash.host = $Host
    $Runspacehash.runspace = [RunspaceFactory]::CreateRunspace()
    $Runspacehash.runspace.ApartmentState = “STA”
    $Runspacehash.runspace.ThreadOptions = “ReuseThread”
    $Runspacehash.runspace.Open() 
    $Runspacehash.psCmd = {Add-Type -AssemblyName PresentationCore,PresentationFramework,WindowsBase}.GetPowerShell()
    $Runspacehash.runspace.SessionStateProxy.SetVariable("code",$Code)
    $Runspacehash.runspace.SessionStateProxy.SetVariable("Runspacehash",$Runspacehash)
    $Runspacehash.psCmd.Runspace = $Runspacehash.runspace
    

    $Runspacehash.Handle = $Runspacehash.psCmd.AddScript({ 

        $Script:Update = {
            # Do stuff on tick here
            .$Code.OnTick
        }

        If($Code.ParameterSet -eq "File"){[XML]$XAML = Get-Content $Code.XAMLFile}
        Else{[XML]$XAML = $Code.XAML}

        # builds gui as $Code.GUI with data from $XAML
        # creates variable for each xml property with the "name" assigned.
        Try {

            [reflection.assembly]::loadwithpartialname("System.Windows.Forms") | Out-Null

            $objXMLReader = (New-Object System.Xml.XmlNodeReader $XAML)
            $Code.GUI = [Windows.Markup.XamlReader]::Load($objXMLReader)
        
            $Code.GUI.WindowStartupLocation = "CenterScreen"

            If($Code.LogFile){
                $time = get-date -Format "yyyy-MM-dd HH:mm:ss"
                "$Time | Creating variables for XAML elements.." | Out-File $Code.LogFile -Append
            }
            $XAML.SelectNodes("//*[@Name]") | ForEach-Object {
                If($Code.LogFile){
                    $time = get-date -Format "yyyy-MM-dd HH:mm:ss"
                    "$Time | Variable created: `$Code.$($_.Name)" | Out-File $Code.LogFile -Append
                }
                $Code."$($_.Name)" = $Code.GUI.FindName($_.Name)
            }
        }
        Catch {
            If($Code.LogFile){
                $time = get-date -Format "yyyy-MM-dd HH:mm:ss"
                "$Time | $_" | Out-File $Code.LogFile -Append
            }
            Exit        
        }

        #Timer Event
        $Code.GUI.Add_SourceInitialized({
            $Script:timer = new-object System.Windows.Threading.DispatcherTimer 
            If($Code.LogFile){
                $time = get-date -Format "yyyy-MM-dd HH:mm:ss"
                "$Time | Timer interval is: $($Code.TimerInterval)" | Out-File $Code.LogFile -Append
            }
            $timer.Interval = [TimeSpan]$Code.TimerInterval
        
            $timer.Add_Tick({
                $Update.Invoke()
                [Windows.Input.InputEventHandler]{$Code.GUI.UpdateLayout()}
            })
        
            #Start timer
            If($Code.LogFile){
                $time = get-date -Format "yyyy-MM-dd HH:mm:ss"
                "$Time | Starting timer" | Out-File $Code.LogFile -Append
            }
            $timer.Start()
            If (-NOT $timer.IsEnabled) {

                If($Code.LogFile){
                    $time = get-date -Format "yyyy-MM-dd HH:mm:ss"
                    "$Time | Stopping GUI" | Out-File $Code.LogFile -Append
                }

                $Code.GUI.Close()
            }
        }) 

        $Code.GUI.Add_Closed({
            If($Code.LogFile){
                $time = get-date -Format "yyyy-MM-dd HH:mm:ss"
                "$Time | GUI disposed" | Out-File $Code.LogFile -Append
            }
            $timer.Stop()
            $Runspacehash.PowerShell.Dispose()
    
            [gc]::Collect()
            [gc]::WaitForPendingFinalizers()    
        })

        Try{$Code.InitializationScript.Invoke()}
        Catch{
            If($Code.LogFile){
                $time = get-date -Format "yyyy-MM-dd HH:mm:ss"
                "$Time | $_" | Out-File $Code.LogFile -Append
            }
        }

        # Does not work to place this in the InitializationScript yet. Need to work around this somehow
        $Code.GUI.Add_MouseLeftButtonDown({
            $This.DragMove()
        })

        
        $Code.GUI.ShowDialog() | Out-Null

    }).BeginInvoke()

    #Give gui time to initialize
    Start-sleep 1
}

Function Update-GUI{
<#
    .SYNOPSIS
        Update-GUI Sends a scriptblock for execution in the GUI thread.
    .DESCRIPTION
        Takes a Scriptblock for execution in the GUI thread.
        It is a simple command (9 rows) but as it is used often converted to a function.
        Code is:
        $Code.GUI.Dispatcher.Invoke([action]{
            Try{.$ScriptBlock}
            Catch{
                If($Code.LogFile){
                    $time = get-date -Format "yyyy-MM-dd HH:mm:ss"
                    "$Time | Could not execute command: $ScriptBlock" | Out-File $Code.LogFile -Append
                }
            }    
        })
    .PARAMETER ScriptBlock
        Scriptblock to run inside thread.
    .Example
        Update-GUI -ScriptBlock {$Code.Message.content = "Hello world"}
        Updates the message node content with "hello world"
#>
Param(
    [Parameter(Mandatory=$True)]
    [ScriptBlock]$ScriptBlock
) 
    $Code.GUI.Dispatcher.Invoke([action]{
        Try{.$ScriptBlock}
        Catch{
            If($Code.LogFile){
                $time = get-date -Format "yyyy-MM-dd HH:mm:ss"
                "$Time | Could not execute command: $ScriptBlock" | Out-File $Code.LogFile -Append
            }
        }    
    })
}

Microsoft hotfix indexer and browser

A while back I made a quick hotfix indexer script and database browser.

These were not too great but had sufficient functionality for the time. Now that I have expanded on functionality I want to write this as a single post.

Any Microsoft enterprise technician could see the value in having a list like this.
The question is why Microsoft don’t provide this list themselves.
Anyhow, let’s begin!

This package contains three files. One indexer.ps1. One HotfixBrowser.ps1 and a config file where you specify SQL connectionstring and table to be used before running the scripts.

The heart of the script is the indexer. It goes through a range specified and lists them in a database. The table must have the following columns:

Capture2

Other columns can be added if needed and will be listed in the hotfix browser.
What I do is add a scheduled task that runs this script with a set of ranges every weekend to get a up-to date view.

When the indexer has completed you can start HotfixBrowser.ps1browser

From here you can search with standard SQL syntax. All fields to a LIKE search so for example All windows 8 hotfixes released during 2014 has this search criteria.
2

Clicking search allows you to quickly see the short text of all articles involving Windows 8 with a hotfix download available allowing you to work efficiently and determine if any of the hotfixes are applicable to your client environment.
3

There are also the option to go ‘advanced’ which allows you to edit the Select query as you see fit.
4

In the File menu you can export the current datatable as a CSV file.
In the tools mode you can save and load searches as well as choose visible columns and enable edit mode.
Edit mode makes the cells writable and allows you to send a UPDATE query to the server (given the appropriate rights) to edit some fields such as note and other custom fields.

Hotfix browser

#Requires -version 2
[CmdletBinding()]
Param($min,$max)
Begin{
    $MyPath = Split-Path $MyInvocation.MyCommand.path -Parent
    
    Function SaveFileDialog{
    Param(
        [Parameter(Mandatory=$True)]
        $Filetype
    )
        [Void][Reflection.Assembly]::LoadWithPartialName("System.Windows.Forms")
        $SaveFileDialog = New-Object System.Windows.Forms.SaveFileDialog
        $SaveFileDialog.Filter = "$Filetype files (*.$Filetype)|*.$Filetype|All files (*.*)|*.*"
        $status = $SaveFileDialog.ShowDialog()

        If($status -eq "Cancel"){$Return = $status}
        Else{$Return = $SaveFileDialog.FileName}

        $SaveFileDialog.Dispose()
        Return $Return
    }
    
    Function Call-Form{
        [reflection.assembly]::loadwithpartialname("System.Windows.Forms") | Out-Null
        [reflection.assembly]::loadwithpartialname("System.Drawing") | Out-Null
        $EditMode = $False
        $savepath = "$MyPath\SavedSearches.xml"
        
        ### MAINFORM
        $form1 = New-Object System.Windows.Forms.Form
        $label7 = New-Object System.Windows.Forms.Label
        $label6 = New-Object System.Windows.Forms.Label
        $NotesSearch = New-Object System.Windows.Forms.TextBox
        $LinkSearch = New-Object System.Windows.Forms.TextBox
        $label5 = New-Object System.Windows.Forms.Label
        $label4 = New-Object System.Windows.Forms.Label
        $Progressbar = New-Object System.Windows.Forms.ProgressBar
        $DownloadSearch = New-Object System.Windows.Forms.ComboBox
        $label3 = New-Object System.Windows.Forms.Label
        $label2 = New-Object System.Windows.Forms.Label
        $label1 = New-Object System.Windows.Forms.Label
        $ReviewSearch = New-Object System.Windows.Forms.TextBox
        $AppliestoSearch = New-Object System.Windows.Forms.TextBox
        $ShortTextSearch = New-Object System.Windows.Forms.TextBox
        $KBIDsearch = New-Object System.Windows.Forms.TextBox
        $searchButton = New-Object System.Windows.Forms.Button
        $dataGrid = New-Object System.Windows.Forms.DataGridView
        $InitialFormWindowState = New-Object System.Windows.Forms.FormWindowState
        

        #SAVE FORM
        $saveForm = New-Object System.Windows.Forms.Form
        $saveBox = New-Object System.Windows.Forms.TextBox
        $SaveButton = New-Object System.Windows.Forms.Button
        $listView = New-Object System.Windows.Forms.ListView
        
        #LOAD FORM
        $loadForm = New-Object System.Windows.Forms.Form
        $loadBox = New-Object System.Windows.Forms.TextBox
        $loadButton = New-Object System.Windows.Forms.Button
        
        $searchButton_Click ={
            $Progressbar.Show()
            If($DownloadSearch.Text -eq "True"){
                $DL = "1"
            }
            Else{
                $DL = "0"
            }
            
            
            #Get values from fields
            $SearchValues = @{}
            $SearchValues.Add("KBID",$KBIDsearch.Text)
            $SearchValues.Add("ShortText",$ShortTextSearch.Text)
            $SearchValues.Add("Download",$DL)
            $SearchValues.Add("AppliesTo",$AppliestoSearch.Text)
            $SearchValues.Add("LastReviewed",$ReviewSearch.Text)
            $SearchValues.Add("Link",$LinkSearch.Text)
            $SearchValues.Add("Notes",$NotesSearch.Text)
            
            #Compile search query
            $Query = "Select KBID,ShortText,Download,AppliesTo,LastReviewed,Link,Notes FROM hotfix_primary WHERE Exist = 1"
            $SearchValues.Keys | ForEach-Object{
                If($SearchValues.$_ -ne ''){
                    $value = $SearchValues.$_
                    $Query = "$Query AND $_ LIKE `'$Value`'"
                }
                
            }
            Try{
                $Data = Get-SQLQuery $Query
                $dataGrid.Rows.Clear()
                $Data | ForEach-Object{
                    [datetime]$date = $_.LastReviewed
                    $lastReviewed = '{0:yyyy-MM-dd}' -f $Date
                    $dataGrid.Rows.Add($_.KBID,$_.ShortText,$_.Download,$_.AppliesTo,$lastReviewed,$_.Link,$_.Notes)
                }
            }
            Catch{}#Write-Error $_}
            $Progressbar.Hide()
        }

        $OnLoadForm_StateCorrection=
        {#Correct the initial state of the form to prevent the .Net maximized form issue
        	$form1.WindowState = $InitialFormWindowState
        }
        
        # Menu button clicks
        
        $toolEdit_Click = {
            If($EditMode -eq $False){
                $toolEdit.Checked = $True
                $EditMode = $True
                $MainMenu.Items.Add($editMenu) | out-null
                $datagrid.columns | ForEach-Object{$_.ReadOnly = $False}
            }
            Else{
                $toolEdit.Checked = $False
                $EditMode = $False
                $MainMenu.Items.Remove($editMenu) | out-null
                $datagrid.columns | ForEach-Object{$_.ReadOnly = $True}
            }
        }
        
        $fileExport_Click = {
            $File = SaveFileDialog -Filetype "csv"
            If($File -ne "Cancel"){
                $Progressbar.Show()
                "KBID,ShortText,Download,AppliesTo,LastReviewed,Link,Notes" | Out-File $File
                $dataGrid.Rows | ForEach-Object{
                    $Cells = $_.cells
                    $KBID = ($cells | Where-object{$_.OwningColumn.Name -eq "KBID"}).Value
                    $ShortText = ($cells | Where-object{$_.OwningColumn.Name -eq "ShortText"}).Value
                    $Download = ($cells | Where-object{$_.OwningColumn.Name -eq "Download"}).Value
                    $AppliesTo = ($cells | Where-object{$_.OwningColumn.Name -eq "AppliesTo"}).Value
                    $LastReviewed = ($cells | Where-object{$_.OwningColumn.Name -eq "LastReviewed"}).Value
                    $Link = ($cells | Where-object{$_.OwningColumn.Name -eq "Link"}).Value
                    $Notes = ($cells | Where-object{$_.OwningColumn.Name -eq "Notes"}).Value
                    If($KBID -ne $null){
                        "$KBID,$ShortText,$Download,$AppliesTo,$LastReviewed,$Link,$Notes" | Out-File $File -Append
                    }                    
                }
                $Progressbar.Hide()
            }
        }
        
        $toolClear_Click = {
            $dataGrid.Rows.Clear()
            
            $KBIDsearch.Text = ''
            $ShortTextSearch.Text = ''
            $DownloadSearch.Text = "True"
            $AppliestoSearch.Text = ''
            $ReviewSearch.Text = ''
            $LinkSearch.Text = ''
            $NotesSearch.Text = ''
        }
        
        $toolSave_Click = {
            .$saveloadOnLoad
            $saveForm.Controls.Add($listView)
            $saveForm.ShowDialog()| Out-Null
        }
        $toolLoad_Click = {
            .$saveloadOnLoad
            $loadForm.Controls.Add($listView)
            $loadForm.ShowDialog()| Out-Null
        }
        
        $fileQuit_Click = {
            $form1.Close()
        }
        
        $SaveButton_OnClick = {
            $new = $saves.CreateElement('Save')
            $Name = $SaveBox.Text
            $new.SetAttribute('Name',"$($SaveBox.Text)")
            $new.SetAttribute('Query',"$Query")
            Try{($saves.SavedSearches.Save | Where-Object{$_.Name -eq $Name}).RemoveAll()}Catch{}
            $saves.SavedSearches.AppendChild($new)
            $saves.Save($savepath)
            #clean up bad elements
            [xml]$Clean = Get-Content $savepath | Where-Object{($_ -NotMatch '') -or ($_ -NotMatch 'Save Name=""')}
            $Clean.Save($savepath)
            $saveForm.Controls.Remove($listView)
            $saveForm.Close()
        }
        $LoadButton_OnClick = {
            $Query = ($saves.SavedSearches.Save | Where-object {$_.Name -Match "$($loadBox.Text)"}).Query
            $loadForm.Controls.Remove($listView)
            $loadForm.Close()
            $Progressbar.Show()
            $KBIDsearch.Text = ''
            $ShortTextSearch.Text = ''
            $DownloadSearch.Text = "True"
            $AppliestoSearch.Text = ''
            $ReviewSearch.Text = ''
            $LinkSearch.Text = ''
            $NotesSearch.Text = ''
            Try{
                $Data = Get-SQLQuery $Query
                $dataGrid.Rows.Clear()
                $Data | ForEach-Object{
                    [datetime]$date = $_.LastReviewed
                    $lastReviewed = '{0:yyyy-MM-dd}' -f $Date
                    $dataGrid.Rows.Add($_.KBID,$_.ShortText,$_.Download,$_.AppliesTo,$lastReviewed,$_.Link,$_.Notes)
                }
            }
            Catch{Write-Error $_}
            $Progressbar.Hide()
        }
        
        $listView_OnSelected = {
            $SaveBox.Text = $listView.SelectedItems[0].Text
            $loadBox.Text = $listView.SelectedItems[0].Text
        }
        
        $saveloadOnLoad = {
            $loadBox.Text = ''
            $SaveBox.Text = ''
            $listView.Items.Clear()
            [xml]$Clean = Get-Content $savepath | Where-Object{($_ -NotMatch '') -or ($_ -NotMatch 'Save Name=""')}
            $Clean.Save($savepath)
            $saves = [xml](Get-Content $savepath)
            $saves.SavedSearches.Save | ForEach-Object{
                $listView.Items.Add("$($_.Name)")|Out-Null
            }
        }
        
        $editClear_Click = {
            $new = "`r`n`r`n"
            $new | out-file $savepath
        }
        
        $editUpdate_Click = {
        
            $dataGrid.Rows | ForEach-Object{
                $Cells = $_.cells
                $KBID = ($cells | Where-object{$_.OwningColumn.Name -eq "KBID"}).Value
                If($KBID -ne $null){
                    $ShortText = ($cells | Where-object{$_.OwningColumn.Name -eq "ShortText"}).Value
                    $Download = ($cells | Where-object{$_.OwningColumn.Name -eq "Download"}).Value
                    $AppliesTo = ($cells | Where-object{$_.OwningColumn.Name -eq "AppliesTo"}).Value
                    [Datetime]$LastReviewed = ($cells | Where-object{$_.OwningColumn.Name -eq "LastReviewed"}).Value
                    $Link = ($cells | Where-object{$_.OwningColumn.Name -eq "Link"}).Value
                    $Notes = ($cells | Where-object{$_.OwningColumn.Name -eq "Notes"}).Value
                
                    $Columns = "KBID","ShortText","Download","AppliesTo","LastReviewed","Link","Notes"
                    $Values = "`'$KBID`'","`'$ShortText`'","`'$Download`'","`'$AppliesTo`'","`'$LastReviewed`'","`'$Link`'","`'$Notes`'"
                    $UpdateCMD = "UPDATE hotfix_primary SET $($c = $Columns.count;(1..($c-1) | ForEach-Object{"$($Columns[$_]) = $($Values[$_])"}) -Join ',') WHERE KBID = `'$KBID`';"
                    Process-SQLCmd $UpdateCMD
                }                    
            }
            
        }

        #----------------------------------------------
        #region Generated Form Code
        $form1.ClientSize = "853,485"
        $form1.DataBindings.DefaultDataSourceUpdateMode = 0
        $form1.Text = "KB article browser"
        ### Main Menu Settings
        $MainMenu = new-object System.Windows.Forms.MenuStrip
        $MainMenu.Location = "0,0"
        $MainMenu.Name = "MainMenu"
        $MainMenu.Size = "1000,620"
        $MainMenu.TabIndex = 0
        $MainMenu.Text = "Main Menu"
        
        ## File Menu settings
        $fileMenu = new-object System.Windows.Forms.ToolStripMenuItem('&fileMenu')
        $fileMenu.Size = "35,20"
        $fileMenu.Text = "File"
        
        # Quit button
        $fileQuit = new-object System.Windows.Forms.ToolStripMenuItem('&Quit')
        $fileQuit.add_Click($fileQuit_Click)
        # Save button
        $fileExport = new-object System.Windows.Forms.ToolStripMenuItem('&Export list to CSV')
        $fileExport.Add_Click($fileExport_Click)
        
        # Add to file menu
        $fileMenu.DropDownItems.Add($fileExport) | out-null
        $fileMenu.DropDownItems.Add($fileQuit) | out-null
        
        ## Tool Menu settings
        $toolMenu = new-object System.Windows.Forms.ToolStripMenuItem('&toolMenu')
        $toolMenu.Size = "35,20"
        $toolMenu.Text = "Tools"
        
        $toolSave = new-object System.Windows.Forms.ToolStripMenuItem('&Save search')
        $toolSave.add_Click($toolSave_Click)
        
        # Copy button
        $toolLoad = new-object System.Windows.Forms.ToolStripMenuItem('&Load search')
        $toolLoad.add_Click($toolLoad_Click)
        
        # Copy button
        $toolEdit = new-object System.Windows.Forms.ToolStripMenuItem('&Edit mode')
        $toolEdit.add_Click($toolEdit_Click)

        # Clear button
        $toolClear = new-object System.Windows.Forms.ToolStripMenuItem('&Clear/Reset')
        $toolClear.add_Click($toolClear_Click)

        # Add to tool menu
        $toolMenu.DropDownItems.Add($toolSave) | out-null
        $toolMenu.DropDownItems.Add($toolLoad) | out-null
        $toolMenu.DropDownItems.Add($toolEdit) | out-null
        $toolMenu.DropDownItems.Add($toolClear) | out-null
        
        ## Edit Menu settings
        $editMenu = new-object System.Windows.Forms.ToolStripMenuItem('&editMenu')
        $editMenu.Size = "35,20"
        $editMenu.Text = "Edit"
        
        # Clear saved searches button
        $editClear = new-object System.Windows.Forms.ToolStripMenuItem('&Remove all saved searches')
        $editClear.add_Click($editClear_Click)
        
        # Update button
        $editUpdate = new-object System.Windows.Forms.ToolStripMenuItem('&Update database')
        $editUpdate.add_Click($editUpdate_Click)
        
        # Add to tool menu
        $editMenu.DropDownItems.Add($editUpdate) | out-null
        $editMenu.DropDownItems.Add($editClear) | out-null
        
        # Add main menu buttons
        $MainMenu.Items.Add($fileMenu) | out-null
        $MainMenu.Items.Add($toolMenu) | out-null
        
        $form1.Controls.Add($MainMenu) | out-null
        
        #######Searchboxes##########
        
        $NotesSearch.DataBindings.DefaultDataSourceUpdateMode = 0
        $NotesSearch.Location = "653,64"
        $NotesSearch.Name = "NotesSearch"
        $NotesSearch.Size = "93,20"
        $NotesSearch.TabIndex = 15

        $form1.Controls.Add($NotesSearch)

        $LinkSearch.DataBindings.DefaultDataSourceUpdateMode = 0
        $LinkSearch.Location = "553,64"
        $LinkSearch.Name = "LinkSearch"
        $LinkSearch.Size = "94,20"
        $LinkSearch.TabIndex = 14

        $form1.Controls.Add($LinkSearch)
        
        $DownloadSearch.DataBindings.DefaultDataSourceUpdateMode = 0
        $DownloadSearch.FormattingEnabled = $False
        $DownloadSearch.Items.Add("True")|Out-Null
        $DownloadSearch.Items.Add("False")|Out-Null
        $DownloadSearch.Location = "253,64"
        $DownloadSearch.Name = "DownloadSearch"
        $DownloadSearch.Size = "94,21"
        $DownloadSearch.TabIndex = 10
        $DownloadSearch.SelectedItem = "True"
        $DownloadSearch.DropDownStyle = 2
        
        $form1.Controls.Add($DownloadSearch)
        
        $ReviewSearch.DataBindings.DefaultDataSourceUpdateMode = 0
        $ReviewSearch.Location = "453,65"
        $ReviewSearch.Name = "ReviewSearch"
        $ReviewSearch.Size = "94,20"
        $ReviewSearch.TabIndex = 6

        $form1.Controls.Add($ReviewSearch)

        $AppliestoSearch.DataBindings.DefaultDataSourceUpdateMode = 0
        $AppliestoSearch.Location = "353,65"
        $AppliestoSearch.Name = "AppliestoSearch"
        $AppliestoSearch.Size = "94,20"
        $AppliestoSearch.TabIndex = 5

        $form1.Controls.Add($AppliestoSearch)

        $ShortTextSearch.DataBindings.DefaultDataSourceUpdateMode = 0
        $ShortTextSearch.Location = "153,65"
        $ShortTextSearch.MaxLength = 128
        $ShortTextSearch.Name = "ShortTextSearch"
        $ShortTextSearch.Size = "94,20"
        $ShortTextSearch.TabIndex = 3

        $form1.Controls.Add($ShortTextSearch)

        $KBIDsearch.DataBindings.DefaultDataSourceUpdateMode = 0
        $KBIDsearch.Location = "53,65"
        $KBIDsearch.MaxLength = 7
        $KBIDsearch.Name = "KBIDsearch"
        $KBIDsearch.Size = "94,20"
        $KBIDsearch.TabIndex = 2

        $form1.Controls.Add($KBIDsearch)
        
        $searchButton.Anchor = 9

        $searchButton.DataBindings.DefaultDataSourceUpdateMode = 0
        $searchButton.Location = "752,64"
        $searchButton.Name = "searchButton"
        $searchButton.Size = "88,21"
        $searchButton.TabIndex = 1
        $searchButton.Text = "Search"
        $searchButton.UseVisualStyleBackColor = $True
        $searchButton.add_Click($searchButton_Click)

        $form1.Controls.Add($searchButton)
        
        #####Labels and the rest#####

        $label7.DataBindings.DefaultDataSourceUpdateMode = 0

        $label7.Location = "653,43"
        $label7.Name = "label7"
        $label7.Size = "100,15"
        $label7.TabIndex = 17
        $label7.Text = "Notes"

        $form1.Controls.Add($label7)

        $label6.DataBindings.DefaultDataSourceUpdateMode = 0

        $label6.Location = "554,43"
        $label6.Name = "label6"
        $label6.Size = "100,14"
        $label6.TabIndex = 16
        $label6.Text = "Link"

        $form1.Controls.Add($label6)

        $label5.DataBindings.DefaultDataSourceUpdateMode = 0

        $label5.Location = "454,43"
        $label5.Name = "label5"
        $label5.Size = "93,18"
        $label5.TabIndex = 13
        $label5.Text = "Last Review Date"

        $form1.Controls.Add($label5)

        $label4.DataBindings.DefaultDataSourceUpdateMode = 0

        $label4.Location = "353,43"
        $label4.Name = "label4"
        $label4.Size = "94,15"
        $label4.TabIndex = 12
        $label4.Text = "AppliesTo"

        $form1.Controls.Add($label4)

        $Progressbar.DataBindings.DefaultDataSourceUpdateMode = 0
        $Progressbar.Location = "13,437"
        $Progressbar.Name = "Progressbar"
        $Progressbar.Size = "828,36"
        $Progressbar.Style = 2
        $Progressbar.TabIndex = 11
        $Progressbar.Anchor = 14

        $form1.Controls.Add($Progressbar)
        $Progressbar.Hide()


        $label3.DataBindings.DefaultDataSourceUpdateMode = 0

        $label3.Location = "253,43"
        $label3.Name = "label3"
        $label3.Size = "94,18"
        $label3.TabIndex = 9
        $label3.Text = "Hotfix available"

        $form1.Controls.Add($label3)

        $label2.DataBindings.DefaultDataSourceUpdateMode = 0

        $label2.Location = "153,44"
        $label2.Name = "label2"
        $label2.Size = "94,18"
        $label2.TabIndex = 8
        $label2.Text = "Short Text"

        $form1.Controls.Add($label2)

        $label1.DataBindings.DefaultDataSourceUpdateMode = 0
        $label1.Location = "53,44"
        $label1.Name = "label1"
        $label1.RightToLeft = 0
        $label1.Size = "94,18"
        $label1.TabIndex = 7
        $label1.Text = "KB ID"
        $label1.add_Click($handler_label1_Click)

        $form1.Controls.Add($label1)
        
        ####DATA GRID COLUMNS######
        
        $dataGrid.Anchor = 15
        $KBIDColumn = New-Object System.Windows.Forms.DataGridViewTextBoxColumn
        $KBIDColumn.HeaderText = "KB ID"
        $KBIDColumn.Name = "KBID"
        $KBIDColumn.ReadOnly = $True
        $KBIDColumn.Width = 100

        $dataGrid.Columns.Add($KBIDColumn)|Out-Null
        
        $ShortTextColumn = New-Object System.Windows.Forms.DataGridViewTextBoxColumn
        $ShortTextColumn.HeaderText = "Short Text"
        $ShortTextColumn.Name = "ShortText"
        $ShortTextColumn.ReadOnly = $True
        $ShortTextColumn.Width = 100

        $dataGrid.Columns.Add($ShortTextColumn)|Out-Null
        
        $DownloadColumn = New-Object System.Windows.Forms.DataGridViewTextBoxColumn
        $DownloadColumn.HeaderText = "Hotfix available"
        $DownloadColumn.Name = "Download"
        $DownloadColumn.ReadOnly = $True
        $DownloadColumn.Width = 100

        $dataGrid.Columns.Add($DownloadColumn)|Out-Null
        
        $AppliesToColumn = New-Object System.Windows.Forms.DataGridViewTextBoxColumn
        $AppliesToColumn.HeaderText = "Applies to"
        $AppliesToColumn.Name = "AppliesTo"
        $AppliesToColumn.ReadOnly = $True
        $AppliesToColumn.Width = 100

        $dataGrid.Columns.Add($AppliesToColumn)|Out-Null
        
        $DateColumn = New-Object System.Windows.Forms.DataGridViewTextBoxColumn
        $DateColumn.HeaderText = "Last Review date"
        $DateColumn.Name = "LastReviewed"
        $DateColumn.ReadOnly = $True
        $DateColumn.Width = 100

        $dataGrid.Columns.Add($DateColumn)|Out-Null
        
        $LinkColumn = New-Object System.Windows.Forms.DataGridViewTextBoxColumn
        $LinkColumn.HeaderText = "Link"
        $LinkColumn.Name = "Link"
        $LinkColumn.ReadOnly = $True
        $LinkColumn.Width = 100

        $dataGrid.Columns.Add($LinkColumn)|Out-Null
        
        $NotesColumn = New-Object System.Windows.Forms.DataGridViewTextBoxColumn
        $NotesColumn.HeaderText = "Notes"
        $NotesColumn.Name = "Notes"
        $NotesColumn.ReadOnly = $False
        $NotesColumn.Width = 100

        $dataGrid.Columns.Add($NotesColumn)|Out-Null
        
        $dataGrid.DataBindings.DefaultDataSourceUpdateMode = 0
        $dataGrid.Location = "12,91"
        $dataGrid.Name = "dataGrid"
        $dataGrid.Size = "828,382"
        $dataGrid.TabIndex = 0
        $dataGrid.add_CellContentClick($handler_dataGrid_CellContentClick)

        $form1.Controls.Add($dataGrid)

        #endregion Generated Form Code

        #Save the initial state of the form
        $InitialFormWindowState = $form1.WindowState
        #Init the OnLoad event to correct the initial state of the form
        $form1.add_Load($OnLoadForm_StateCorrection)
        
        ### SAVE SEARCH FORM SETTINGS
        $saveForm.ClientSize = "246,196"
        $saveForm.DataBindings.DefaultDataSourceUpdateMode = 0
        $saveForm.Name = "saveForm"
        $saveForm.Text = "Save current search"
        $form1.add_Load($saveloadOnLoad)

        $saveBox.Anchor = 14
        $saveBox.DataBindings.DefaultDataSourceUpdateMode = 0
        $saveBox.Location = "1,133"
        $saveBox.Name = "saveBox"
        $saveBox.Size = "245,20"
        $saveBox.TabIndex = 2

        $saveForm.Controls.Add($saveBox)

        $SaveButton.Anchor = 14

        $SaveButton.DataBindings.DefaultDataSourceUpdateMode = 0

        $SaveButton.Location = "1,159"
        $SaveButton.Name = "SaveButton"
        $SaveButton.Size = "245,38"
        $SaveButton.TabIndex = 1
        $SaveButton.Text = "Save"
        $SaveButton.UseVisualStyleBackColor = $True
        $SaveButton.add_Click($SaveButton_OnClick)

        $saveForm.Controls.Add($SaveButton)

        $listView.Anchor = 15
        $listView.DataBindings.DefaultDataSourceUpdateMode = 0
        $listView.Location = "1,1"
        $listView.Name = "listView"
        $listView.Size = "245,121"
        $listView.TabIndex = 0
        $listView.add_MouseClick($listView_OnSelected)
        #$listView.add_KeyPress($listView_OnDelete)
        $listView.View = 3
        
        ### LOAD SEARCH FORM SETTINGS
        $loadForm.ClientSize = "246,196"
        $loadForm.DataBindings.DefaultDataSourceUpdateMode = 0
        $loadForm.Name = "loadForm"
        $loadForm.Text = "Load current search"
        $form1.add_Load($saveloadOnLoad)

        $loadBox.Anchor = 14
        $loadBox.DataBindings.DefaultDataSourceUpdateMode = 0
        $loadBox.Location = "1,133"
        $loadBox.Name = "loadBox"
        $loadBox.Size = "245,20"
        $loadBox.TabIndex = 2

        $loadForm.Controls.Add($loadBox)

        $loadButton.Anchor = 14

        $loadButton.DataBindings.DefaultDataSourceUpdateMode = 0

        $loadButton.Location = "1,159"
        $loadButton.Name = "loadButton"
        $loadButton.Size = "245,38"
        $loadButton.TabIndex = 1
        $loadButton.Text = "load"
        $loadButton.UseVisualStyleBackColor = $True
        $loadButton.add_Click($loadButton_OnClick)
        
        $loadForm.Controls.Add($listView)
        $loadForm.Controls.Add($loadButton)
        
        #Show the Form
        If(!(Test-Path $savePath)){.$editClear_Click}
        $form1.ShowDialog()| Out-Null
    }
    
    Function Process-SQLCmd {
    Param(
        [Parameter(Mandatory=$true)]
        [String]$Command,
        [Parameter(Mandatory=$false)]
        [String]$ConnectionString = 'Server=LocalHost\SQL;Database=ClientMaintenance;Trusted_Connection=True;'
    )
        $connection = New-Object System.Data.SqlClient.SqlConnection
        $connection.ConnectionString = $ConnectionString
        $connection.Open()
        $cmd = New-Object System.Data.SQLClient.SQLCommand
        $cmd.Connection = $connection
        $cmd.CommandText = $Command
        Try{$cmd.ExecuteNonQuery()}
        Catch{
            If(!($_.Exception -match 'Violation of PRIMARY KEY constraint')){
                $_.Exception | Out-file "$Mypath\error.log" -append
                $command | Out-file "$Mypath\error.log" -append
            }
        }
        $connection.Close()
    }
    
    Function Get-SQLQuery{
    Param(
        [Parameter(Mandatory=$true)]
        [String]$SQLQuery,
        [Parameter(Mandatory=$false)]
        [String]$ConnectionString = 'Server=LocalHost\SQL;Database=ClientMaintenance;Trusted_Connection=True;'
    )
        $connection = New-Object System.Data.SqlClient.SqlConnection
        $connection.ConnectionString = "$ConnectionString"
        $connection.Open()
        $Command = New-Object System.Data.SQLClient.SQLCommand
        $Command.CommandTimeout = 300
        $Command.Connection = $Connection
        $Command.CommandText = $SQLQuery
        Try{
            $Reader = $Command.ExecuteReader()
            $Counter = $Reader.FieldCount
            
            $DataTable = New-Object system.Data.DataTable
            for($i = 0; $i -lt $counter; $i++){
                $Column = New-Object system.Data.DataColumn $Reader.GetName($i),([string])
                $DataTable.Columns.Add($Column)
            }
            
            while ($Reader.Read()) {
                $strData = @()
                for ($i = 0; $i -lt $Counter; $i++) {
                    $strData += [String]$Reader.GetValue($i)
                }
                $DataTable.Rows.Add($strData) | out-null
            }
            $Reader.Close()
            $Data = $Datatable
        }
        Catch{
            Write-Error $_
            $Data  = $_.Exception
        }
        Finally{
            $Connection.Close()
        }
        Return $Data
    }
}
Process{
    
    Call-Form
}
End{}