3 Replies Latest reply on Jun 24, 2011 4:26 PM by qle

    Need report:SQL Node/interface/LastChange/Status

    d-moore

      Need some type of adv sql report that will gives me a node, the interface (gig1/0/1) when it last changed  June-1-2011 and status (up/down).  I have this below, that was leftover from my orion 8.51. SQL field in the report writer. But have no idea what it does or if the fields are valid

       

      select n.nodeid, n.Caption as NodeName, i.Caption as InterfaceName, InterfaceLastChange, i.Status, i.StatusLED
      from nodes n
      inner join interfaces i
          on n.nodeid = i.nodeid
      where n.nodeid = 157 and i.Status = 2

        • Re: Need report:SQL Node/interface/LastChange/Status
          qle

          Caption doesn't seem to be a valid column in the Interfaces table any longer. Also removed the where condition since it seems to only display a specific NodeID and only when it's down. Otherwise, the query seems valid.

          select n.nodeid, n.Caption as NodeName, i.InterfaceName, InterfaceLastChange, i.Status, i.StatusLED
          from nodes n
          inner join interfaces i
              on n.nodeid = i.nodeid

          Hope it works for you.

            • Re: Need report:SQL Node/interface/LastChange/Status
              d-moore

              Thanks for the info!

              possible to add interface alias column, so I can get on the output what interface might be named in the cisco switch? 

              Those NodeID= and Status=2 are part of the report that give me the node and the stats of the interface being "down", so  they are necessary.

              Noticed I can't "sort" (ascending/descending) the results either on the webpage or inside the sql query. That normal?

                • Re: Need report:SQL Node/interface/LastChange/Status
                  qle

                  To sort, simply add order by <column name> to the end of the query. Here is the updated query with InterfaceAlias added.

                  select n.nodeid, n.Caption as NodeName, i.InterfaceName, i.InterfaceAlias, InterfaceLastChange, i.Status, i.StatusLED
                  from nodes n
                  inner join interfaces i
                  on n.nodeid = i.nodeid
                  order by InterfaceAlias