Skip to content

Instantly share code, notes, and snippets.

@toyg
Created November 2, 2017 12:06
Show Gist options
  • Save toyg/11e61d6bd118f83491e33bc6245f59ff to your computer and use it in GitHub Desktop.
Save toyg/11e61d6bd118f83491e33bc6245f59ff to your computer and use it in GitHub Desktop.
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
#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
Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment