Public/Get-PdqInventoryComputerData.ps1
|
<#
.SYNOPSIS Retrieves all rows from the specified table for the specified computer names. .INPUTS None. .OUTPUTS System.Data.DataRow 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 table 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, [Parameter(Mandatory = $true)] [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' -Name $Table $TableColumns = $TableData.Columns if ( 'ComputerId' -notin $TableColumns ) { throw "The '$Table' table cannot be used in this function because it does not have a ComputerId column." } $ColumnsJoined = "Computers.Name AS ComputerName, Computers.HostName" foreach ( $Column in $Columns ) { if ( $Column -notin $TableColumns ) { throw "The '$Table' table does not contain a column named '$Column'." } $QualifiedName = $Table, $Column -join '.' $ColumnsJoined = $ColumnsJoined, $QualifiedName -join ', ' } $Query = "SELECT $ColumnsJoined FROM $Table INNER JOIN Computers USING (ComputerId);" if ( $Name ) { $Query = $Query -replace ';', " WHERE ComputerId = @ComputerId;" foreach ( $DesiredName in $Name ) { $ComputerId = Resolve-PdqInventoryComputerName -Name $DesiredName $Params = @{ 'ComputerId' = $ComputerId } $ComputerData = Invoke-SqlQuery -Query $Query -ConnectionName 'Inventory' -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' } } finally { Close-PdqSqlConnection -Product 'Inventory' -CloseConnection $CloseConnection } } |