billing_report.pl - extract usage data from RTG databases, particularly for bandwidth billing.
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
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
By default, interfaces whose name contains vlan and port-channel are filtered out. Selecting this option prevents the filtering of those interfaces.
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
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.
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.
Prints out the pod documentation.
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.
Suppresses the 95th percentile columns in the report.
Suppresses the rate columns in the report. The four rate columns are the average in/out Mbit/s and max in/out Mbit/s.
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.
If you have many networks configured, you can run a report for just one of them by specifying it with the -net option.
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.
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).
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.
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.
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 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.
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
- 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)
- bug fix, reports with end dates of < 10 needed zero padding for the day value - append missing descriptions list to -desc reports
- added units to header of 95th column
- include the runtime messages in the email report - CLI -desc can optionally be a file with interface descriptions, one per line.
- 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
- 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
- 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
- initial authoring - based on RTG's report.pl
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.
Matt Simerson <email@example.com>
Based on report.pl by Rob Beverly and Adam Rothschild.
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.