SQL to relate databases to apps

ADDM, Client Management, Spread sheet loads by ITSM, etc..
Post Reply
hudatolah
Site Admin
Posts: 143
Joined: Thu Apr 04, 2013 8:10 pm
Are You a Headhunter?: Affirmative
Surfer?: Yes

SQL to relate databases to apps

Post by hudatolah » Mon Aug 19, 2019 7:46 pm

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
The Blackholesurfer. My surfboard has teeth.

hudatolah
Site Admin
Posts: 143
Joined: Thu Apr 04, 2013 8:10 pm
Are You a Headhunter?: Affirmative
Surfer?: Yes

Re: SQL to relate databases to apps

Post by hudatolah » Mon Aug 19, 2019 7:54 pm

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';
The Blackholesurfer. My surfboard has teeth.