Category Archives: Powershell

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{}

Index all microsoft hotfixes

Microsoft has a crappy hotfix index mechanism. I want to change this, and I’ve done this by indexing all support.microsoft articles myself.

To do this you need a SQL server with the following table configuration
Capture

Then you run the indexer script. It goes through all hotfixes inefficiently but gets the job done.
All support.microsoft.com/kb pages are indexed. From 1 to 4000000

Result in MSSQL looks something like this:

Capture

Due to web formatting the script can’t be displayed here. Instead I’ve linked it below as a .txt file
Script

Of course, there is a hotfix browser that is used to search and view these but it’s not done yet.

Get startpage

Invent startpage set in IE for XP and Windows7 clients. Needs a list of computers and collects the startpage in a CSV file.

[cmdletbinding()]
Param(
    $Computers,
    $output,
    $Progress,
    [Switch]$Loop
)

$ErrorActionPreference = "SilentlyContinue"

If(Test-Path -Path $Computers -PathType Leaf){
    [Array]$Script:computers = Get-Content $Computers
}

$ComputerCount = $Computers.Count
$Script:Database = @{}
Function Get-Startpage{
    Param(
        $output,
        $Progress
    )
    if(Test-Path -Path $Progress -PathType Leaf){
        [Array]$ProgressContent = Get-Content $Progress
    }
    Else{
        Try{"DATE,Computer,Status" | Out-File $Progress
            [Array]$ProgressContent = Get-Content $Progress
        }
        Catch{Write-Error $_;Break}
    }
    If(!(Test-Path -Path $output -PathType Leaf)){
        Try{'SID,Computername,USER,Start Page' | Out-File $output}
        Catch{Write-Error $_;Break}
    }
    Foreach($Client in $Script:Computers){
        
        Try{
            [Array]$status = $ProgressContent | Where-Object{ $_ -match "$Client"}
            $Status = $status[-1].Split(",")[-1]
        }
        Catch{$status -eq $null}
        
        Write-Host $Client
        
        $Ping = new-object Net.NetworkInformation.Ping
        
        If(($Ping.Send($Client).Status -eq "Success") -and (($status -eq "Failed") -or ($status -eq $null))){
            
            $OperatingSystem  = (([adsisearcher]"cn=$client").FindOne()).Properties.operatingsystem
            
            Switch($OperatingSystem){
                "Windows 7 Enterprise"{
                    Try{$Userprofile = Get-WmiObject -ComputerName $client win32_userprofile}
                    Catch{
                        Write-Host $Client
                        Write-Error $_
        				$Userprofile = $null
        			}
                    If($Userprofile){
                        $Userprofile | Where-Object {($_.localpath -match 'c*users') -and ($_.Loaded)} | Select SID | ForEach-Object{
                            $SID = $_.SID    
                            $objSID = New-Object System.Security.Principal.SecurityIdentifier($SID)
                            $objUser = $objSID.Translate([System.Security.Principal.NTAccount])
                            $Username = $objUser.Value.Split("\")[1]
                            $table = @{"USER" = $Username;"SID" = $SID; "Start Page" = "n/a"; "Computername" = "$Client"}
                            $currentUser = New-Object psobject -property $table
                            $Script:Database.$client += @{"$username" = $currentUser}
                        }
                    }
                }
                
                "Windows XP Professional"{
                    Try{$Userprofile = (Get-WmiObject -computername $Client -Query "Select UserName from Win32_ComputerSystem").UserName}
                    Catch{
                        Write-Host $Client
                        Write-Error $_
        			    $Userprofile = $null
        			}
                    If($Userprofile){
                        $Domain = $Userprofile.Split("\")[0]
                        $Username = $Userprofile.Split("\")[1]
                        $objUser = New-Object System.Security.Principal.NTAccount($Domain, $Username)
                        $SID = ($objUser.Translate([System.Security.Principal.SecurityIdentifier])).Value

                        $table = @{"USER" = $Username;"SID" = $SID; "Start Page" = "n/a"; "Computername" = "$Client"}
                        $currentUser = New-Object psobject -property $table
                        $Script:Database.$client += @{"$username" = $currentUser}
                    }
                    
                }
                
                "Default"{Break}
            
            }
            $Userdata = ($Script:Database.$Client | ForEach-Object {$_.Values})
            Foreach($User in $Userdata){
                Try{
                    $SID = $User.SID
                    $objSID = New-Object System.Security.Principal.SecurityIdentifier($SID)
                    $objUser = $objSID.Translate( [System.Security.Principal.NTAccount])  
                    $Username = $objUser.Value.Split("\")[1]
                    $reg = [Microsoft.Win32.RegistryKey]::OpenRemoteBaseKey('USERS', $Client)
                    $regkey = $reg.OpenSubkey("$SID\\Software\\Microsoft\\Internet Explorer\\Main")
                    $StartPage = $regkey.GetValue("Start Page")
                        
                    If($StartPage.GetType().Name -ne "String"){Throw "Value not String"}

                    $Script:Database.$Client.$Username.'Start Page' = $StartPage

                }Catch{
                    Write-Host $Client
                    Write-Error $_
                    $Script:Database.$Client.$Username.'Start Page' = "Unknown"
                }
            }
			Try{
				$Script:Database | ForEach-Object{$_.$Client.Values} | ForEach-Object{
					$SID = $_.SID
					$Computername = $_.Computername
					$User = $_.USER
					$StartPage = $_.'Start Page'
					"$SID,$Computername,$User,$StartPage"
				} | Out-File $output -Append
			}
			Catch{Write-Host $Client;Write-Error $_;Break}
				
			Try{
				$TimeStamp = Get-Date -Format "yyyyMMdd:HH.mm.ss"
				"$TimeStamp,$Client,Successful" | Out-File $Progress -Append
				$Script:Database.Status += @{"$Client" = $True}
			}
			Catch{Write-Host $Client;Write-Error $_;Break}
        }
        ElseIf(($Status -eq "Successful") -or ($Status -eq "Skipped")){
            #Do nothing. Already Successful.
            $Script:Database.Status += @{"$Client" = $True}
        }
        Else{
            Try{
                $Script:Database.Status += @{"$Client" = $false}
                $TimeStamp = Get-Date -Format "yyyyMMdd:HH.mm.ss"
                "$TimeStamp,$Client,Failed" | Out-File $Progress -Append
            }
            Catch{Write-Host $Client;Write-Error $_}
        }
        
        $Script:Percentage = 100/$ComputerCount
        $Script:Completed = ($Script:Database.Status.Values | Where-Object{$_ -Match $true}).Count
        If(!$Script:Completed){$Script:Completed = 0}
        $Script:currentpercentage = $Script:Percentage*$Script:Completed
        
        write-progress -Activity "Collecting Startpage information." -ID 1 -Status "Processing $Client. Completed count: $Script:Completed/$Script:ComputerCount" -PercentComplete $Script:currentpercentage
    }
}


[Void](Get-Startpage -output $Output -Progress $Progress)
If($Loop){
    While(($Database.status.Values  | Where-Object{$_ -Match $false}) -eq $false){
        [Void](Get-Startpage -output $Output -Progress $Progress)
    }
}

Access CSC

this is used to quickly gain access to a users client side cache without ruining sync.
Used for backup issues and other stuff.

$comp = 'computer1' # Replace with computer
$FQDN = 'your.domain.com' # Replace with FQDN
$UserID = 'user1' # Replace with the useraccount to gain access

Function ConvertTo‐Base64($string) {
  $bytes  = [System.Text.Encoding]::Unicode.GetBytes($string);
  $encoded = [System.Convert]::ToBase64String($bytes); 
  Return $encoded;
}

$cmd = @'
if (schtasks.exe /Query | select-string "RUNCMD1") {
    schtasks.exe /delete /tn "RUNCMD1" /F
}
     
mkdir c:\temp\ -ea 0

"running"|out-file c:\temp\csctemp
schtasks.exe /ru "SYSTEM" /Create /TN "RUNCMD1" /TR "cmd /C icacls c:\windows\csc\ /c /grant {FQDN}\{UserID}`:`(OI`)`(CI`)F /T > c:\temp\cmd1.log && del c:\temp\csctemp" /sc "once" /ST "23:00"
schtasks.exe /run /tn "RUNCMD1"
'@.Replace('{FQDN}',$FQDN).Replace('{UserID}',$UserID)

$EncodedCmd = ConvertTo‐Base64($cmd)
$EncodedArgs = 'c:\windows\System32\WindowsPowerShell\v1.0\powershell.exe -ExecutionPolicy ByPass -EncodedCommand "' + $EncodedCmd + '"'
$result = Invoke-WmiMethod -ComputerName $comp -class win32_process -name create -ea 0 -ArgumentList $EncodedArgs

Add users as local admin on select computers

This is used to add users as local admin on a select group of clients.
Needs a input file specified or default to input.txt in same folder.
It converts Administrator SID to friendlyname to account for things such as foreign characters.

It uses WMI to invoke a client side script to be run under admin credentials in order to bypass PSremoting policies.

Content in input should be formatted as below.

Computername,Username
computer1,User1
computer2,User1
computer2,User2
This is used to add users as local admin on a select group of clients.
Needs a input file specified or default to input.txt in same folder.
It converts Administrator SID to friendlyname to account for things such as foreign characters.

It uses WMI to invoke a client side script to be run under admin credentials in order to bypass PSremoting policies.

Content in input should be formatted as below.
Computername,Username
computer1,User1
computer2,User1
computer2,User2

Script below:

[Cmdletbinding()]
Param(
    [String]$InputFile=".\input.txt"
)
$MyPath = Split-Path $MyInvocation.MyCommand.path -Parent
$Logpath = "$MyPath\Status.log"
$Ping = new-object Net.NetworkInformation.Ping
If(!(Test-path -Path $InputFile -PathType Leaf)){Throw{"Could not find $InputFile"}}
$Data = Import-Csv $InputFile -Delimiter ','

$Logpath = "$MyPath\Status.log"
If(!(Test-Path -Path $Logpath -PathType Leaf)){
    New-Item -Path $Logpath -ItemType file
    "Computername,Username,Status" | Out-File $Logpath
}
$LogData = Import-Csv $Logpath -Delimiter ','

Function ConvertTo-Base64($String){
    $Bytes = [System.Text.Encoding]::Unicode.GetBytes($String)
    $Encoded = [System.Convert]::ToBase64String($Bytes)
    Return $Encoded
}
$cmdtemplate = @'
$SID = "S-1-5-32-544" # built-in administrator group sid
# Find Administrator group based on SID and gets the friendlyname of account
$admins = New-object System.Security.Principal.SecurityIdentifier($SID)
[string]$adminNTaccount = $admins.Translate([System.Security.Principal.NTAccount]).Value.Split('\')[1]

# Gets a list of members and collects friendlyname of each member
$computer = [ADSI]('WinNT://.,computer')
$Group = $computer.psbase.children.find($adminNTaccount)
$Group.Add("WinNT://{replacewithuser},user") # Must change/replace user string here with real user account
'@

$data | Foreach-object{
    $Computer = $_.Computername
    $User = $_.Username
    $Cmd = $cmdtemplate -replace '{replacewithuser}',$User
    $EncodedCmd = ConvertTo-Base64($Cmd)
    $EncodedArgs = 'c:\windows\System32\WindowsPowerShell\v1.0\powershell.exe -ExecutionPolicy ByPass -EncodedCommand "' + $EncodedCmd + '"'
    Remove-Variable added -Force -ErrorAction Ignore
    $added = $LogData | Where-Object {($_.Computername -match $computer) -and ($_.Username -match $user) -and ($_.Status -match "Success")}
    $Status = ($Ping.Send($Computer)).Status
    If($added){
        Write-host "Already added $User to $Computer" -ForegroundColor Blue
    }
    ElseIf($Status -eq "Success"){
        $Result = Invoke-WmiMethod -ComputerName $Computer -class win32_process -name create -ErrorAction Ignore -ArgumentList $EncodedArgs
        Write-Verbose $Result
        If($Result.ReturnValue -eq 0){
            Write-host "Added $User to $Computer" -ForegroundColor Green
            $Returnvalue = "Success" 
        }
        Else{
            Write-Host "Failed to add $User to $Computer" -ForegroundColor Red
            $Returnvalue = "Error $($result.ReturnValue)"
        }        
        
    }
    Else{
        Write-Host "Could not contact $Computer" -ForegroundColor Red
        $Returnvalue = "Host Unreachable"
    }

    "$computer,$User,$Returnvalue" | Out-File -Append $Logpath


}
Write-host "Done!" -BackgroundColor Green -ForegroundColor Black
Write-host "Bash the keyboard to quit application!" -BackgroundColor Green -ForegroundColor Black
[void]$Host.UI.RawUI.ReadKey("NoEcho,IncludeKeyDown")

The one-liner maker!

I give you all, probably the most unused function on the planet. The “Make one-liner”!

It’s probably not even close to “Complete” but it is easy to add exceptions on where to format.

Function Main{
    $ScriptString = Get-Content [enter powershell script path here]
    Make-Oneliner $ScriptString
}

Function Make-Oneliner{
    Param([Array]$array)
    [string]$OneLiner = @()
    Foreach($Line1 in $array){
    
    $Line1 = $Line1.Split("#")[0]
    $Line1 = $Line1.Trim()
    Foreach($Line in $Line1){
        #Parse and add ;
            If(
                ($line.EndsWith("`{")) -or
                ($line.EndsWith("`;")) -or
                ($line.EndsWith("`(")) -or
                ($line.EndsWith("`|")) -or
                ($line.EndsWith("`]"))
            ){
                    $OneLiner += $Line
            }
            Elseif($line.EndsWith("`'")){
                if(($Line.Split("`'").Count % 1) -eq 0){
                    $Line = $Line + "`;"
                    $OneLiner += $Line
                    }
            }
            Elseif($line.EndsWith("`"")){
                if(($Line.Split("`"").Count % 1) -eq 0){
                    $Line = $Line + "`;"
                    $OneLiner += $Line
                    }
            }
            Elseif($Line.StartsWith("#")){
                # do nothing
            }
            Else{
                $Line = $Line + "`;"
                $OneLiner += $Line
            }
        }
    }
    # Cleanup inappropriate ";".
    $OneLiner = $OneLiner.Replace(';)',')').Replace(',;',',').Replace(';;',';').Replace(';;',';')

    # Cleanup inappropriate ";" on words using insensitive replace methods.
    $OneLiner = $OneLiner -ireplace '};else','}else'
    $OneLiner = $OneLiner -ireplace '};catch','}catch'
    $OneLiner = $OneLiner -ireplace '};finally','}finally'

    Return $OneLiner
}
Main

SQL queries in powershell

I was trying to query the SCCM database and sort the results with Powershell to get a full report on all the connections to a collection. To do this i needed to inject a SELECT query and retrieve an object from the database which proved to be a bit difficult. Below is how i resolved it.

To connect to a SQL you need a client, in powershell and .NET we have the System.Data.SQLClient namespace

$connection = New-Object System.Data.SqlClient.SqlConnection

Then i needed a connection string to open the database.

$connection.ConnectionString = "$ConnectionString"
$connection.Open()

Now we need to inject the command we want. Fairly simple, we create a SQLCommand and connect it to the already established connection.

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

Then we insert our command

$Command.CommandText = $SQLQuery

What we do after is execute the command and create a counter of the columns in the result.

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

Now we create the DataTable and use the counter to populate the columns. We name them after each column in the query.

$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 the Reader is active it processes a loop for the length of counter and ejects the results into new rows in the DataTable.
To get the correct formatting, we place everything into a string and format accordingly. Name and value.
We then use the Invoke-Expression cmdlet to execute the formatted string and add values to the rows

while ($Reader.Read()) {
    $Data = @()
    for ($i = 0; $i -lt $Counter; $i++) {
        $Data += [String]$Reader.GetValue($i)
    }
    $DataTable.Rows.Add($Data)
}

Last but not least, we close the reader and the connection to the database and output the finished table

$Reader.Close()
$Connection.Close()
   
Return $DataTable

And we’re done!

The finished function will now look like this:

Function Get-SQLQuery{
    Param(
        [Parameter(Mandatory=$true)]
        [String]$SQLQuery,
        [Parameter(Mandatory=$true)]
        [String]$ConnectionString
    )
    $connection = New-Object System.Data.SqlClient.SqlConnection
    $connection.ConnectionString = "$ConnectionString"
    $connection.Open()
    $Command = New-Object System.Data.SQLClient.SQLCommand
    $Command.Connection = $Connection
    $Command.CommandText = $SQLQuery
    $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()) {
        $Data = @()
        for ($i = 0; $i -lt $Counter; $i++) {
            $Data += [String]$Reader.GetValue($i)
        }
        $DataTable.Rows.Add($Data)
    }
    $Reader.Close()
    $Connection.Close()
    
    Return $DataTable
}

First set your connectionstring and Select query then invoke the command like this:

Get-SQLQuery -ConnectionString $connectionstring -SQLQuery $SelectQuery

Examples of connectionstrings can be found at Connectionstrings.com
The usual string is the trusted which uses your credentials

If you are using the function a lot, concider adding it to your Powershell profile. For more information check out my previous post on WGET and profiles