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