www.blackholesurfer.com

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

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';