This post is part 3 in a 3 part series on building your own server inventory system using PowerShell.
- Staging the SQL database and tables (Part 1)
- Collecting server data and pushing to the SQL database (Part 2)
- Pulling the data from the SQL database into a UI (Part 3) (This article)
In this article, I am wrapping up my 3 part series on building a server inventory system using PowerShell and SQL. Over the course of the last articles, I have show you how to build the databases and tables required to store server information and then began the actual data collection and sending it to the SQL server for later review. Now I am going to take you through the process of building out and using the server inventory user interface made from PowerShell and XAML. I won’t show off every line of code as it will be rather large, but will take you through some key areas that I think are interesting.
The script that calls the UI is available here: https://github.com/proxb/ServerInventoryReport/blob/master/Start-ServerInventoryUI.ps1
Requirements
Before I go deep into the code, I wanted to lay out some of the requirements that I had when designing this UI to make it as usable as possible for folks other than just me.
- Show list of servers in a tree list view
- Be able to jump to a server using a keyboard shortcut and entering server name
- Use of tabs to switch between types of inventory groups
- Custom filter to display user defined data
- Filters should persist between tabs so nothing is lost when switching
- Filters can be cleared with a button push
- Create an Excel spreadsheet of inventory on demand with user selectable options
There may be a few more things that I have left out, but these cover most of the main items that I wanted to have on this UI.
Creating the UI
I build the front end UI using XAML and stepping through all of the controls in order to get the right look. If you look at the XAML code, you may notice that there are a lot of missing things in it such as Tabs and DataGrid for each tab. This is because I need to dynamically create them later on when I begin making the connection to the SQL server.
[xml]$xaml = @" <Window xmlns='http://schemas.microsoft.com/winfx/2006/xaml/presentation' xmlns:x='http://schemas.microsoft.com/winfx/2006/xaml' x:Name='AboutWindow' Title='Help' Width = '400' Height = '670' WindowStartupLocation = 'CenterScreen' ShowInTaskbar = 'False' ResizeMode="NoResize"> <StackPanel> <TextBlock FontWeight = 'Bold' FontSize = '20' TextDecorations="Underline">Keyboard Shortcuts</TextBlock> <Label /> <TextBlock FontSize = '14' Padding = '0' Text = 'CTRL+G - Go to specified computer' /> <TextBlock FontSize = '14' Padding = '0' Text = 'CTRL+R - Generate Excel Report' /> <TextBlock FontSize = '14' Padding = '0' Text = 'CTRL+E - Exit application' /> <TextBlock FontSize = '14' Padding = '0' Text = 'CTRL+A - Go to All Computers' /> <TextBlock FontSize = '14' Padding = '0' Text = 'F1 - Displays help information' /> <TextBlock FontSize = '14' Padding = '0' Text = 'F5 - Applies filter' /> <TextBlock FontSize = '14' Padding = '0' Text = 'F8 - Clears filter' /> <Label /> <TextBlock FontWeight = 'Bold' FontSize = '20' TextDecorations="Underline">Filter Definitions</TextBlock> <Label /> <TextBlock FontWeight = 'Bold' FontSize = '14' Padding = '0' Text = "= (EQUAL)" /> <TextBlock FontSize = '14' Padding = '0' Text = "Used to find exact matches." /> <Label /> <TextBlock FontWeight = 'Bold' FontSize = '14' Padding = '0' Text = "LIKE" /> <TextBlock FontSize = '14' Padding = '0' Text = "Used to find approximate matches using wildcards (* or %)." /> <Label /> <TextBlock FontWeight = 'Bold' FontSize = '14' Padding = '0' Text = "NOT" /> <TextBlock FontSize = '14' Padding = '0' Text = "Used to find opposite of filter. Placed before the Column name in filter" TextWrapping = 'Wrap'/> <Label /> <TextBlock FontWeight = 'Bold' FontSize = '14' Padding = '0' Text = "< (LESS THAN)" /> <TextBlock FontSize = '14' Padding = '0' Text = "Used to find numbers smaller than given value" TextWrapping = 'Wrap'/> <Label /> <TextBlock FontWeight = 'Bold' FontSize = '14' Padding = '0' Text = "> (GREATER THAN)" /> <TextBlock FontSize = '14' Padding = '0' Text = "Used to find numbers larger than given value" TextWrapping = 'Wrap'/> <Label /> <TextBlock FontWeight = 'Bold' FontSize = '14' Padding = '0' Text = "<= (LESS THAN OR EQUAL)" /> <TextBlock FontSize = '14' Padding = '0' Text = "Used to find numbers equal to or smaller than given value" TextWrapping = 'Wrap'/> <Label /> <TextBlock FontWeight = 'Bold' FontSize = '14' Padding = '0' Text = ">= (GREATER THAN OR EQUAL)" /> <TextBlock FontSize = '14' Padding = '0' Text = "Used to find numbers equal to or larger than given value" TextWrapping = 'Wrap'/> <Label /> <TextBlock FontWeight = 'Bold' FontSize = '14' Padding = '0' Text = "IS NULL" /> <TextBlock FontSize = '14' Padding = '0' Text = "Used to find columns with no data" TextWrapping = 'Wrap'/> <Label /> <Button x:Name = 'CloseButton' Width = '100'> Close </Button> </StackPanel> </Window> "@ #Load XAML $reader=(New-Object System.Xml.XmlNodeReader $xaml) $AboutWindow=[Windows.Markup.XamlReader]::Load( $reader )
User Defined Variables
This is really the only area that you should have to make and updates to the code. Defining the SQL server that you will be connecting to and then defining the table names that exist on the database which will help in building out more of the UI controls.
#region User Defined Variables $SQLServer = 'vsql' ## Update Tables to include to new tables added to SQL so UI controls will be auto generated at runtime $Script:Tables = 'tbGeneral','tbOperatingSystem', 'tbNetwork','tbMemory','tbProcessor','tbUsers','tbGroups', 'tbDrives','tbAdminShare','tbUserShare','tbServerRoles','tbSoftware','tbScheduledTasks','tbUpdates','tbServices' #endregion User Defined Variables
Creating Help
Having a UI that does things is only as good as the help which you provide. That being the case, I wanted to have a small help window that would highlight a few things that the user might need to know such as keyboard shortcuts and some Filter definitions.
Function Show-AboutHelp { $rs=[RunspaceFactory]::CreateRunspace() $rs.ApartmentState = "STA" $rs.ThreadOptions = "ReuseThread" $rs.Open() $ps = [PowerShell]::Create() $ps.Runspace = $rs $ps.Runspace.SessionStateProxy.SetVariable("pwd",$pwd) [void]$ps.AddScript({ [xml]$xaml = @" <Window xmlns='http://schemas.microsoft.com/winfx/2006/xaml/presentation' xmlns:x='http://schemas.microsoft.com/winfx/2006/xaml' x:Name='AboutWindow' Title='Help' Width = '400' Height = '670' WindowStartupLocation = 'CenterScreen' ShowInTaskbar = 'False' ResizeMode="NoResize"> <StackPanel> <TextBlock FontWeight = 'Bold' FontSize = '20' TextDecorations="Underline">Keyboard Shortcuts</TextBlock> <Label /> <TextBlock FontSize = '14' Padding = '0' Text = 'CTRL+G - Go to specified computer' /> <TextBlock FontSize = '14' Padding = '0' Text = 'CTRL+R - Generate Excel Report' /> <TextBlock FontSize = '14' Padding = '0' Text = 'CTRL+E - Exit application' /> <TextBlock FontSize = '14' Padding = '0' Text = 'CTRL+A - Go to All Computers' /> <TextBlock FontSize = '14' Padding = '0' Text = 'F1 - Displays help information' /> <TextBlock FontSize = '14' Padding = '0' Text = 'F5 - Applies filter' /> <TextBlock FontSize = '14' Padding = '0' Text = 'F8 - Clears filter' /> <Label /> <TextBlock FontWeight = 'Bold' FontSize = '20' TextDecorations="Underline">Filter Definitions</TextBlock> <Label /> <TextBlock FontWeight = 'Bold' FontSize = '14' Padding = '0' Text = "= (EQUAL)" /> <TextBlock FontSize = '14' Padding = '0' Text = "Used to find exact matches." /> <Label /> <TextBlock FontWeight = 'Bold' FontSize = '14' Padding = '0' Text = "LIKE" /> <TextBlock FontSize = '14' Padding = '0' Text = "Used to find approximate matches using wildcards (* or %)." /> <Label /> <TextBlock FontWeight = 'Bold' FontSize = '14' Padding = '0' Text = "NOT" /> <TextBlock FontSize = '14' Padding = '0' Text = "Used to find opposite of filter. Placed before the Column name in filter" TextWrapping = 'Wrap'/> <Label /> <TextBlock FontWeight = 'Bold' FontSize = '14' Padding = '0' Text = "< (LESS THAN)" /> <TextBlock FontSize = '14' Padding = '0' Text = "Used to find numbers smaller than given value" TextWrapping = 'Wrap'/> <Label /> <TextBlock FontWeight = 'Bold' FontSize = '14' Padding = '0' Text = "> (GREATER THAN)" /> <TextBlock FontSize = '14' Padding = '0' Text = "Used to find numbers larger than given value" TextWrapping = 'Wrap'/> <Label /> <TextBlock FontWeight = 'Bold' FontSize = '14' Padding = '0' Text = "<= (LESS THAN OR EQUAL)" /> <TextBlock FontSize = '14' Padding = '0' Text = "Used to find numbers equal to or smaller than given value" TextWrapping = 'Wrap'/> <Label /> <TextBlock FontWeight = 'Bold' FontSize = '14' Padding = '0' Text = ">= (GREATER THAN OR EQUAL)" /> <TextBlock FontSize = '14' Padding = '0' Text = "Used to find numbers equal to or larger than given value" TextWrapping = 'Wrap'/> <Label /> <TextBlock FontWeight = 'Bold' FontSize = '14' Padding = '0' Text = "IS NULL" /> <TextBlock FontSize = '14' Padding = '0' Text = "Used to find columns with no data" TextWrapping = 'Wrap'/> <Label /> <Button x:Name = 'CloseButton' Width = '100'> Close </Button> </StackPanel> </Window> "@ #Load XAML $reader=(New-Object System.Xml.XmlNodeReader $xaml) $AboutWindow=[Windows.Markup.XamlReader]::Load( $reader ) #Connect to Controls $CloseButton = $AboutWindow.FindName("CloseButton") $AuthorLink = $AboutWindow.FindName("AuthorLink") $CloseButton.Add_Click({ $AboutWindow.Close() }) #Show Window [void]$AboutWindow.showDialog() }).BeginInvoke() }
Icon Image for Buttons
For some of my buttons, such as the Excel report, I wanted to show more than just a dull grey button and instead looked at grabbing some images from the icons which would represent best the way that the button was being used.
I looked at one of my older blog posts for the way to get the image from an icon and translate it into a value that could be used in WPF, in this case a Base64 encoded string: https://learn-powershell.net/2016/01/18/getting-the-icon-from-a-file-using-powershell/
I could then use that string and supply it to the button as a BitmapImage.
$ExcelIcon = 'AAABAAEAICAQAgAAAADoAgAAFgAAACgAAAAgAAAAQAAAAAEABAAAAAAAgA IAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAACAAACAAAAAgIAAgAAAAIAAgACAgAAAgICAAM DAwAAAAP8AAP8AAAD//wD/AAAA/wD/AP//AAD///8AAAAAAAAAAAAAAAAAAAAAAAAAAAA AAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAIiIiIiIiIiIiIiIiIiIgAC////////// ////////IAAv//////iP//+Id4//diAAL/////////iPiP/4//YgAC////////iIj//4d 4/3IAAvj/////+I///4h4j/+CAAL4////////+I+I//j/8gAC+P//////+IiP//h3j/cA A/iDMzMyKPhzM4+IiP/4AAP4hzMzIij3Mzf4j/+I/wAD+IhzMyIogzM///+Hd/+AA/iIg yInj3Mzj/+IiI//8AP4iPciJvd3d/iIiP////AD+Ij/YiJ3d3/4j////4YAA/iIj4Iid3 eP////+HJyAAP4iI/3J3d3////hyZ38gAD+IiP/3d3f//4d3d3d/IAA/iIiP93d3j4d3d 3d3fyAAP4iIj3d3dm93d3d3d38gAD+IiIh3d2Zn93d3d3d/IAA/iIiHN3hmZo+IiIh3fy AAP/iIczN/dmZviIiIiI8gADj4iDMz/4dmZ/iIiIiPIAA3+IczOPiIZmaPiIiIjyAAM4+ IiIiIiIiIiIiIiI8gAAN/+IiIiIiIiIiIiIiPIAAAN4//iIiIiIiIiIiIjyAAAAM3j/// //////////8gAAAAMzMzMzMzMzMyIiIiIAD/////AAAAAQAAAAEAAAABAAAAAQAAAAEAA AABAAAAAQAAAAEAAAABAAAAAQAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAQAAAAEAAAABAA AAAQAAAAEAAAABAAAAAQAAAAEAAAABAAAAAQAAAAEAAAABgAAAAcAAAAHgAAAB8AAAAQ==' $ExcelBytes = [convert]::FromBase64String($ExcelIcon) $ExcelBitMap = New-Object System.Windows.Media.Imaging.BitmapImage $ExcelBitMap.BeginInit() $ExcelBitMap.StreamSource = [System.IO.MemoryStream]$ExcelBytes $ExcelBitMap.EndInit() $UIHash.Excel.Source = $ExcelBitMap $UIHash.Clear.Source = $ClearBitMap
Excel Report Gotcha: Columns
In my planning, I didn’t think that I would have more than 26 (A-Z) columns in my Excel report for an given column. When my reports started to show blank data, I started my investigation and found that my UI columns were more than 26 and would then go into the AA, AB, etc… column labels in Excel which I hadn’t accounted for. I ended up making some changes in how I created the Column letter to Number hash table by accounting for numbers greater than 26.
$_Number = 0 $Previous = 0 $Script:Letters = [hashtable]::Synchronized(@{}) $Range = 97..122 For ($n=0;$n -le 5; $n++) { For ($i=1;$i -le $Range.count; $i++) { $_Number = $Previous + $i If ($n -ne 0) { $Script:Letters[$_Number] = "$([char]($Range[$n-1]))$([char]($Range[$i-1]))" } Else { $Script:Letters[$_Number] = [char]($Range[$i-1]) } } $Previous = $_number }
Dynamically Build Controls
Building most of the UI up front allowed me to at least see how things are going to look, but due to the possibility of having more tables in my database for collecting information, I didn’t want to hard code a set number of tabs, menus and datagrids. To handle this stuff, I created helper functions for each of these items that are used later on once I have determined the number of tables in the database (this is actually defined in the beginning of the code as noted in the User Defined Variables region.)
I won’t show all of the code as it is over 200 lines but will show you the first few lines that show off the builds of some of the controls.
Write-Verbose "Running Window Loaded Event" ForEach ($Table in $Tables) { $UIHash["$($Table)_tab"] = New-Tab -TabControl $UIHash.TabControl -Name "$($Table)_tab" -Header $Table.Substring(2) $UIHash["$($Table)_datagrid"] = New-DataGrid -Tab $Tab -Name "$($Table)_datagrid" $UIHash["ParentContextMenu"] = New-ContextMenu $UIHash["AddToFilter_AND_$($Table)_menu"] = New-MenuItem -Name "AddToFilter_AND_$($Table)_menu" -Header "Add to AND Filter" -Visibility Collapsed $UIHash["AddToFilter_OR_$($Table)_menu"] = New-MenuItem -Name "AddToFilter_OR_$($Table)_menu" -Header "Add to OR Filter" -Visibility Collapsed [void]$UIHash["ParentContextMenu"].AddChild($UIHash["AddToFilter_AND_$($Table)_menu"]) [void]$UIHash["ParentContextMenu"].AddChild($UIHash["AddToFilter_OR_$($Table)_menu"]) #region AddToFilter (EQUAL) $UIHash["AddToFilter_Equal_$($Table)_menu"] = New-MenuItem -Name "AddToFilter_Equal_$($Table)_menu" -Header "Add to Filter (EQUAL)" $UIHash["AddToFilter_Equal_$($Table)_menu"].Add_Click({ $UIHash.Filter_txtbx.Text = "$ColumnName = '$Value'" }) #endregion AddToFilter (EQUAL) ...
This leads into the building of the context menus to help with filtering our data on each DataGrid. I added this feature so an everyday user would just right click on an item and pick the type of filtering criteria to use. Subsequent filtering approaches would be either an AND or an OR to ensure the proper type of filtering takes place as shown in the GIF below.
Generating an Excel Report
While it is nice to have a UI that you can work with, other times you might want to have a report that can be sent via email or printed out. By clicking on the Excel icon at the upper left hand side, you are treated to some options on what kind of data you want on the report by picking the tabs that will be available on the excel workbook. Keep in mind that any filters which are in place will for the display will be brought into the report. If you are picking any other system from the tree view on the left, then only that system will be included in the report so it is recommended that you select All prior to generating a report.
The results is a great Excel report to use as you need.
The code that does all of the work is below:
Function Invoke-ExcelReport { $Script:ExcelReports.Clear() $Return = $ExcelReport.Invoke() If ($Return -AND $Script:ExcelReports.Count -gt 0) { $UIHash.ProgressBar.Maximum = $ExcelReports.Count ## Begin runspace initialization $newRunspace =[runspacefactory]::CreateRunspace() $newRunspace.ApartmentState = "STA" $newRunspace.ThreadOptions = "ReuseThread" $newRunspace.Open() $newRunspace.SessionStateProxy.SetVariable("uiHash",$uiHash) $newRunspace.SessionStateProxy.SetVariable("ExcelReports",$ExcelReports) $newRunspace.SessionStateProxy.SetVariable("ExcludeProperties",$ExcludeProperties) $newRunspace.SessionStateProxy.SetVariable("ReportPath",$ReportPath) $newRunspace.SessionStateProxy.SetVariable("Letters",$Letters) $PowerShell = [PowerShell]::Create().AddScript({ Add-Type –assemblyName System.Windows.Forms Function Show-ReportLocation { Param($ReportLocation) $title = "Report Completed" $message = "The report has been saved to: $ReportLocation" $button = [System.Windows.Forms.MessageBoxButtons]::OK $icon = [Windows.Forms.MessageBoxIcon]::Information [windows.forms.messagebox]::Show($message,$title,$button,$icon) } Function ConvertTo-MultidimensionalArray { [cmdletbinding()] Param ( [parameter(ValueFromPipeline)] [object]$InputObject = (Get-Process), [parameter()] [ValidateSet('AliasProperty','CodeProperty','ParameterizedProperty','NoteProperty','Property','ScriptProperty','All')] [string[]]$MemberType = 'Property', [string[]]$PropertyOrder ) Begin { If (-NOT $PSBoundParameters.ContainsKey('Data')) { Write-Verbose 'Pipeline' $isPipeline = $True } Else { Write-Verbose 'Not Pipeline' $isPipeline = $False } $List = New-Object System.Collections.ArrayList $PSBoundParameters.GetEnumerator() | ForEach { Write-Verbose "$($_)" } } Process { If ($isPipeline) { $null = $List.Add($InputObject) } } End { If ($isPipeline) { $InputObject = $List } $rowCount = $InputObject.count If ($PSBoundParameters.ContainsKey('PropertyOrder')){ $columns = $PropertyOrder } Else { $columns = $InputObject | Get-Member -MemberType $MemberType | Select -Expand Name } $columnCount = $columns.count ##Create data holder $MultiArray = New-Object -TypeName 'string[,]' -ArgumentList ($rowCount+1),$columnCount ##Add information to object #Columns first $col=0 $columns | ForEach { $MultiArray[0,$col++] = $_ } $col=0 $row=1 For ($i=0;$i -lt $rowCount;$i++) { $columns | ForEach { $MultiArray[$row,$col++] = $InputObject[$i].$_ } $row++ $col=0 } ,$MultiArray } } $uiHash.Window.Dispatcher.Invoke("Background",[action]{ [System.Windows.Input.Mouse]::OverrideCursor = [System.Windows.Input.Cursors]::Wait $UIHash.ExportToExcel.IsEnabled = $False $UIHash.Filter_btn.IsEnabled = $False $UIHash.ClearFilter_btn.IsEnabled = $False }) #$uiHash.host.UI.WriteVerboseLine("Generating report for $($Script:ExcelReports -join '; ')") #Create excel COM object $excel = New-Object -ComObject excel.application $workbook = $excel.Workbooks.Add() #Make Visible $excel.Visible = $False $excel.DisplayAlerts = $False $ToCreate = $Script:ExcelReports.Count - 3 #$uiHash.host.UI.WriteVerboseLine("ToCreate: $ToCreate") If ($ToCreate -gt 0) { 1..$ToCreate | ForEach { [void]$workbook.Worksheets.Add() } } ElseIf ($ToCreate -lt 0) { 1..([math]::Abs($ToCreate)) | ForEach { Try { $Workbook.worksheets.item(2).Delete() } Catch {} } } $i = 1 ForEach ($Table in $Script:ExcelReports) { #$uiHash.host.UI.WriteVerboseLine("Processing $Table") $uiHash.Window.Dispatcher.Invoke("Background",[action]{ $uiHash.status_txt.text = "Generating Report: $(($Table).SubString(2))" $UIHash.ProgressBar.Value++ }) $uiHash.Window.Dispatcher.Invoke("Background",[action]{ $Global:DataGrid = $UIHash."$($Table)_Datagrid" }) $Properties = $DataGrid.ItemsSource[0].psobject.properties|Where{ $ExcludeProperties -notcontains $_.Name } | Select-Object -ExpandProperty Name #$uiHash.host.UI.WriteVerboseLine("Properties: $($Properties -join '; ')") $RowCount = $DataGrid.ItemsSource.Count+1 $ColumnCount = ($DataGrid.ItemsSource | Get-Member -MemberType Property).Count #$uiHash.host.UI.WriteVerboseLine("Column Count: $ColumnCount | Row Count: $RowCount" ) $uiHash.Window.Dispatcher.Invoke("Background",[action]{ $Global:__Data = $DataGrid.ItemsSource }) $Data = $__Data|ConvertTo-MultidimensionalArray -PropertyOrder $Properties #$uiHash.host.UI.WriteVerboseLine("Data: $($Data|Out-String)") $serverInfoSheet = $workbook.Worksheets.Item($i) [void]$serverInfoSheet.Activate() $serverInfoSheet.Name = $Table.Substring(2) #$uiHash.host.UI.WriteVerboseLine(("Range: {0}, {1}" -f "A1","$($Letters[$ColumnCount])$($RowCount)")) $Range = $serverInfoSheet.Range("A1","$($Letters[$ColumnCount])$($RowCount)") $Range.Value2 = $Data $UsedRange = $serverInfoSheet.UsedRange $UsedRange.Value2 = $UsedRange.Value2 [void]$workbook.ActiveSheet.ListObjects.add( 1,$workbook.ActiveSheet.UsedRange,0,1) [void]$usedRange.EntireColumn.AutoFit() [void]$usedRange.EntireRow.AutoFit() $i++ } #Save the report Write-Verbose "Saving to $($Script:ReportPath)" $workbook.SaveAs(($Script:ReportPath) -f $pwd) #Quit the application $excel.Quit() #Release COM Object [System.Runtime.InteropServices.Marshal]::ReleaseComObject([System.__ComObject]$excel) | Out-Null [gc]::Collect() [gc]::WaitForPendingFinalizers() Show-ReportLocation -ReportLocation $Script:ReportPath $uiHash.Window.Dispatcher.Invoke("Background",[action]{ [System.Windows.Input.Mouse]::OverrideCursor = $Null $UIHash.ExportToExcel.IsEnabled = $True $UIHash.Filter_btn.IsEnabled = $True $UIHash.ClearFilter_btn.IsEnabled = $True $uiHash.status_txt.text = $Null $UIHash.ProgressBar.Value = 0 }) }) $PowerShell.Runspace = $newRunspace [void]$Jobs.Add(( [pscustomobject]@{ PowerShell = $PowerShell Runspace = $PowerShell.BeginInvoke() } )) } }
There are many other areas that I could highlight, but that may have to be another time. In the meantime, head on out to the GitHub page (listed below) for my ServerInventory project and let me know what you think! As always, if you want to contribute to this project, just fork the repo over to your page, make some updates and submit a Pull Request.