Reporting: Top Talkers, busiest servers - PLUGIN AVAILABLE

[bradley]

Hi all,

A rather urgent need of ours is for some kind of reporting tool that can present a list of busiest sites, busiest servers etc. and currently there is no way of "sorting" the graphs
in either "List" or "Preview" mode.

I am aware that something along these lines is in the pipe-line:
http://forums.cacti.net/viewtopic.php?t ... highlight=

But as I just said, we kind of need it now already.

So... I'm thinking of writing a Perl "adapter" that can produce various reports for these kinds of things, but before I begin if the reporting module discussed in the link above is near ready I may be able to wait just a little bit longer...

Does any body have any up-to-date information on this?

Regards
--
Brad.

[bradley]

Hi all

Attached is my first attempt at producing reports in Cacti.

Any way click here to download it:

http://www.kitefamily.co.uk/cacti-reports.zip

README file included below:

[EDIT] - 21/03/2006

I have converted this into a plugin now. Download the above link for
the plugin.

This package adds daily/weekly/monthly/yearly reporting functionality to Cacti.

HOW TO INSTALL:

1: unzip this package into your cacti plugins directory:
cp cacti-reports.zip /path/to/cacti/plugins/
cd /path/to/cacti/plugins && unzip cacti-reports.zip

2: Open up runReports.pl in your text editor: You need to change 2 things:

a: The path to your RRD-Tool installation
b: Your Cacti Database Username/Password

3: Add the plugin to your 'include/config.php' file

$plugins[] = 'reports';

4: Set up a cron-job:

# Daily Report
0 0 * * * perl /data/cacti/plugins/reports/runReports.pl 1
# Weekly Reports
0 0 * * 1 perl /data/cacti/plugins/reports/runReports.pl 2
# Monthly Reports
0 0 1 * * perl /data/cacti/plugins/reports/runReports.pl 3
# Yearly Reports
0 0 1 1 * perl /data/cacti/plugins/reports/runReports.pl 4

The only parameter given to runReports.pl must be the RRA ID number - unless you've
messed with your cacti installation they are as follows:
1 - Daily 5 minute average
2 - Weekly 30 minute average
3 - Monthly 2 hour average
4 - Yearly 1 day average

The above cron-tab runs the daily reports every day at 00:00,
the weekly reports every Monday at 00:00,
the monthly reports on the 1st of each month at 00:00
the yearly reports on Jan 1st at 00:00 every year.

You can run the script by hand if required - however - it reports on data from
the CURRENT TIME backwards for the duration of the RRA, so the daily reports
runs on the last 24 hours from the current time. Therefore, if you run the reports
at 13:47 then the data in your report will be from 13:47 the previous day until the
current time. To correct this and to run the reports as if it were midnight, add
the '--start' parameter to runReports.pl.

5: Configure which reports you want produced using the new "Report Settings" console menu item
in your web browser.

[flavour]

This looks *great* & is something I'm very keen on too :)

Any chance of using the Plugin Architecture (cactiusers.org) instead of patching the core code directly?

The patch you provide is 'reports.patch' not 'cacti-reports.patch' as in your docs.
It also fails to apply to the current version: 0.8.6h (easily fixed):

Code: Select all

patching file lib/functions.php
Hunk #1 FAILED at 1341.
1 out of 1 hunk FAILED -- saving rejects to file lib/functions.php.rej

With the plugin architecture patch applied it gets worse (although, again, easily fixed):

Code: Select all

patching file include/config_arrays.php
Hunk #1 succeeded at 250 (offset 2 lines).
patching file include/config_form.php
patching file include/top_graph_header.php
Hunk #1 FAILED at 95.
1 out of 1 hunk FAILED -- saving rejects to file include/top_graph_header.php.rej
patching file include/top_header.php
Hunk #1 FAILED at 35.
1 out of 1 hunk FAILED -- saving rejects to file include/top_header.php.rej

