List the name of all interface and volume custom properties

bhumphrey

Since SWQL doesn't allow us to select *, is there a SQL method for determining the name of all interface and volume custom properties. For instance, on node custom properties, we can do something like this:

$orionEnvironment = Get-SwisData -SwisConnection $swisConnection -Query "SELECT MachineName, OSVersion, TimeZone, SqlServer, SqlDatabase, SqlLogin FROM Orion.Environment"

$sqlQueryCustomPropertiesNode = "SELECT * FROM INFORMATION_SCHEMA.COLUMNS WHERE TABLE_NAME = N'NodesCustomProperties'"

$customPropertiesNodes = Invoke-Sqlcmd -ServerInstance $orionEnvironment.SqlServer -Database $orionEnvironment.SqlDatabase -Credential $credSQL -Query $sqlQueryCustomPropertiesNode | Select COLUMN_NAME | WHERE COLUMN_NAME -ne 'NodeID' | Sort COLUMN_NAME

But since the interfaces and volumes don't have their own dedicated table, I'm curious if there's another way to list these. I thought about using ORDINAL_POSITION on the INFORMATION_SCHEMA.COLUMNS, but that's going to change frequently between versions.

shuth

You can reference the Orion.CustomProperty table in SWQL. The following query will give you all the Interface and Volume properties. You can add Description to get the custom property description.

SELECT Field AS [Custom Property Name], 
CASE
 WHEN TargetEntity = 'Orion.NPM.InterfacesCustomProperties' THEN 'Interface'
 WHEN TargetEntity = 'Orion.VolumesCustomProperties' THEN 'Volume'
 END AS [Custom Property Type]
FROM Orion.CustomProperty
WHERE TargetEntity IN ('Orion.NPM.InterfacesCustomProperties', 'Orion.VolumesCustomProperties' )

This will show you all the fields:

SELECT TOP 1000 Table, Field, DataType, MaxLength, StorageMethod, Description, TargetEntity, Mandatory, Default, DisplayName, InstanceType, Uri, InstanceSiteId
FROM Orion.CustomProperty