10 Replies Latest reply on Feb 24, 2017 9:26 PM by zackm

    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

        • Re: Help with SQL join  Custom Properties to NCM report
          Steven Klassen

          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
              ,InterfaceAlias
          FROM (
              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'
          ) T1
          GROUP BY Caption, InterfaceDescription, InterfaceAlias, OperStatus, T1.lastchange
          ORDER BY LastChange DESC
          
            • Re: Help with SQL join  Custom Properties to NCM report
              sja

              Nop...

               

               

              SELECT

                  Caption

                  ,InterfaceDescription

                  ,InterfaceAlias

              FROM (

                  SELECT

                      ND.Caption

                      ,NCMI.InterfaceAlias

                      ,NCMI.OperStatus

                      ,NCMI.InterfaceDescription

                      ,NodesCustomProperties.Owner

               

              Msg 4104, Level 16, State 1, Line 11

              The multi-part identifier "NodesCustomProperties.Owner" could not be bound.

                • Re: Help with SQL join  Custom Properties to NCM report
                  Steven Klassen

                  Just nope, huh? Fair enough.

                    • Re: Help with SQL join  Custom Properties to NCM report
                      sja

                      Hi

                       

                      Why it not happy with

                      "The multi-part identifier "NodesCustomProperties.Owner" could not be bound"

                       

                      That

                       

                      ,NodesCustomProperties.Owner

                        • Re: Help with SQL join  Custom Properties to NCM report
                          Steven Klassen

                          Do you have a NPM custom property called 'Owner'?

                            • Re: Help with SQL join  Custom Properties to NCM report
                              sja

                              Yes

                              and under Owner there are

                               

                              OwnerA

                              OwnerB

                               

                              That work just fine

                               

                               

                              FROM [NPMSLX].[dbo].[NodesCustomProperties]

                                Where Owner='EM'

                               

                              Do I need to join

                               

                               

                              NCM_NodeProperties ?

                                • Re: Help with SQL join  Custom Properties to NCM report
                                  zackm

                                  so, the problem right in front of us is on lines 11 and 15

                                   

                                  You cannot alias a table name (line 15) and then call the full table name in your select statement (line 11). Since the SELECT statement runs after the FROM statement in the order of operations, once you create an alias, the original table name ceases to exist for the remainder of the batch.

                                   

                                  This is the problem query:

                                  SELECT  
                                      Caption  
                                      ,InterfaceDescription  
                                      ,InterfaceAlias  
                                  FROM (  
                                      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'  
                                  ) T1  
                                  GROUP BY Caption, InterfaceDescription, InterfaceAlias, OperStatus, T1.lastchange  
                                  ORDER BY LastChange DESC 
                                  

                                   

                                  And this is that query, fixed:

                                  SELECT  
                                      Caption  
                                      ,InterfaceDescription  
                                      ,InterfaceAlias  
                                  FROM (  
                                      SELECT  
                                          ND.Caption  
                                          ,NCMI.InterfaceAlias  
                                          ,NCMI.OperStatus  
                                          ,NCMI.InterfaceDescription  
                                          ,ncp.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'  
                                  ) T1  
                                  GROUP BY Caption, InterfaceDescription, InterfaceAlias, OperStatus, T1.lastchange  
                                  ORDER BY LastChange DESC
                                  

                                   

                                  However, the more pressing problem is really that this is a very non-optimized query.

                                   

                                  Based on the above, the end result would be something like this:

                                   

                                  CaptionInterfaceDescriptionInterfaceAlias
                                  NodeAEthernet1/1Uplink to Datacenter

                                   

                                  So let's just skip all the junk and go right there:

                                   

                                  SELECT
                                    n.Caption
                                    ,i.InterfaceDescription
                                    ,i.InterfaceAlias
                                  FROM NCM_Interfaces i
                                  JOIN NCM_NodeProperties p ON i.NodeID = p.NodeID
                                  JOIN Nodes n ON p.CoreNodeID = n.NodeID
                                  WHERE n.Owner = 'OwnerXYZ'
                                  AND n.Caption LIKE '%.bbn%'
                                  AND (
                                    i.InterfaceName LIKE '%Fa%' OR InterfaceName LIKE '%Gi%'
                                  )
                                  AND (
                                    i.InterfaceAlias <> '' AND
                                    i.InterfaceAlias NOT LIKE '%uncont%' AND
                                    i.InterfaceAlias NOT LIKE '%-Controlled%'
                                  )
                                  

                                   

                                  Up to you how you get there, but the shortest path is usually the best in SQL-land

                                   

                                   

                                  FWIW: That particular error (and most others found in SQL) are easily solved with a bit of the googles as the error codes are quite literally thrown right in your face. This is something that I found out WAY later than I should have in my SQL journey, so I'd be remiss to not point it out here.

                                   

                                  Msg 4104, Level 16, State 1, Line 11

                                  The multi-part identifier "NodesCustomProperties.Owner" could not be bound.

                                   

                                  So, Error Message 4104: http://www.sql-server-helper.com/error-messages/msg-4104.aspx

                                  2 of 2 people found this helpful
                      • Re: Help with SQL join  Custom Properties to NCM report
                        njoylif

                        what version of Orion are you using?  try using Nodes (view for backward compatability) vs nodesdata and nodescustomproperties (one option)

                         

                        Initially, your join for the nodescustomproperties, first and foremost, has to join to the "previously built/put together" table so that line should be...

                        INNER JOIN NodesCustomProperties  NCP ON (NCMNP.NodeID=NCP.NodeID)

                         

                        Or   -- INNER JOIN NodesCustomProperties  NCP ON (NCMNP.NodeID=NCP.NodeID AND NodesCustomProperties.Owner ='OwnerXyZ')

                         

                        Also be careful with your left vs inner join (join alone defaults to left join if I recall correctly).

                         

                        Add a table (join) at a time to see where the issue lies.  Then, when you know there is a results set to work with, you can put your where logic