PowerShell Server Inventory, Part 3: Viewing the Inventory using a UI

This post is part 3 in a 3 part series on building your own server inventory system using PowerShell.

image

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.

image

 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
}

image

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.

ServerInventoryFilter

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.

ServerInventoryExcelReport

The results is a great Excel report to use as you need.

ExcelReport

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.

ServerInventory GitHub

https://github.com/proxb/ServerInventoryReport

Posted in powershell | Tagged , , , , , , | Leave a comment

Speaking at the Tampa PowerShell User Group

On May 18th, I will be speaking remotely at the Tampa PowerShell User Group talking about using Runspaces. I don’t know if this will be open for remote users, but if you are local to the Tampa area, then feel free to stop by and listen!

Information about meeting: https://www.meetup.com/Florida-IT-Professionals/events/237512826/

Posted in powershell | Tagged , , , | Leave a comment

PowerShell Server Inventory, Part 2: Collecting and Sending Data

This will be at least a 3 part series in which I will go over each aspect of my build which will cover the following topics:

Picking up where I left at, we have created our database and tables that will be used to store all of our data on our servers. Next up is to begin querying each server in the domain and pull as much information as we can and then send that data up to SQL so we can view it later on.

For this part of querying and sending data to the SQL server, I felt that it would be best to do some multithreading to speed up the time that it would take to hit all of the systems rather than perform the work sequentially. My multithreading approach of choice is to use my module, PoshRSJob to handle this. You can grab this module from my GitHub repo and place it on whichever server that you will be using to schedule the task from to perform the scans and uploads to SQL.

I say that you can scheduled a job to run the next script, but this is something that could be performed manually as well if need be and in fact, I would recommend this to ensure that no errors are thrown during the script execution.

The script that I wrote which will be used is called Invoke-ServerInventoryDataGathering.ps1 which only has a single parameter that accepts what SQL server will be used to send the data to. The script itself is over 1000 lines of which most of it consists of various regions which are used to group each type of data pull ranging from user account information to drive data to system configurations. In fact, about half of the code consists of helper functions which are used for data gathering or, in the case of Get-Server, is used to perform a domain wide lookup of all of the servers for a more dynamic approach of ensuring that all servers are being checked rather than using a file to host all of the systems.

image

There is a variable called $ServerGroup which is keeping a value of ‘MemberServer’. You can disregard this (keep it uncommented though) as I have my lab set up differently where Domain Admins do not have access to anything other than domain controllers which require a second script to be used to query the domain controllers and then write the data to the SQL server (which does allow domain admins to access for the purpose of writing the data to SQL).

Continuing to step through the code,  I have several regions which I use to group together each type of query. If more queries need to be added to grab more information, I can simply create a new region and add the necessary code to gather and send the data.

image

Each region follows the same type of process:

  1. Query the remote system for the particular information that I need
  2. If the query was successful, then proceed to perform a check against the database for data on the same system on the table that matches the type of data being queried for
  3. If data already exists, then remove the existing data
  4. Send the new data up to the appropriate table based on the current data being collected

Kicking off the script manually is just a matter of running it. In my case, VSQL is hard coded as the parameter value for the SQL server. In other cases, you would want to supply your own value.

image

I use Wait-RSJob with the –ShowProgress switch so I can track the status of each job. Each job represents a computer that is being scanned and the data gathered sent to a SQL database for later viewing.

image

Of course,  the preferred use of this script is by putting it into a scheduled task so it can be run on a daily schedule to handle all of the updates or new systems that come into the environment.

image

With all of this done, we can quickly verify that data actually exists. We can either look in SQL Server Management Studio for the data, or run a simple PowerShell command using the Invoke-SQL function against one of the tables.

image

PowerShell Example

$TSQL = @"
SELECT TOP 1000 [ComputerName]
      ,[Manufacturer]
      ,[Model]
      ,[SystemType]
      ,[SerialNumber]
      ,[ChassisType]
      ,[Description]
      ,[BIOSManufacturer]
      ,[BIOSName]
      ,[BIOSSerialNumber]
      ,[BIOSVersion]
      ,[InventoryDate]
  FROM [ServerInventory].[dbo].[tbGeneral]
"@

Invoke-SQLCmd -Computername VSQL -TSQL $TSQL -Database ServerInventory -CommandType Query

