/ 
RTG-Report-1.17
River stage zero No dependents
/ billing_report.pl

NAME

billing_report.pl - extract usage data from RTG databases, particularly for bandwidth billing.

VERSION

1.5.0

SYNOPSIS

bandwidth_billing.pl [-options] [-v]

  available options:
  ------------------------------------------------
  all_ifs:    display all interfaces (no filtering)
  back:       days backwards used to determine reporting period
  desc:       port description to search for
  email:      email address to send the report to
  max:        maximum number of interfaces to report
  no95th:     suppress 95th percentile columns
  norates:    suppress rate columns
  nosum:      suppress summary rows
  net:        report only a particular network
  start/stop: start/end day in DD format
  units:      reporting units - a number to divide octets/bits by
  utiliz:     display port utilization columns

  help:     display this help page
  verbose:  verbose/debug output

USAGE

Add to cron:

# crontab -e
30 5 1 * * /usr/local/sbin/report_bandwidth.pl

Or better yet, add to your systems periodic scripts. On FreeBSD, this would be optimal:

mkdir -p /usr/local/etc/periodic/monthly && chdir /usr/local/etc/periodic/monthly
echo '#!/bin/sh' > bandwidth_report.sh
echo '/usr/local/sbin/report_bandwidth.pl' >> bandwidth_report.sh
chmod 755 bandwidth_report.sh

OPTIONS

-all_ifs

By default, interfaces whose name contains vlan and port-channel are filtered out. Selecting this option prevents the filtering of those interfaces.

-back

The default reporting time period is the first day of the current month until the last complete day of data, yesterday. The exception to this rule is EOM (End of Month) days. If run within 5 days of of the end of the month (ie, the 1-5 of the next month), then the reporting interval is the previous complete month.

This setting allows specification of how many days -back are used when calculating the month to report. To run a report for the previous month when the current day is greater than 5, set -back to a value of todays day plus one.

Examples: 
Jan 25th, -back 5, reports Jan 1-24
Feb 04th, -back 5, reports Jan 1-31
Feb 10th, -back 5, reports Feb 1-9
Feb 10th, -back 11, reports Jan 1-31
Feb 10th, -back 45, reports Dec 1-31
-desc

A port description. If provided, the report will only report data for ports that match the supplied description.

-desc can optionally specify a filename. The contents of the file must be a list of port descriptions, one per line. When run this way, only ports matching a description listed in the file will be reported on.

-email

The report is automatically emailed to each email address listed in the reporter.conf file. You can optionally specify an email address on the command line that overrides the config file settings.

-help

Prints out the pod documentation.

-max

max is a maximum number of interfaces to run against. We have a large network with many thousands of interfaces. I use this option when testing the script.

-no95th

Suppresses the 95th percentile columns in the report.

-norates

Suppresses the rate columns in the report. The four rate columns are the average in/out Mbit/s and max in/out Mbit/s.

-nosum

The report automatically includes 4 summary entries for each network. These four links include summaries of uplinks, downlinks, isp_uplinks, and a grand total for the network. The uplinks, downlinks, and isp_uplinks are all detected based on keywords defined in the port descriptions. You can suppress these summary entries entirely with the -nosum option.

-net

If you have many networks configured, you can run a report for just one of them by specifying it with the -net option.

-start/stop

If the -back option doesn't provide the exact date range you want, you can optional specify a -start and -end day for the report. This would be useful if you were reporting on a basis other than monthly.

-units

The default reporting units are Megabyte/bits. This can be changed in the config file or via the command line. Common values would be 1000 (kilo), 1000000 (mega), 1000000000 (gig).

-utiliz

Utilization is the percentage of bandwidth utilized by a client. It is not reported by default. Utilization is calculated based on the actual usage and the port speed. When using the default rtgtargetmkr.pl script supplied with RTG, this number will be frequently invalid as that script does not automatically update port descriptions or speeds. I have rewritten the target maker script with a version that does update the dabase when switch/router changes are detected.

-verbose

This billing is designed to run quietly via cron and only generate noise if errors are encountered. The -verbose option prints out status information while processing.

