4 Replies Latest reply on Mar 24, 2015 2:50 AM by JiriPsota

    Unmanaged interfaces in the future report?

    patricktw

      I'm trying to identify a way to pull a list of interfaces that will be unmanaged in the future.  Typically we have maintenance on a pair of circuits mandated by our carriers once a month or so, and usually for multiple nights.

       

      Does anyone know an easy way to report on when an interface is set to be unmanaged in the future, from the time of start to end? Ideally I'd like to see the Node Name, Interface Id, Unmanaged From, and Unmanaged To.  I've tested several other reports shared by others, but have only been able to see the future unmanaged state of Nodes when using them.

       

      I know this is part of what the Web Performance Monitor can do, but I can't justify the price tag for just this report.. and I'm a self admitted newb when it comes to SQL :-/

        • Re: Unmanaged interfaces in the future report?
          azabielski

          Here is what i believe you want

           

          select i.InterfaceName,n.Caption,i.UnManageFrom,i.UnManageUntil from Interfaces as i

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

          where GETDATE() <= i.UnManageFrom

           

          Let me know if this is correct of if you have any questions

          • Re: Unmanaged interfaces in the future report?
            JiriPsota

            There are two ways how you can unmanaged interface.

            1. unmanage whole node

            2. unmanage specific interface

            Following select should cover both cases (it's UNION of two selects so you can remove one if you would like to see interfaces unmanaged with the first or the second way):

             

            --Unmanage Nodes with interfaces

            SELECT n.Caption

                 , i.Caption

              , DATEADD(mi, DATEDIFF(mi, GETUTCDATE(), GETDATE()), n.UnManageFrom) AS UnmanagedFrom

              , DATEADD(mi, DATEDIFF(mi, GETUTCDATE(), GETDATE()), n.UnmanageUntil) AS UnmanagedUntil

              ,'NO' AS OnlyInterface

              FROM Nodes n JOIN Interfaces i ON (n.NodeID=i.NodeID)

            WHERE n.UnManageFrom>GETUTCDATE()

               AND (i.UnManageFrom IS NULL or i.UnManageFrom<GETUTCDATE())

            UNION

            --Unmanaged interfaces

            SELECT n.Caption

                 , i.Caption

              , DATEADD(mi, DATEDIFF(mi, GETUTCDATE(), GETDATE()), i.UnManageFrom) AS UnmanagedFrom

              , DATEADD(mi, DATEDIFF(mi, GETUTCDATE(), GETDATE()), i.UnmanageUntil) AS UnmanagedUntil

              ,'YES' AS OnlyInterface

              FROM Nodes n JOIN Interfaces i ON (n.NodeID=i.NodeID)

            WHERE i.UnManageFrom>GETUTCDATE()

             

             

            UnManageFrom and UnManageUntil is in UTC so it has to be converted to Local time. "NO" in "OnlyInterfaces" column means the whole node was unmanaged. "YES" means, specific interface was unmanaged.

            • Re: Unmanaged interfaces in the future report?
              patricktw

              Hey guys,

               

              Thank you very much for helping with this.  Running both reports from the server itself in Report Writer brings up a great list of our unmanaged interfaces coming up -- but when I try to run it from our web front end, the report results with "No Activity to Report".

               

              I'm not sure what is failing here.  I've tried logging out and back in, and the reports were saved successfully after I added them as an advanced sql report.