The SQL tables would ideally be called 'plugin_reports', etc instead of just 'reports', etc This makes them easier to identify as being non-core code (although I'd love to see it included in the core!)


The reports.sql seems to be missing some structure:
DBD::mysql::st execute failed: Unknown column 'report_definitions.result_limit' in 'field list' at runReports.pl line 566.
Could not execute SELECT

I can confirm that this field doesn't exist in the database nor in reports.sql

F

[bradley]

Thanks for the feedback.

I've updated the documentation and have fixed the issue with runReports.pl - this column is no longer required but it still existed in
my database. Sorry about that - download the .zip file again and it should be fixed.

I now include 2 patch files: One for version 0.8.6g, the other for 0.8.6h,
apply the one for which ever version you are using and it should go without any problems now.

With regards to plugins etc, the main issue I have is making sure that I have an upgrade path, and the best way of doing that currently is to support integration with a base-line cacti distribution - for our environment we don’t use any plugins - HP-OpenView/NNM provide the network management/monitoring so no need for THold etc.

Ideally I'd like to see this become core code because that solves my upgrade-path issue. Also, and this may not be correct, but I'm hoping that
by providing a patch to the base code then there's more chance of it being included in the core distribution because it will be easier for the maintainers to incorporate.

Rony, cigamit, Linegod rax and TheWitness, could you provide any guidance on this?

Thanks
--
Brad.

[cigamit]

Great add-on. Still trying to a get a few minor things worked out of it. Are you running PHP with E_ALL? As I get a few notices and also a few failing SQL statements when "Report Type" = "Select One"

Notice: Undefined variable: endTime in /var/www/html/report_view.php on line 351
Notice: Undefined index: rra_id in /var/www/html/report_view.php on line 351

in 2 different places you are trying to "select" consolidation_function_id from report_definitions when it is not part of that table.

--

This is ofcourse before the perl script has been actually ran yet, so that may be why you didn't catch them. Also having a few issues with the perl script itself.

Line 97 is missing an ending comma, so the SQL statement fails. I also don't actually get any results in report_items (empty table) either.
I believe the issue is with several of the SQL statements that are missing arguments. For instance this piece of code

Code: Select all

    my $insertResultSth = $dbh->prepare("INSERT INTO
                                           report_items
                                         (
                                              report_id,
                                              local_graph_id,
                                              consolidation_function_id,
                                              graph_item_name,
                                              graph_item_units,
                                              graph_item_value
                                         )
                                         VALUES (?, ?, ?, ?, ?, ?)") || die "Prepare Failed: " . $dbh->errstr;

is completely missing the arguments (that replaces the ?). I see several places like this in the expiration code and elsewhere. Also, do you have any issues with moving the perl script to PHP?

I have also converted this to a Plugin, otherwise I won't be able to run it on my production systems (took about 5 minutes). Just wish I could get it functioning fully.

[bradley]

Hi cigamit,

Can you try download the new zip file (same location) - I have fixed
the issues you mentioned.

The warnings from PHP were harmless but they are fixed now.

consolidation_function_id is not actually needed in the report_definitions
table, but it does exist on my database because I did at some point use it,
I've removed it from the code.

The issues with the perl script have also been resolved, however, the select statements are supposed to be like that - I use prepared statements. If you look a few lines down (in a foreach loop) the prepared statement is being executed() with the correct parameters. This prevents SQL injection attacks etc - I don’t think PHP supports this (AFAIK?) which is why you need to validate the parameters all the time.

I have no issues with having the runReports.pl script converted to PHP, but what would that give you that you don’t already have?

I personally don’t like php as a language but as I say, that’s a personal thing so if somebody would like to port it to PHP then I'm happy for them to do that.

Would you be able to send me the changes needed for the plugin architecture to work please? I could then maintain that too if required.

Oh yeah, and you need to set up which reports you want to run before runReports.pl will produce any thing - check the "Report Settings" item in on the Console.

Lastly, thanks for making Cacti the great product that it is, keep up the good work!

--
Bradley Kite

[flavour]

I have no issues with having the runReports.pl script converted to PHP, but what would that give you that you don’t already have?

Less system requirements are good to make it simpler to deploy.
- if you don't need Perl & the RRD perl modules then less frustration for people trying to make it work.

Personally I have no problem with Perl, but I can see the advantage of avoiding unnecessary dependencies.

F

[bradley]

The RRD modules come with RRDTool so this should not be a problem,
but I suppose if it needs to run on Windows (99% of unix's come with
perl already).

Any takers for porting it?

[Tybio]

Building it as a plugin will let people who do use those tools use this one.

I use a few plugins right now, and want to give this a try. I can not however due to the issue with having two "patches" to the cacti files. I've installed the plugin system so I don't have to face that...which is the point .

As good as this looks, I'll keep waiting for the reports plugin from cactiusers so it intigrates fully.

[cigamit]

Oops, my bad on the SQL statements. Still getting one SQL Failed when viewing the reports, without actually selecting a report, just a little check to ensure "report_definition_id" has a value before that bit of code executes is all that is required.

Code: Select all

03/15/2006 10:51:20 AM - CMDPHP: Poller[0] ERROR: SQL Row Failed "SELECT id, report_definition_id, date_processed FROM reports WHERE report_definition_id = ORDER BY date_processed DESC LIMIT 1"

Also, I still currently get nothing for my reports if I select both a Data Template and a Graph Template for the report. Just setting a Graph Template causes the Report to be inserted, but never displayed on the Report Settings screen, but I actually get info in the report when ran!

[bradley]

cigamit, it looks like you're running it from the poller?

What bit are you running from the poller becasue the poller should not be running any component that I've created.

Download the file again - I've fixed the issue of having an empty report_definition_id.

Can you send me a quick dump of your report_definition table?

--
Brad.

[cigamit]

cigamit, it looks like you're running it from the poller?

What bit are you running from the poller becasue the poller should not be running any component that I've created.

Download the file again - I've fixed the issue of having an empty report_definition_id.

Can you send me a quick dump of your report_definition table?

--
Brad.

Nope, not running via the Poller (yet, but it eventually will for the Plugin version so that it doesn't require an extra cron job just haven't gotten that far into it yet), but I guess it is a bit misleading. That is from the Cacti Log in v0.8.6h, which says "Poller" but that is actually what it most always says with SQL errors. All I did was click the "Reports" tab to get the error to display. Otherwise currently I am running perl script from the command line.

Also, I should point out that you will probably want to use the validation functions on all your data you are grabbing from POST. First most of security's sake, but also, so that if the "Save" errors out, the validation function is what saves everything to the session, so that it will still be selected (not reset) once you are thrown back to the error page.

Code: Select all

form_input_validate($_POST["data_template_id"], "data_template_id", "^[0-9]+$", false, 3);

You currently have code in there to detect if both the Data Template and the Graph Template are 0, but not if only 1 of them is (the "and" could be changed to an "or" to fix it). This would stop the "disappearance" issue, but I have yet to finish tracking down the issue with it not running reports when both are present.

Here is dump of my testing with the a few blank data templates and graph templates.

Code: Select all

CREATE TABLE `report_definitions` (
  `id` int(11) NOT NULL auto_increment,
  `report_name` varchar(255) default NULL,
  `data_template_id` int(11) default NULL,
  `graph_template_id` int(11) default NULL,
  `rra_id` int(11) default NULL,
  `include_data_from_start_hour` int(11) default NULL,
  `include_data_from_end_hour` int(11) default NULL,
  `include_data_from_start_day` int(11) default NULL,
  `include_data_from_end_day` int(11) default NULL,
  PRIMARY KEY  (`id`),
  KEY `rra_id` (`rra_id`)
) ENGINE=MyISAM DEFAULT CHARSET=utf8;
INSERT INTO `report_definitions` (`id`, `report_name`, `data_template_id`, `graph_template_id`, `rra_id`, `include_data_from_start_hour`, `include_data_from_end_hour`, `include_data_from_start_day`, `include_data_from_end_day`) VALUES (4,'test3',0,35,1,0,23,0,6),(3,'test',0,12,1,0,23,0,6),(5,'test',0,38,0,0,23,0,6),(6,'tes1',58,0,0,0,23,0,6);

[bradley]

Hmm... Strange that you're not getting any reports after you run it...

It definatley works on 0.8.6g so perhaps its a version issue?

I'll try it on 0.8.6.h and see if there are some other issues - and also fix the POST validation too.

My logic for choosing between Data Template and Graph Template was as follows:

If *only* a data template is selected, then only report on items within that data template.

If *only* a graph template is selected, then only report on items that are plotted on the graph, regardless of which data template they are from.

If *both* data template and graph template are supplied then only report on items that are A) on the graph and B) part of the data template.

