Query a Foundation database to retrieve EPM Component data from SQLServer. This uses the .NET SqlClient assembly, so will work from any .NET 2.0 computer

query_EPM_COMPONENTS.ps1

#requires -Version 1
$SQLconn = New-Object -TypeName System.Data.SqlClient.SqlConnection -ArgumentList ('server=ardy-sql01; database=foundation; uid=EPMSQL; pwd=Hyp3r10n')
$SQLconn.Open()
$SQLcmd = $SQLconn.CreateCommand()
$SQLcmd.CommandType = [System.Data.CommandType]::Text
$SQLcmd.CommandText = 
'SELECT COMPONENT_NAME, PROPERTY_NAME, PROPERTY_VALUE FROM HSS_COMPONENT_PROPERTY_VALUES inner join HSS_COMPONENT on HSS_COMPONENT_PROPERTY_VALUES.COMPONENT_ID = HSS_COMPONENT.COMPONENT_ID order by component_name asc'
$data = $SQLcmd.ExecuteReader()

$dataTable = New-Object -TypeName 'System.Data.DataTable'
$dataTable.Load($data)

$SQLconn.Close()

# Select the data we want to retreive
# Get all data, grouped by COMPONENT_NAME
$dataTable | Select-Object component_name, property_name, property_value | Sort-Object -Property component_name | Group-Object -Property component_name 

# or, Filter data based on COMPONENT_NAME(s)
$dataTable | Where-Object -FilterScript { $_.component_name -like '*WEB*APP' } | Format-Table -GroupBy Component_Name -Property Property_Name, Property_Value -AutoSize