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

    }

}