There is really no easy way to do it, even with Analyitics. I am working on SQL
to do something very similar. My customer wants to see the relationships on the
same line as well. I wrote the SQL and am using it in BMC Analytics with Custom
SQL for the report. This code was written for SQL Server 2012 and this is based
on the Views from CMDB 8.1. The key to this is the distinct statment at the
begining and the (Select Top1 from AST_AssetPeople.Full_Name....) and join to
AST_AssetPeople. I am also looking for any related OS records that were
discovered for the asset. You will have to tweak this to account for your own
Product catalog. I am also joining to AST Attributes for some custom fields,
but I don't have them in this statement.
I hope this helps,
Jesus Ortega
SELECT Distinct
AM_BMC_CORE_BMC_BASEELEMENT.Name ,
ISNULL(AM_BMC_CORE_BMC_BASEELEMENT.AssetID,'UNKNOWN') 'Asset ID',
AssetLifecycleStatus.ASSETLIFECYCLESTATUS,
-- AM_BMC_CORE_BMC_BASEELEMENT.InstanceId ,
AM_BMC_CORE_BMC_BASEELEMENT.Region ,
AM_BMC_CORE_BMC_BASEELEMENT.SiteGroup,
ISNULL(AM_BMC_CORE_BMC_BASEELEMENT.Floor,'UNKNOWN') as 'Floor',
ISNULL(AM_BMC_CORE_BMC_BASEELEMENT.Room,'UNKNOWN') 'Room' ,
(
Select Top 1
AST_AssetPeople.Full_Name
FROM
AST_AssetPeople
Where
AST_AssetPeople.AssetInstanceId=
AM_BMC_CORE_BMC_BASEELEMENT.ReconciliationIdentity
AND
( AST_AssetPeople.PersonRole )=39000
) as 'Managed by',
(
Select Top 1
AST_AssetPeople.Full_Name
FROM
AST_AssetPeople
Where
AST_AssetPeople.AssetInstanceId=
AM_BMC_CORE_BMC_BASEELEMENT.ReconciliationIdentity
AND
( AST_AssetPeople.PersonRole )=10000
) as 'Supported by' ,
AM_RELATED_CI.Name 'OS',
AM_BMC_CORE_BMC_BASEELEMENT.Description as 'Comments'
,
ISNULL(AM_BMC_CORE_BMC_BASEELEMENT.SerialNumber,'UNKNOWN') as 'Serial No.'
FROM
AST_AssetPeople RIGHT OUTER JOIN (
SELECT *
FROM BMC_CORE_BMC_BASEELEMENT
WHERE BMC_CORE_BMC_BASEELEMENT.COMPANY = '<your company here>'
) AM_BMC_CORE_BMC_BASEELEMENT ON (AST_AssetPeople.AssetInstanceId=
AM_BMC_CORE_BMC_BASEELEMENT.ReconciliationIdentity)
LEFT OUTER JOIN (
SELECT FIELD_ENUM_VALUES.ENUMID,
FIELD_ENUM_VALUES.VALUE AS ASSETLIFECYCLESTATUS
FROM FIELD_ENUM_VALUES
WHERE FIELD_ENUM_VALUES.FIELDID = 7
AND FIELD_ENUM_VALUES.SCHEMAID = (SELECT SCHEMAID
FROM ARSCHEMA
WHERE NAME =
'BMC.CORE:BMC_BaseElement') )
AssetLifecycleStatus ON
(AssetLifecycleStatus.ENUMID=AM_BMC_CORE_BMC_BASEELEMENT.AssetLifecycleStatus)
LEFT OUTER JOIN BMC_CORE_BMC_BASERELATIONSHIP AM_BMC_BASERELATIONSHIP ON
(AM_BMC_CORE_BMC_BASEELEMENT.InstanceId=AM_BMC_BASERELATIONSHIP.Source_InstanceId
and AM_BMC_BASERELATIONSHIP.Name IN ('SYSTEMOS'))
LEFT OUTER JOIN (
SELECT *
FROM BMC_CORE_BMC_BASEELEMENT
WHERE BMC_CORE_BMC_BASEELEMENT.COMPANY ='<You company here>'
)
AM_RELATED_CI ON
(AM_BMC_BASERELATIONSHIP.Destination_InstanceId=AM_RELATED_CI.InstanceId )
-- AND AM_RELATED_CI.Item = 'Operating System and Components'
LEFT OUTER JOIN AST_Attributes ON
(AST_Attributes.ReconciliationIdentity=AM_BMC_CORE_BMC_BASEELEMENT.ReconciliationIdentity)
WHERE
(
( ( ISNULL(AM_BMC_CORE_BMC_BASEELEMENT.Category,'UNKNOWN') ) = 'Hardware' )
AND
AssetLifecycleStatus.ASSETLIFECYCLESTATUS NOT IN (
'Delete','Disposed','End of Life' )
AND AM_BMC_CORE_BMC_BASEELEMENT.datasetid = 'BMC.ASSET'
AND
AM_BMC_CORE_BMC_BASEELEMENT.Item IN ( 'Server' )
AND AM_RELATED_CI.Name is not null)
_______________________________________________________________________________
UNSUBSCRIBE or access ARSlist Archives at www.arslist.org
"Where the Answers Are, and have been for 20 years"
