SQL Queries

Asset information can be queried using the following SQL queries.

Get Namespace Information

select*from UA.f_Namespaces()

Get Flat List of Asset Types

select*from UA.f_AssetTypes(null,null)orderbyPath

Get Specific Asset Type

select*from UA.f_AssetTypes(null,null)wherepath='CapstoneAssetType\xyzType'

Note: replace “xyzType” with the full path to the desired type.

Get All Assets

select*from UA.f_Assets()

Get All Assets of Specific Type

select A.*

from UA.f_Assets() A

innerjoin UA.f_AssetTypes(null,null) T on A.TypeDefinitionNSI=T.AssetTypeNodeIdNSI and A.TypeDefinitionNodeIdNumeric=T.AssetTypeNodeIdNumeric

where T.path='CapstoneAssetType\xyzType'

orderby A.Path

Note: replace “xyzType” with the full path to the desired type.

Get Properties of a Specific Type

select*

from UA.f_AssetTypeProperties(15,4)

Note: replace “15,4” with the AssetTypeNodeIdNSI and AssetTypeNodeIdNumeric values for the desired type.

Get Properties of a Specific Asset

select*

from UA.f_AssetProperties()

where AssetNodeIdNSI=15 and AssetNodeIdNumeric=4

Note: replace “15,4” with the AssetNodeIdNSI and AssetNodeIdNumeric values for the desired type.