Code: Select all
declare @fieldname nvarchar(128), @classname nvarchar(256), @fieldid nvarchar(32)
set @fieldname = 'Name'
set @classname = 'BMC.CORE:BMC_Computersystem'
set @fieldid = '%'+(select cast(fieldid as nvarchar(32)) from field where schemaid = (select distinct schemaid from arschema where name = @classname) and fieldname = @fieldname)+'%'
SELECT 'https://COMPANY-smartit.onbmc.com/smartit/app/#/asset/'+cs.reconciliationidentity+'/BMC_COMPUTERSYSTEM' as 'SMART IT URL',
DATEADD(s,cs.CreateDate, '19700101')'DateCreated', cs.name 'CI Name', cs.hostname 'Hostname', cs.Model 'Model', cs.systemenvironment
,cs.Description, cs.ShortDescription, cs.Submitter,
cs.Room, cs.Site, cs.PartitionId,cs.Type,
case
when cs.primarycapability = 0 then 'Not Dedicated'
when cs.primarycapability = 1 then 'Unknown'
when cs.primarycapability = 2 then 'Other'
when cs.primarycapability = 3 then 'Storage'
when cs.primarycapability = 4 then 'Router'
when cs.primarycapability = 5 then 'Switch'
when cs.primarycapability = 6 then 'Layer 3 Switch'
when cs.primarycapability = 7 then 'Central Office Switch'
when cs.primarycapability = 8 then 'Hub'
when cs.primarycapability = 9 then 'Access Server'
when cs.primarycapability = 10 then 'Firewall'
when cs.primarycapability = 11 then 'Print'
when cs.primarycapability = 12 then 'I/O'
when cs.primarycapability = 13 then 'Web Caching'
when cs.primarycapability = 14 then 'Server'
when cs.primarycapability = 15 then 'Management'
when cs.primarycapability = 16 then 'Block Server'
when cs.primarycapability = 17 then 'File Server'
when cs.primarycapability = 18 then 'Mobile User Device'
when cs.primarycapability = 19 then 'Repeater'
when cs.primarycapability = 20 then 'Bridge/Extender'
when cs.primarycapability = 21 then 'Gateway'
when cs.primarycapability = 22 then 'LoadBalancer'
when cs.primarycapability = 23 then 'Mainframe'
when cs.primarycapability = 24 then 'SANSwitch'
when cs.primarycapability = 25 then 'SANHub'
when cs.primarycapability = 26 then 'SANBridge'
when cs.primarycapability = 27 then 'SANRouter'
when cs.primarycapability = 28 then 'SANDirector'
when cs.primarycapability = 29 then 'RAIDStorageDevice'
when cs.primarycapability = 30 then 'Virtual Tape Library'
when cs.primarycapability = 31 then 'JBOD'
when cs.primarycapability = 32 then 'Workstation'
when cs.primarycapability = 33 then 'StorageSubsystem'
when cs.primarycapability = 34 then 'Storage Virtualizer'
when cs.primarycapability = 35 then 'Media Library'
when cs.primarycapability = 36 then 'ExtenderNode'
when cs.primarycapability = 37 then 'NAS Head'
when cs.primarycapability = 38 then 'Self-contained NAS'
when cs.primarycapability = 39 then 'UPS'
when cs.primarycapability = 40 then 'IP Phone'
when cs.primarycapability = 41 then 'Management Controller'
when cs.primarycapability = 42 then 'Chassis Manager'
when cs.primarycapability = 43 then 'Host-based RAID controller'
when cs.primarycapability = 44 then 'Storage Device Enclosure'
when cs.primarycapability = 45 then 'Desktop'
when cs.primarycapability = 46 then 'Laptop'
when cs.primarycapability = 47 then 'Virtual Library System'
when cs.primarycapability = 48 then 'Blade System'
when cs.primarycapability = 49 then 'Blade Server'
when cs.primarycapability = 50 then 'VPN Concentrator'
when cs.primarycapability = 51 then 'Proxy Server'
when cs.primarycapability = 52 then 'Layer 3 Proxy Server'
when cs.primarycapability = 53 then 'WAN Accelerator'
when cs.primarycapability = 54 then 'SAN Processor'
when cs.primarycapability = 55 then 'Access Point'
when cs.primarycapability = 56 then 'Wireless LAN Controller'
end 'Primary Capability',
substring(
SUBSTRING(
substring('/'+cast(cs.attributedatasourcelist as nvarchar(1024)),1,patindex(@fieldid,'/'+cast(cs.attributedatasourcelist as nvarchar(1024)))+len(@fieldid)-3),
LEN(substring('/'+cast(cs.attributedatasourcelist as nvarchar(1024)),1,patindex(@fieldid,'/'+cast(cs.attributedatasourcelist as nvarchar(1024)))+len(@fieldid)-3)) -
CHARINDEX('/',REVERSE(substring('/'+cast(cs.attributedatasourcelist as nvarchar(1024)),1,patindex(@fieldid,'/'+cast(cs.attributedatasourcelist as nvarchar(1024)))+len(@fieldid)-3))) + 2,
LEN(substring('/'+cast(cs.attributedatasourcelist as nvarchar(1024)),1,patindex(@fieldid,'/'+cast(cs.attributedatasourcelist as nvarchar(1024)))+len(@fieldid)-3))
),1,CHARINDEX(':',
SUBSTRING(
substring('/'+cast(cs.attributedatasourcelist as nvarchar(1024)),1,patindex(@fieldid,'/'+cast(cs.attributedatasourcelist as nvarchar(1024)))+len(@fieldid)-3),
LEN(substring('/'+cast(cs.attributedatasourcelist as nvarchar(1024)),1,patindex(@fieldid,'/'+cast(cs.attributedatasourcelist as nvarchar(1024)))+len(@fieldid)-3)) -
CHARINDEX('/',REVERSE(substring('/'+cast(cs.attributedatasourcelist as nvarchar(1024)),1,patindex(@fieldid,'/'+cast(cs.attributedatasourcelist as nvarchar(1024)))+len(@fieldid)-3))) + 2,
LEN(substring('/'+cast(cs.attributedatasourcelist as nvarchar(1024)),1,patindex(@fieldid,'/'+cast(cs.attributedatasourcelist as nvarchar(1024)))+len(@fieldid)-3))
))) as 'Provenance'
from BMC_CORE_BMC_ComputerSystem cs where cs.instanceid in (
select instance_id
from AST_ComputerSystem ast
join ast_attributes als on als.assetlifecyclestatus = ast.assetlifecyclestatus
left join BMC_CORE_BMC_Dependency br on (br.source_instanceid = instance_id and br.destination_classid IN ('bmc_application') and br.datasetid = 'bmc.asset')
where ast.data_set_id = 'BMC.ASSET'
--and cs.type = 'server'
and ast.primary_capability = '14'
and br.Destination_InstanceId is null
)
and cs.isVirtual = 1
order by cs.CreateDate DESC;