image

All of this code as well as the rest of the Server Inventory code is available at my GitHub repo here: https://github.com/proxb/ServerInventoryReport

The last part in this series will take us through building a tool that will pull the data from SQL and provide users with a graphical way to view and generate reports.

Posted in powershell | Tagged , , , , | 5 Comments

PowerShell Server Inventory, Part 1: SQL Database Build

If you’re like me, you might not have SCOM or SCCM or some other 3rd party solution that you can leverage to track all of your systems in your environment that you can pull up at a glance to know what software is on it or what local account might be created on it. Fortunately, with the combined forces of PowerShell and SQL, we can build up our own solution that can provide us this information. This is where I came up with a project to roll my own server inventory system.

ServerInventoryUI

This will be at least a 3 part series in which I will go over each aspect of my build which will cover the following topics:

This article will focus on the SQL database and table build using a few techniques. I consider this almost a living script as I have added and even removed a few things since its original inception. I opted to create tables for each section that will be inventoried. Currently, the sections are (in no particular order):

  • AdminShares – Holds the admin shares of a server
  • Drives – Holds the different drives on a server such as CD-ROMs and hard drives
  • General – Holds information that doesn’t really apply to the other tables
  • Groups – Holds local group information on the server
  • Memory – Holds the memory information of a server
  • Network – Holds networking configuration information of a server
  • OperatingSystem – Holds operating system information
  • Processor – Information about the processors on a server
  • ScheduledTasks – Lists all scheduled tasks on a server
  • ServerRoles – Lists all server roles, if applicable, on a server
  • Services – Lists all services on a server
  • Software – Lists all installed software on a server
  • Updates – Lists all installed updates on a server
  • Users – Lists all local users on a server
  • UserShares – Lists all user created shares on a server

Of course, this could change over time depending on various requirements, but I am hoping that for the most part, things stay the same and the only things that would need to be updated would be the content in each table such as adding or removing various properties. Adding new tables is as easy as copying the code from a different table creation region and making changes to add the new names and properties to include.

One last requirement that I had was that this data was only meant to last until the next run of the scheduled task which collects the data and sends it to SQL. This means that whatever that I had prior to the run will be replaced by the new data. Probably not the most efficient way to handle the data, but there is always room for improvement with this project.

The script that I wrote to help build not only the database, but the tables where I will push all of my data to is found at the following link: https://github.com/proxb/ServerInventoryReport/blob/master/Invoke-ServerInventorySQLBuild.ps1

I have defaulted the SQL server name to vSQL as that is my server in my lab environment so feel free to update it as needed or just call the script with the parameter –SQLServer and it will do the rest of the work for you! You can also see that I have my own version of Invoke-SQLCmd here. There are much better versions of this out in the wild so you can feel free to rip this one out and replace with a different one if you wish.

As for the column names and types, I tried to make it something that would ‘just work’ and also after some trial and error with figuring out a size for some of the data in the columns where I ran into errors when sending the data to SQL as something like a display name was longer than what was allowed in SQL. This might still be an issue for some of you depending on what you have running, but for that you can just the code and re-build the database.

Running the code is simple, just make sure that you point it to a valid SQL server and let it run!

.\Invoke-ServerInventorySQLBuild.ps1

image

It shouldn’t take too long to finish and after that, you can use SQL Server Management Studio (or whatever else you want to use) to validate that the database and tables have been created.

image

Part 2 will take us to performing the queries against remote server and sending the data collected up to the SQL server using a script that I wrote which will leverage my PoshRSJobs module for added multithreading.

Posted in powershell | Tagged , , , | 1 Comment

Speaking at Austin PowerShell User Group

image

This Thursday at 6pm CST I will be speaking at the Austin PowerShell User Group. I won’t actually be there (I wish I was there) but instead will be doing this remotely from my house. My topic will be on PowerShell runspaces which happens to be one of my favorite things to talk about.

This was supposed to have happened last month but I ended up being pretty sick and had to  reschedule. This week I am feeling great and looking forward to sharing my knowledge on PowerShell and runspaces!

Be sure to check out the link here for more information; https://www.meetup.com/Austin-PowerShell/events/237381901/

Posted in powershell | Tagged , , , , | Leave a comment