www.blackholesurfer.com

Code: Select all

declare @fieldname nvarchar(75), @classname nvarchar(18), @fieldid nvarchar(32)
set @fieldname = 'Name'
set @classname = 'BMC.CORE:BMC_Computersystem'
-- SELECT max(len(Name)),max(len(classid)),max(len(attributedatasourcelist)) from BMC_CORE_BMC_ComputerSystem where AttributeDatasourcelist is NOT NULL and Datasetid = 'BMC.ASSET'
set @fieldid = '%'+(select cast(fieldid as nvarchar(32)) from field where schemaid = (select schemaid from arschema where name = @classname) and fieldname = @fieldname)+'%';

select 
substring(
SUBSTRING(
       substring(':'+cast(cs.attributedatasourcelist as nvarchar(1101)),1,patindex(@fieldid,':'+cast(cs.attributedatasourcelist as nvarchar(1101)))+len(@fieldid)-3), 
       LEN(substring('/'+cast(cs.attributedatasourcelist as nvarchar(1101)),1,patindex(@fieldid,'/'+cast(cs.attributedatasourcelist as nvarchar(1101)))+len(@fieldid)-3)) -  
       CHARINDEX('/',REVERSE(substring('/'+cast(cs.attributedatasourcelist as nvarchar(1101)),1,patindex(@fieldid,'/'+cast(cs.attributedatasourcelist as nvarchar(1101)))+len(@fieldid)-3))) + 2, 
       LEN(substring('/'+cast(cs.attributedatasourcelist as nvarchar(1101)),1,patindex(@fieldid,'/'+cast(cs.attributedatasourcelist as nvarchar(1101)))+len(@fieldid)-3))
       ),1,CHARINDEX(':',
       SUBSTRING(
       substring('/'+cast(cs.attributedatasourcelist as nvarchar(1101)),1,patindex(@fieldid,'/'+cast(cs.attributedatasourcelist as nvarchar(1101)))+len(@fieldid)-3), 
       LEN(substring('/'+cast(cs.attributedatasourcelist as nvarchar(1101)),1,patindex(@fieldid,'/'+cast(cs.attributedatasourcelist as nvarchar(1101)))+len(@fieldid)-3)) -  
       CHARINDEX('/',REVERSE(substring('/'+cast(cs.attributedatasourcelist as nvarchar(1101)),1,patindex(@fieldid,'/'+cast(cs.attributedatasourcelist as nvarchar(1101)))+len(@fieldid)-3))) + 2, 
       LEN(substring('/'+cast(cs.attributedatasourcelist as nvarchar(1101)),1,patindex(@fieldid,'/'+cast(cs.attributedatasourcelist as nvarchar(1101)))+len(@fieldid)-3))
       ))) AS List,
cs.Name,
cs.DatasetId
from BMC_CORE_BMC_ComputerSystem cs
join AST_ComputerSystem ac on cs.ReconciliationIdentity = ac.reconciliation_identity
-- join [dbo].[utf_EnumValues]('ast:attributes', 'assetlifecyclestatus') als on als.value = ac.assetlifecyclestatus 
join ast_attributes als on als.ReconciliationIdentity = cs.reconciliationidentity
where cs.DatasetId = 'BMC.ASSET'
--and cs.name not like 'k%'
--and cs.Type = 'Server'
--and cs.item not in ('Appliance','Blade Enclosure','Chassis','Console','Onboard Administrator','Other')
--and cs.name != 'Deactivated Computer'
--and cs.name != 'VM no longer being discovered'
--and cs.name != 'VM no longer in Vcenter'
and als.assetLifecycleStatus = '7'
and cs.AttributeDataSourceList IS NOT NULL

Code: Select all

WITH field_data AS (
    SELECT fieldid::TEXT AS fieldid
    FROM field
    WHERE schemaid = (SELECT schemaid FROM arschema WHERE name = 'BMC.CORE:BMC_Computersystem')
      AND fieldname = 'Name'
),
fieldid_pattern AS (
    SELECT '%' || fieldid || '%' AS fieldid
    FROM field_data
)
SELECT 
    SUBSTRING(
        SUBSTRING(
            SUBSTRING(':' || cs.attributedatasourcelist::TEXT, 1, POSITION(fieldid_pattern.fieldid IN (':' || cs.attributedatasourcelist::TEXT)) + LENGTH(fieldid_pattern.fieldid) - 3), 
            LENGTH(SUBSTRING('/' || cs.attributedatasourcelist::TEXT, 1, POSITION(fieldid_pattern.fieldid IN ('/' || cs.attributedatasourcelist::TEXT)) + LENGTH(fieldid_pattern.fieldid) - 3)) -  
            STRPOS(REVERSE(SUBSTRING('/' || cs.attributedatasourcelist::TEXT, 1, POSITION(fieldid_pattern.fieldid IN ('/' || cs.attributedatasourcelist::TEXT)) + LENGTH(fieldid_pattern.fieldid) - 3)), '/') + 2, 
            LENGTH(SUBSTRING('/' || cs.attributedatasourcelist::TEXT, 1, POSITION(fieldid_pattern.fieldid IN ('/' || cs.attributedatasourcelist::TEXT)) + LENGTH(fieldid_pattern.fieldid) - 3))
        ), 1, 
        POSITION(':' IN SUBSTRING(
            SUBSTRING('/' || cs.attributedatasourcelist::TEXT, 1, POSITION(fieldid_pattern.fieldid IN ('/' || cs.attributedatasourcelist::TEXT)) + LENGTH(fieldid_pattern.fieldid) - 3), 
            LENGTH(SUBSTRING('/' || cs.attributedatasourcelist::TEXT, 1, POSITION(fieldid_pattern.fieldid IN ('/' || cs.attributedatasourcelist::TEXT)) + LENGTH(fieldid_pattern.fieldid) - 3)) -  
            STRPOS(REVERSE(SUBSTRING('/' || cs.attributedatasourcelist::TEXT, 1, POSITION(fieldid_pattern.fieldid IN ('/' || cs.attributedatasourcelist::TEXT)) + LENGTH(fieldid_pattern.fieldid) - 3)), '/') + 2, 
            LENGTH(SUBSTRING('/' || cs.attributedatasourcelist::TEXT, 1, POSITION(fieldid_pattern.fieldid IN ('/' || cs.attributedatasourcelist::TEXT)) + LENGTH(fieldid_pattern.fieldid) - 3))
        ))
    ) AS list,
    cs."Name",
    cs."DatasetId"
FROM BMC_CORE_BMC_ComputerSystem cs
JOIN AST_ComputerSystem ac ON cs."ReconciliationIdentity" = ac."reconciliation_identity"
JOIN ast_attributes als ON als."ReconciliationIdentity" = cs."reconciliationidentity"
JOIN fieldid_pattern ON true
WHERE cs."DatasetId" = 'BMC.ASSET'
  AND als."assetLifecycleStatus" = '7'
  AND cs."AttributeDataSourceList" IS NOT NULL;