Page 1 of 1

SQL to relate databases to apps

Posted: Mon Aug 19, 2019 7:46 pm
by hudatolah
select distinct
db.instanceid as db179, sa.instanceid as SA179
from BMC_CORE_BMC_Database db
left join BMC_CORE_BMC_Dependency r on r.Source_InstanceID = DB.InstanceId and r.name = 'APPLICATIONDATABASE'
left join BMC_CORE_BMC_Application a on a.InstanceId = r.destination_instanceid
left join BMC_CORE_BMC_Dependency rSS on rSS.Destination_InstanceId = db.InstanceId = rss.Source_instanceid
left join BMC_CORE_BMC_dependency rs.Destination_Instanceid = ss.instanceid
left join BMC_CORE_BMC_ComputerSystem cs on cs.Instanceid = rs.source_instanceid
left join BMC_CORE_BMC_dependency rsa.source_instanceid = cs.instanceid and rsa.name = 'APPLICATIONSYSTEMCOMPUTER'
left join BMC_CORE_BMC_Application sa on sa.Instanceid = rsa.Destination_Instanceid
where db.datasetid = 'BMC.ASSET'
and a.Instanceid is null
and db.model not in ('Oracle Database', 'SQL Server')
and sa.SerialNumber is not null

Re: SQL to relate databases to apps

Posted: Mon Aug 19, 2019 7:54 pm
by hudatolah
select ap.instanceid as ap179, ap.serialnumber as apsnlist, cs.instanceid as CS179, cs.apmlistonhost as csapidlist
from bmc_core_bmc_computersysterm cs
left join BMC_CORE_BMC_application ap ap.datasetid = 'MYDATA.STAGE' and cs.datasetid = 'MYDATA.STAGE' and cs.APMListOnHost is not null
where cs.datasetid = 'MYDATA.STAGE' and CS.APMListOnHost like ('%'+ap.SerialNumber+'%') and ap.SystemEnvironm,ent = 'Production';