Help with SQL join Custom Properties to NCM report

sja

Hi

I like to filter that  report with  Custom Properties 'Owner='OwnerXyZ'

ELECT Caption,InterfaceDescription,InterfaceAlias

FROM (

SELECT ND.Caption, NCMI.InterfaceAlias,NCMI.OperStatus,NCMI.InterfaceDescription,NodesCustomProperties.Owner,NCMI.LastChange

FROM NCM_Interfaces NCMI INNER JOIN NCM_NodeProperties NCMNP ON (NCMI.NodeID = NCMNP.NodeID)

INNER JOIN NodesData ND ON (NCMNP.CoreNodeID = ND.NodeID)

INNER JOIN NodesCustomProperties  ON (NodesCustomProperties.Owner ='OwnerXyZ')

WHERE (Caption like '%.bbn%' )

AND (InterfaceName like  '%Fa%' or InterfaceName like  '%Gi%')

and InterfaceAlias!=''

and InterfaceAlias not like  '%uncont%'

and InterfaceAlias not like  '%-Controlled%'

) T1

GROUP BY Caption, InterfaceDescription,InterfaceAlias,OperStatus,t1.lastchange

ORDER BY LastChange DESC

mrxinu

Hi there sja​, I think your JOIN was off just a little bit. I've fixed it and moved the Owner bit down to the WHERE clause. Give this a try:

SELECT    Caption    ,InterfaceDescription    ,InterfaceAliasFROM (    SELECT        ND.Caption        ,NCMI.InterfaceAlias        ,NCMI.OperStatus        ,NCMI.InterfaceDescription        ,NodesCustomProperties.Owner        ,NCMI.LastChange    FROM NCM_Interfaces NCMI JOIN NCM_NodeProperties NCMNP ON NCMI.NodeID = NCMNP.NodeID    JOIN NodesData ND ON NCMNP.CoreNodeID = ND.NodeID    JOIN NodesCustomProperties ncp ON nd.NodeID = ncp.NodeID    WHERE Caption LIKE '%.bbn%'    AND (InterfaceName LIKE '%Fa%' OR InterfaceName LIKE '%Gi%')    AND InterfaceAlias != ''    AND InterfaceAlias NOT LIKE '%uncont%'    AND InterfaceAlias NOT LIKE '%-Controlled%'    AND ncp.Owner = 'OwnerXyZ') T1GROUP BY Caption, InterfaceDescription, InterfaceAlias, OperStatus, T1.lastchangeORDER BY LastChange DESC