14 Replies Latest reply on Feb 26, 2013 3:11 PM by stuartwhyte

    Need help w/Simple SQL query

    netjcp

      Hello all,

       

      Not familiar with SQL / Queries, but I would like to modify the following to incude only nodenames that beging witth XXX, and possible filter to only ethernet interfaces.

       

       

      declare @prev60 smalldatetime

      SET @prev60=dateadd(dd,-60,getdate())

       

       

        select i.InterfaceName, i.NodeID, i.interfaceid, sa.StatusName as AdminStatus, so.statusname as OperStatus, ss.Statusname as status, Interfacelastchange, n.caption

        from

        Interfaces i left join StatusInfo sa on i.adminStatus=sa.statusid

        left join StatusInfo so on i.OperStatus=so.StatusId

        left join StatusInfo ss on i.Status=ss.StatusId

        left join Nodes n on i.NodeID=n.NodeID

        where (Interfacelastchange < @prev60) and i.Status <> 1 and i.adminstatus <> 4 and i.NodeName like WBX

      -- group by n.caption, i.InterfaceName,

        order by InterfaceLastChange

       

      Thanks in advance !!

        • Re: Need help w/Simple SQL query
          stuartwhyte

          Hi,

           

          Try this:

           

          declare @prev60 smalldatetime

          SET @prev60=dateadd(dd,-60,getdate())

           

          select

          i.InterfaceName,

          i.InterfaceTypeDescription,

          i.NodeID,

          i.interfaceid,

          sa.StatusName as AdminStatus,

          so.statusname as OperStatus,

          ss.Statusname as status,

          Interfacelastchange,

          n.caption

           

            from

           

            Interfaces i left join StatusInfo sa on i.adminStatus=sa.statusid

           

            left join StatusInfo so on i.OperStatus=so.StatusId

           

            left join StatusInfo ss on i.Status=ss.StatusId

           

            left join Nodes n on i.NodeID=n.NodeID

           

          where (Interfacelastchange < @prev60) and i.Status <> 1 and i.adminstatus <> 4 and n.Caption like 'xxx%' and i.InterfaceTypeDescription = 'Ethernet'

           

          -- group by n.caption, i.InterfaceName,

           

            order by InterfaceLastChange

           

           

          Hope this helps

          Stuart

          1 of 1 people found this helpful
            • Re: Need help w/Simple SQL query
              netjcp

              Hello Stuart..

               

              Thankyou so much !!!

              Looks good.. but I’m trying to report by nodename.. tried to change nodeid to nodename, but have something wrong..

                • Re: Need help w/Simple SQL query
                  stuartwhyte

                  The node name is in the n.Caption field.  You could change the line to be n.caption AS NodeName if you need node name as the column heading.

                    • Re: Need help w/Simple SQL query
                      netjcp

                      Stuart !

                       

                      Thank you . That's Great,,, Can I ask one more q ?  I want to expand my search from just XXX to wither XXX or YYY, I've attempted it, and although I don't get any error messages, I only show the XXX devices..

                       

                      This is the line I adjusted.

                       

                      where (Interfacelastchange < @prev60) and i.Status <> 1 and i.adminstatus <> 4 and (n.Caption like '%WBX%' or n.Caption like '%NYX%')

                       

                      Should I open a separate query for this ?

                        • Re: Need help w/Simple SQL query
                          stuartwhyte

                          The line should look like:

                           

                          where (Interfacelastchange < @prev60) and i.Status <> 1 and i.adminstatus <> 4 and (n.Caption like 'xxx%' OR n.Caption like 'yyy%' )and i.InterfaceTypeDescription = 'Ethernet'

                           

                          Removing the leading %

                           

                          Although what you have will work, so I'd say that you have no YYY devices that meet the remaining criteria.  To test use the line like this:

                           

                          where (Interfacelastchange < @prev60) and i.Status <> 1 and i.adminstatus <> 4 and n.Caption like 'yyy%' and i.InterfaceTypeDescription = 'Ethernet'

                           

                          which I expect will produce zero results.

                          1 of 1 people found this helpful
                            • Re: Need help w/Simple SQL query
                              netjcp

                              Thanks again Stuart,

                               

                              I think I have something else going on. Even if I remove the query for n.caption.. I am only getting certain devices.

                              I’m going to see if support can assist..

                               

                              Joe

                                • Re: Need help w/Simple SQL query
                                  stuartwhyte

                                  Break down your WHERE statement into individual blocks and query one at a time:

                                   

                                  i.Status <> 1 will only show devices that are not UP, so unless you have a lot of devices in that state they wont show up, likewise for i.adminstatus.  You are also querying for any interface that has changed in the past 60 days, so any interface that hasn't changed wont show up.

                                   

                                  Support might be a better option as they will be able to see exactly what you are seeing.

                      • Re: Need help w/Simple SQL query
                        Steven Klassen

                        Don't forget to mark stuartwhyte's answer as the correct answer for this thread if he's hit the mark!