DESCRIPTION

This script is a replacement for RTG's report.pl by Rob Beverly and Adam Rothschild.

The biggest difference between this and the old script is that the old one just spat out formatted lines of text as it progressed. Unfortunately, the predefined fixed format also truncated fields. It was entirely too difficult to add or remove fields from the old report. It also required preprocessing to import into other programs (Excel, SQL, etc). It required too much effort to make it do what we needed.

This script is entirely rewritten using the Model-View-Controller architecture. It begins by collecting all the report data from the database(s), calculating the math as required, and then populating internal data structures with the bandwidth data. Internally, the values are always represented with the least possible degree of calculation. During output, the values are converted and output is based on the settings (columns, units, etc) selected.

The only fully implemented export format is CSV but adding other export options is trivial. The addition of an SQL export is planned and there is a subroutine named 'network_plain' which shows a way to generate a plain text report. It can be hacked as necessary. I highly recommend using the CSV export as there are no field length limits, the headers are dynamically generated based on config file/command line settings, and the resulting files can be easily imported into other applications.

CONFIGURATION

Configuration and syntax is defined in the rtgreport.conf file. The default location is in /usr/local/etc. See the example configuration file for additional documentation.

DEPENDENCIES

Uses the following perl built-in modules: English, Getopt::Long

Also uses the following perl modules available from CPAN: Date::Calc, DBIx::Simple, MIME::Lite, Net::SMTP, Text::CSV, Pod::Usage, Config::Std

CHANGES

v1.5.0 - Mar 01, 2008
- moved config values into rtgreport.conf
- added missing documentation 
- renamed pod to net, less LT specific and more generally applicable
- added network_plain subroutine as a code example implementing plain text
- refactored much code info Report.pm (shared with bandwidth report)
v1.1.5 - Feb 07, 2008
- bug fix, reports with end dates of < 10 needed zero padding for the day value
- append missing descriptions list to -desc reports
v1.1.4 - Feb 04, 2008
- added units to header of 95th column
v1.1.3 - Jan 28, 2008 - -units option accepts mb or gb as arguments
v1.1.2 - Jan 25, 2008
- include the runtime messages in the email report
- CLI -desc can optionally be a file with interface descriptions, one per
  line.
v1.1.1 - Jan 24, 2008
- only insert the CSV header line once (instead of once per net) - per Tim J
- new CLI option: -desc, only match interfaces with that description
- new CLI option: -nosum, suppress summary fields
v1.1.0 - Jan 17, 2008
- added net documentation
- added dc name to each net definition
- added CLI options: gig, help, rates, units, util
- units now sets the units (K/M/G) for the report
- print H:M:S in start/stop timestamps
- report uplink/downlink/isp links separately
- only display averages and utilization if selected on cli
- duplicate timestamps can occur when "falling back"
   during DST. Instead of ignoring the entry, add the
   counters and ignore the timestamp.
- calculate 95th percentile if gig option
v1.0.1 - Jan 10, 2008
- made the site config options command line options
- added filtering logic to weed out vlans, etc.
- sort the networks in numberic order
- commented out utilization values
v1.0 - Jan 07, 2008
- initial authoring - based on RTG's report.pl

TODO

Probably: Open a second database connection and dump the results into an SQL table

Done - Abstract many subroutines into a class. Doing so would make it easy to recycle the functions and write a suite of tests. The practical advantages of this have declined since I have integrated many of our RTG functions into this script.

AUTHOR

Matt Simerson <msimerson@cpan.org>

ACKNOWLEDGEMENTS

Based on report.pl by Rob Beverly and Adam Rothschild.

LICENSE AND COPYRIGHT

Copyright (c) 2008 Layered Technologies, Inc. All rights reserved.

This module is free software; you can redistribute it and/or modify it under the same terms as Perl itself. See perlartistic.

This program is distributed in the hope that it will be useful, but WITHOUT ANY WARRANTY; without even the implied warranty of MERCHANTABILITY or FITNESS FOR A PARTICULAR PURPOSE.