Public/Get-PdqInventoryComputerData.ps1
<#
.SYNOPSIS Retrieves all rows from the specified table for the specified computer names. .INPUTS None. .OUTPUTS System.Management.Automation.PSCustomObject System.Object[] .EXAMPLE Get-PdqInventoryComputerData -Name 'CEO-PC' -Table 'NetworkAdapters' -Columns 'Name', 'MacAddress' #> function Get-PdqInventoryComputerData { [CmdletBinding()] param ( # The names or hostnames of the computers you would like to retrieve data for. [String[]]$Name, [Parameter(Mandatory = $true)] [ArgumentCompleter( { param ($CommandName, $ParameterName, $WordToComplete, $CommandAst, $FakeBoundParameters) $Params = @{ 'Product' = 'Inventory' } if ( $FakeBoundParameters.ContainsKey('DatabasePath') ) { $Params += @{ 'DatabasePath' = $FakeBoundParameters.DatabasePath } } (Get-PdqDatabaseTable @Params).Name | Where-Object { $_ -like "$WordToComplete*" } })] # The database tables you would like to query. # This parameter supports tab-completion. # Hit CTRL+SPACE to see all tables, or type a few characters and hit TAB. [String[]]$Table, [ArgumentCompleter( { param ($CommandName, $ParameterName, $WordToComplete, $CommandAst, $FakeBoundParameters) $Params = @{ 'Product' = 'Inventory' } if ( $FakeBoundParameters.ContainsKey('DatabasePath') ) { $Params += @{ 'DatabasePath' = $FakeBoundParameters.DatabasePath } } if ( $FakeBoundParameters.ContainsKey('Table') ) { $Params += @{ 'Name' = $FakeBoundParameters.Table } (Get-PdqDatabaseTable @Params).Columns | Where-Object { $_ -like "$WordToComplete*" } } })] # The columns of the database table you would like to see. # This parameter supports tab-completion if you have already specified your table with -Table. # Hit CTRL+SPACE to see all columns, or type a few characters and hit TAB. [String[]]$Columns, # Don't throw an error if no data is returned. [Switch]$AllowNull, # The path to the currently active database will be retrieved by default. # You can use this parameter if you wish to run this function against a different database. [String]$DatabasePath ) try { $CloseConnection = Open-PdqSqlConnection -Product 'Inventory' -DatabasePath $DatabasePath $TableData = Get-PdqDatabaseTable -Product 'Inventory' -AsHashtable # Make sure at least 1 table has a ComputerId column. foreach ( $DesiredTable in $Table ) { $TableColumns = $TableData[$DesiredTable].Columns if ( 'ComputerId' -in $TableColumns ) { $FoundComputerId = $true } $AllTableColumns += $TableColumns } if ( -not $FoundComputerId ) { throw "The '$Table' table cannot be used in this function because it does not have a ComputerId column." } # Get all columns if -Columns was omitted. if ( -not $Columns ) { $Columns = (Get-PdqDatabaseTable -Name $Table -Product 'Inventory').Columns # Deduplicate the list. $Columns = ($Columns | Group-Object).Name } # Build the column list. # ComputerName and HostName are added so you can differentiate rows when multiple computers are specified. $ColumnList = "Computers.Name AS ComputerName, Computers.HostName" foreach ( $Column in $Columns ) { if ( $Column -notin $AllTableColumns ) { throw "None of the specified tables contain a column named '$Column'." } # Use table.column for the column name if it will conflict with the Computers table. if ( $Column -in $TableData.Computers.Columns ) { # Find the table that the column belongs to. foreach ( $DesiredTable in $Table ) { if ( $Column -in $TableData[$DesiredTable].Columns ) { $QualifiedName = $DesiredTable, $Column -join '.' break } } } else { $QualifiedName = $Column } $ColumnList = $ColumnList, $QualifiedName -join ', ' } # Build the portion of $Query that JOINS the tables. if ( $Table.Count -gt 1 ) { $PreviousTable = $Table[0] $TableList = "$PreviousTable" foreach ( $CurrentTable in $Table[1..$Table.Count] ) { $ColumnPool = $TableData[$PreviousTable].Columns + $TableData[$CurrentTable].Columns $PrimaryKey = ($ColumnPool | Group-Object | Where-Object 'Count' -eq 2).Name if ( -not $PrimaryKey ) { throw "Could not find the primary key for $PreviousTable and $CurrentTable." } if ( $PrimaryKey -isnot [String] ) { $PrimaryKey | Out-String | Write-Verbose throw "$PreviousTable and $CurrentTable have more than 1 matching column." } $TableList += " INNER JOIN $CurrentTable USING ($PrimaryKey)" $PreviousTable = $CurrentTable } } else { $TableList = $Table } $Query = "SELECT $ColumnList FROM $TableList INNER JOIN Computers USING (ComputerId);" Write-Verbose $Query # Execute the query. if ( $Name ) { $Query = $Query -replace ';', " WHERE ComputerId = @ComputerId;" # Look up the computer names. foreach ( $DesiredName in $Name ) { $ComputerId = Resolve-PdqInventoryComputerName -Name $DesiredName $Params = @{ 'ComputerId' = $ComputerId } $ComputerData = Invoke-SqlQuery -Query $Query -ConnectionName 'Inventory' -Stream -Parameters $Params if ( (-not $ComputerData) -and (-not $AllowNull) ) { throw "No data was found in the $Table table for: $DesiredName." } $ComputerData } } else { Invoke-SqlQuery -Query $Query -ConnectionName 'Inventory' -Stream } } finally { Close-PdqSqlConnection -Product 'Inventory' -CloseConnection $CloseConnection } } |