www.blackholesurfer.com

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;

Code: Select all

create table #appid (server nvarchar(256),apmid nvarchar(256), appenv nvarchar(256), App179 nvarchar(256))


insert into #appid values('cwq1-004664','AD00102837','','')
insert into #appid values('cwq1-004709','AD00102837','','')


update #appid
	set appenv = 
		case
			when server like '%PDV' then 'NonProd - Development'
			when server like 'CUD%' then 'NonProd - Development'
			when server like 'CWD%' then 'NonProd - Development'
			when server like '%BDV' then 'NonProd - Development'
			when server like '%CLEV' then 'NonProd - Development'
			when server like '%lab' then 'NonProd - Development'
			when server like '%lab-svc' then 'NonProd - Development'
			when server like '%lab-svc' then 'NonProd - Development'
			when server like '%dev%' then 'NonProd - Development'
			else 'Production'
		end
	where appenv = ''

update #appid
set app179 = 
		case
		
	when apmid = 'AD00002902' and appenv = 'NonProd - Development' then 'OI-FFE4A5B65BBE4BB4B9A4029EEA431DE7'
	when apmid = 'AD00006773' and appenv = 'NonProd - Development' then 'ASGAA5V0GZ286AQCTEHVQBV19FOAYD'
		end
where app179 = ''	
			
--select serialnumber,systemenvironment,instanceid from BMC_CORE_BMC_Application 
--where SerialNumber IN 
--select distinct 
--instanceid,
--hostname from BMC_CORE_BMC_ComputerSystem where hostname IN (select TOP 30 server from #appid order by server) 
--and datasetid = 'BMC.ASSET'
--and SystemEnvironment IS NOT NULL

	select distinct d.server,d.apmid, d.apmid,d.appenv, 
case
	when CIData.InstanceId is not null then CIData.InstanceId
	when CIDataInferred.InstanceId is not null then CIDataInferred.InstanceId
	when d.App179 IS NOT NULL then d.app179
	else null
end 'appInstanceID',
cs.instanceid 'ServInstanceID'
from #appid d
left join 
	(select instanceid, name, systemenvironment, SerialNumber 
	from BMC_CORE_BMC_Application 
	where isnull(serialnumber,'')+isnull(SystemEnvironment,'') in 
	(select isnull(serialnumber,'')+isnull(SystemEnvironment,'')
	from BMC_CORE_BMC_Application 
	where SerialNumber is not null and SystemEnvironment is not null group by isnull(serialnumber,'')+isnull(SystemEnvironment,'') having count(*) = 1)
	) CIData on CIData.serialnumber = d.apmid and CIData.SystemEnvironment = d.appenv
left join WIP_APM_Troux_Product_Version_ versLU on versLU.APM_SPV_ID = d.apmid  --, APM_SWP_ID
left join 
	(select instanceid, name, systemenvironment, SerialNumber 
	from BMC_CORE_BMC_Application 
	where isnull(serialnumber,'')+isnull(SystemEnvironment,'') in 
	(select isnull(serialnumber,'')+isnull(SystemEnvironment,'')
	from BMC_CORE_BMC_Application 
	where SerialNumber is not null and SystemEnvironment is not null group by isnull(serialnumber,'')+isnull(SystemEnvironment,'') having count(*) = 1)
	) CIDataInferred on CIDataInferred.serialnumber = versLU.APM_SWP_ID and CIDataInferred.SystemEnvironment = d.appenv
left join BMC_CORE_BMC_ComputerSystem cs on
	case when charindex('.',cs.name) > 1 then substring(cs.name,1,charindex('.',cs.name)-1) else cs.name end = d.server
	and cs.datasetid = 'bmc.asset'
where cs.InstanceId IS NOT NULL
order by appinstanceid 

drop table #appid

Code: Select all

create table #appid (server nvarchar(256),apmid nvarchar(256), appenv nvarchar(256), App179 nvarchar(256))
insert into #appid values('prdpbisql02OX','HRB-APPID-eb04a0d2-fc7e-11eb-88c9-eddc7b2d5b22','','')
insert into #appid values('prdkmbsql01OX','HRB-APPID-eb29b506-fc7e-11eb-88c9-eddc7b2d5b22','','')
insert into #appid values('tstbnmapp02OX','HRB-APPID-ebb48ff5-fc7e-11eb-88c9-eddc7b2d5b22','','')

update #appid
	set appenv = 
		case
			when server like 'PRD%' then 'Production'
			when server like '%QA%' then 'NonProd - QA'
			when server like '%DEV%' then 'NonProd - Development'
			when server like 'TST%' then 'Test - Development'
			else 'Production'
		end
	where appenv = ''

update #appid
set app179 = 
		case
	when apmid = 'HRB-APPID-eb04a0d2-fc7e-11eb-88c9-eddc7b2d5b22' and appenv = 'NonProd - Development' then 'OI-FFE4A5B65BBE4BB4B9A4029EEA431DE7'
	--when apmid = 'AD00006773' and appenv = 'NonProd - Development' then 'ASGAA5V0GZ286AQCTEHVQBV19FOAYD'
		end
where app179 = ''	
				select distinct d.server,d.apmid, d.apmid,d.appenv, 
case
	when CIData.InstanceId is not null then CIData.InstanceId
	when CIDataInferred.InstanceId is not null then CIDataInferred.InstanceId
	when d.App179 IS NOT NULL then d.app179
	else null
end 'appInstanceID',
cs.instanceid 'ServInstanceID'
from #appid d
left join 
	(select instanceid, name, systemenvironment, SerialNumber 
	from BMC_CORE_BMC_Application 
	where isnull(serialnumber,'')+isnull(SystemEnvironment,'') in 
	(select isnull(serialnumber,'')+isnull(SystemEnvironment,'')
	from BMC_CORE_BMC_Application 
	where SerialNumber is not null and SystemEnvironment is not null group by isnull(serialnumber,'')+isnull(SystemEnvironment,'') having count(*) = 1)
	) CIData on CIData.serialnumber = d.apmid and CIData.SystemEnvironment = d.appenv
left join BMC_CORE_BMC_Application versLU on versLU.SerialNumber = d.apmid  --, APM_SWP_ID
left join 
	(select instanceid, name, systemenvironment, SerialNumber 
	from BMC_CORE_BMC_Application 
	where isnull(serialnumber,'')+isnull(SystemEnvironment,'') in 
	(select isnull(serialnumber,'')+isnull(SystemEnvironment,'')
	from BMC_CORE_BMC_Application 
	where SerialNumber is not null and SystemEnvironment is not null group by isnull(serialnumber,'')+isnull(SystemEnvironment,'') having count(*) = 1)
	) CIDataInferred on CIDataInferred.serialnumber = versLU.SerialNumber and CIDataInferred.SystemEnvironment = d.appenv
left join BMC_CORE_BMC_ComputerSystem cs on
	case when charindex('.',cs.name) > 1 then substring(cs.name,1,charindex('.',cs.name)-1) else cs.name end = d.server
	and cs.datasetid = 'bmc.asset'
where cs.InstanceId IS NOT NULL
order by appinstanceid 

---------------- Destroy table
drop table #appid