www.blackholesurfer.com

Code: Select all

;WITH tmp(instanceid, DataItem, String) AS
(
    SELECT top 10
        cs.instanceid,
        LEFT(cast(cs.attributedatasourcelist as nvarchar(max)), CHARINDEX('/', cast(cs.attributedatasourcelist as nvarchar(max)) + '/') - 1),
        STUFF(cast(cs.attributedatasourcelist as nvarchar(max)), 1, CHARINDEX('/', cast(cs.attributedatasourcelist as nvarchar(max)) + '/'), '')
    from BMC_CORE_BMC_ComputerSystem cs
	join AST_ComputerSystem ac on cs.instanceid = ac.instance_id
    join [dbo].[utf_EnumValues]('ast:attributes', 'assetlifecyclestatus') als on als.value = ac.assetlifecyclestatus 
	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.display = 'Deployed'
    UNION all

    SELECT
        instanceid,
        LEFT(String, CHARINDEX('/', String + '/') - 1),
        STUFF(String, 1, CHARINDEX('/', String + '/'), '')
    FROM tmp
    WHERE
        String > ''
)

SELECT
    instanceid,
	DataItem,
	left(DataItem,charindex(':',DataItem)-1),
    right(DataItem,len(DataItem)-charindex(':',DataItem)) 'fieldIDs',
	t.fieldId,
	t.fieldName
FROM tmp
left join
	(select distinct fieldName, cast(f.fieldId as nvarchar(64)) 'fieldId'
	from field f
	join arschema s on  f.schemaId = s.schemaId and s.name = 'AST:ComputerSystem') t on (right(DataItem,len(DataItem)-charindex(':',DataItem)) = fieldId or right(DataItem,len(DataItem)-charindex(':',DataItem)) like '%,'+fieldId+',%' or right(DataItem,len(DataItem)-charindex(':',DataItem)) like fieldId+',%' or right(DataItem,len(DataItem)-charindex(':',DataItem)) like '%,'+fieldId)
	order by instanceid