1 Reply Latest reply on Mar 4, 2014 10:49 PM by zackm

    how to query reports?

    oakeedmc

      My report title have current percent utilization - Top 25 interfaces,

      but I 'd like to query a report in order to 7 days percent utilization - Top all interfaces

       

      NPM.JPG

        • Re: how to query reports?
          zackm

          Try this:

           

          Avg_Interface_Utilization_-_Last_7_Days.OrionReport

           

          Here's the query:

           

          select
            n.nodeid,
            n.caption as device,
            i.interfaceid,
            i.caption as interface,
            case
                 when tr.avg_in = 0 then 0
                 when i.inbandwidth = 0 then 'Bandwidth Not Set'
                 else round((tr.avg_in/i.inbandwidth)*100,2) end as avg_in_perc_util,
            case
                 when tr.avg_out = 0 then 0
                 when i.outbandwidth = 0 then 'Bandwidth Not Set'
                 else round((tr.avg_out/i.outbandwidth)*100,2) end as avg_out_perc_util
          from interfaces i
          join nodes n on n.nodeid = i.nodeid
          join
          (
            select 
                 interfaceid,
                 avg(in_averagebps) as avg_in,
                 avg(out_averagebps) as avg_out
            from interfacetraffic_detail
            where in_averagebps is not null and out_averagebps is not null
            group by interfaceid
          ) as tr ON tr.interfaceid = i.interfaceid
          order by i.interfaceid
          

           

           

          -ZackM

          http://www.loop1systems.com