6 Replies Latest reply on Oct 29, 2013 8:45 AM by zackm

    costing for our network usage per branch

    mprice501

      Hi Guys

       

      I have been asked to provide a monthly report to show each of our branch offices network usage, internet, ftp, etc.

       

      what would be the best way to do this?

       

      cheers

       

      Matt

        • Re: costing for our network usage per branch
          zackm

          What products/modules do you currently own?

           

          There are a few different options for this. Can you be more specific on what you need to report on? Do you need total bytes across a link, top protocols used, total availability, etc etc etc.

            • Re: costing for our network usage per branch
              mprice501

              Currently NPM and NTA but new to using them, we want to know out of all our branch offices (25 in total) what percentage is their internet use for the month so we can charge them fairly

                • Re: costing for our network usage per branch
                  zackm

                  I would suggest identifying the outbound interface for each branch, then pulling those into a single bandwidth report in report writer. To get percentages and such, you could either manually run the numbers monthly, or use some advanced SQL in your report to get it all automated. Message me if you need further help, I'm a tad busy now

                    • Re: costing for our network usage per branch
                      mprice501

                      Hi I have changed the interface captions to identify the outside interfaces of the 25 branch offices, how would I make a report to identify just the internet traffic from those branches? percentages would be great if you could help

                        • Re: costing for our network usage per branch
                          zackm

                          Sorry for the delay.

                           

                          Working with a coworker, we came up with the following:

                           

                          DECLARE @LastMonthFirstDay DateTime
                          DECLARE @LastMonthLastDay DateTime
                          
                          SET @LastMonthFirstDay = (SELECT CAST('01 '+ RIGHT(CONVERT(CHAR(11), DATEADD(MONTH, -1, GETDATE()), 113), 8) AS DateTime))
                          SET @LastMonthLastDay = (SELECT DATEADD(DAY, -1, CAST('01 '+ RIGHT(CONVERT(CHAR(11), GETDATE(), 113), 8) AS DateTime)))
                          
                          DECLARE @AllTheBytes BigInt
                          
                          SET @AllTheBytes = (
                              SELECT SUM(it.in_totalbytes + it.out_totalbytes)
                              FROM interfacetraffic it
                              JOIN interfaces i ON i.interfaceid = it.interfaceid
                              WHERE i.custom_property = 'xyz'
                              AND it.datetime BETWEEN @LastMonthFirstDay AND @LastMonthLastDay
                          )
                          
                          SELECT i.interfaceid, i.caption, SUM(it.in_totalbytes + it.out_totalbytes) total_bytes,
                                (SUM(it.in_totalbytes + it.out_totalbytes) / @AllTheBytes) * 100 percentage_of_all_the_bytes
                          FROM interfaces i
                          JOIN interfacetraffic it ON i.interfaceid = it.interfaceid
                          WHERE i.custom_property = 'xyz' AND it.datetime BETWEEN @LastMonthFirstDay AND @LastMonthLastDay
                          GROUP BY i.interfaceid, i.caption
                          

                           

                           

                          So; you will need to add a custom property to all of your interfaces that you want to measure bandwidth on. Then call on that property in the query where it has i.custom_property = 'xyz'

                          i.e.:

                          If you set you custom property to "Bandwidth_Billing", and all interfaces you cared about had a value of "Yes"; then you would do the following:

                           

                          i.Bandwidth_Billing = 'Yes'

                           

                          Then you would take your final query and use report writer to make an advanced sql report.

                           

                          Let me know how it goes!