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(DefaultParameterSetName = 'Name')] param ( [Parameter(ParameterSetName = 'Name')] # The names or hostnames of the computers you would like to retrieve data for. [String[]]$Name, [Parameter(ParameterSetName = 'Id')] # The ComputerIDs you would like to retrieve data for. [UInt32[]]$Id, [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 # Remove Computers from the list of tables if it is not the only table. if ( ('Computers' -in $Table) -and ($Table.Count -gt 1) ) { $Table = $Table | Where-Object { $_ -ne 'Computers' } } $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;" # Do not join the Computers table to itself. if ( $Table[0] -ne 'Computers' ) { $Query = $Query -replace ';', ' INNER JOIN Computers USING (ComputerId);' } # Execute the query. if ( $Name -or $Id ) { $Query = $Query -replace ';', ' WHERE ComputerId = @ComputerId;' # Build a hashtable so IDs can be translated back to names. $ComputerIds = @{} if ( $Name ) { # Look up the computer names. foreach ( $DesiredName in $Name ) { $ComputerId = Resolve-PdqInventoryComputerName -Name $DesiredName $ComputerIds.Add($ComputerId, $DesiredName) } } else { foreach ( $DesiredId in $Id ) { $ComputerIds.Add($DesiredId, $null) } } # Query data for each computer. foreach ( $Enumerator in $ComputerIds.GetEnumerator() ) { $ComputerId = $Enumerator.Key $ComputerName = $Enumerator.Value $Params = @{ 'ComputerId' = $ComputerId } $ComputerData = Invoke-SqlQuery -Query $Query -ConnectionName 'Inventory' -Stream -Parameters $Params if ( (-not $ComputerData) -and (-not $AllowNull) ) { if ( $ComputerName ) { $NameForError = $ComputerName } else { $NameForError = $ComputerId } throw "No data was found in the $Table table for: $NameForError." } $ComputerData } } else { Invoke-SqlQuery -Query $Query -ConnectionName 'Inventory' -Stream } Write-Verbose $Query } finally { Close-PdqSqlConnection -Product 'Inventory' -CloseConnection $CloseConnection } } |