3 Replies Latest reply on Jan 27, 2012 9:07 AM by d09h

    how to exclude interfaces from a report

    michalis

      I want to exclude an interface from a report i created. i need to exclude interfaces with a specific description. does anybody know the name of the variable that represents the interface description?

       

      thanks

          • Re: how to exclude interfaces from a report
            michalis

            below is the sql program. i do not know much about sql. i want to exclude from the report interfaces with a description ME-Cyta. can you help?

             

            thanks

             

             

            SELECT
            n.nodeid,
            i.interfaceid,
            n.Caption as Node,
            i.Caption as Interface,
            MAX(t.In_Averagebps) AS 'MAX_IN',
            MAX(t.Out_Averagebps) AS 'MAX_OUT',
            AVG(t.In_Averagebps) AS 'AVG_IN',
            AVG(t.Out_Averagebps) AS 'AVG_OUT',
            i.InterfaceSpeed,
            ROUND(AVG(t.In_Averagebps)/InterfaceSpeed*100,2) AS 'AVGPercent_Used_IN',
            ROUND(AVG(t.Out_Averagebps)/InterfaceSpeed*100,2) AS 'AVGPercent_Used_OUT'

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

            WHERE
            (
              (
             (
                n.MachineType like 'Cisco [1-7]%'
            and not i.Caption   like '%cellular%'
            and not i.Caption   like '%gigabitethernet0/1.2%'

               )

              )
            AND
               (
               LTRIM(MONTH(t.DateTime)) = (LTRIM(MONTH(getdate())) -1)
               AND
               DATEPART(dw, t.DateTime) between 2 and 6
               AND
             (
              t.DateTime BETWEEN
               CAST(Convert(Char(10), t.DateTime, 101) as smalldatetime) + ' 08:00'
               AND
               CAST(Convert(Char(10), t.DateTime, 101) as smalldatetime) + ' 15:00'
             )
               )
            )

            Group By n.caption, n.nodeid, i.interfaceid, i.caption, i.InterfaceSpeed

            ORDER BY n.caption, avg_in Desc

          • Re: how to exclude interfaces from a report
            d09h

            In a Report Writer report, the interface caption is the configured description on a Cisco switch.

            You can MIB walk to verify, but that's what led me to start including that field on reports with interfaces.

             

            Here's a report and corresponding SQL:

            SELECT
            Interfaces.Caption AS Interface_Caption
             FROM
            Interfaces