I dont know if it fully works like that in practice but I know that if you specify BOTH then you should get results because thats what I used on the screenshots I supplied.

Could you add this to line 122 of runReports.pl and send me the output:

print Dumper(\@graphs), "\n";

bradley...remove-this-bit...@kitefamily.co.uk

(Its kind of like print_r() in php).

[cigamit]

Actaully I get results from those, its only when I choose both the data template and the datasource that I don't get any graphs. I was assuming that I had to select both.

The issue with only selecting 1 or the other is that the report doesn't show up in the report settings if both is not selected. To reproduce, just create a new report, select a graph template, don't bother with anything else (or you can set it to weekly, doesn't matter), and Save. Now when you go to the Report Settings again, its not there, but it exists in the database. Seems like just a small tweak to your SQL statement should fix this.

I will email you the output with a clean DB, and both options selected to see if you can figure it out.

[cigamit]

Actaully I get results from those, its only when I choose both the data template and the datasource that I don't get any graphs. I was assuming that I had to select both.

The issue with only selecting 1 or the other is that the report doesn't show up in the report settings if both is not selected. To reproduce, just create a new report, select a graph template, don't bother with anything else (or you can set it to weekly, doesn't matter), and Save. Now when you go to the Report Settings again, its not there, but it exists in the database. Seems like just a small tweak to your SQL statement should fix this.

I will email you the output with a clean DB, and both options selected to see if you can figure it out.

EDIT: I won't bother to email it since the variable is empty. I will go ahead and do some debugging on my own to see if I can locate the issue. Prints out lots of nice stuff with just one item selected.