Generating email-based reports with inline charts

[nclemons]

One of the things that we did over the last few months at my company was migrate from Nagios + Cacti over to Zabbix. We've found a few instances where Nagios works a little bit better, but Cacti has been almost completely supplanted-- and exceeded-- by Zabbix.

The one thing that we are able to do in Cacti is to do regular email-based reports. An example would be for new registered users, so we can see the trend and be alerted to any sharp rises or dips in the registration rate. (While we might be able to do this via trend-based triggers, that doesn't necessarily fulfill the requirements of the management team who want to see the report). The images for these reports are directly embedded into the email so when you click on the email you see the report right away.

I understand that Zabbix does not have this functionality. Core Cacti didn't, either, it was an additional plugin that added it. I've seen recommendations from folks through searches to use Jasper + iReports, documented well here. While this is a painful kludge to recreate all the graphs we want when they're already better presented inside of Zabbix, it would be viable if Jasper actually supported scheduled emails where the images were embedded directly inline. If you specify HTML, it generates the HTML + images and attaches a ZIP file to the email. Not what I'm looking for. And I'd prefer to avoid the overhead of PDF files as well.

Does anyone have any recommendations as to what I can use, or what I can modify, to get this kind of email-based report with the charts inline?

Thanks for your assistance.

[danrog]

Someone else wrote a script that worked exactly like Cacti's daily report email. Basically emailed graphs to a user wrapped in an HTML email so images were displayed inline. I've taken that script and completely overhauled it. This script will email each user their favorite graphs and screens (currently only works on static screens). And I limited to weekly or monthly graphs (although you can change that fairly easily if you know perl). The email address is taken from the DEFAULT media type email. So if you changed that, you will have to modify the SQL query to match up with your media ID.

#Send zabbix reports
#  Weekly starts on Monday so we can grab the weekend in one graph
15 2 * * 1 root /usr/bin/perl /home/zabbix/scripts/graphreport.pl 2>&1 >/dev/null
#  Monthly report
15 1 1 * * root /usr/bin/perl /home/zabbix/scripts/graphreport.pl -month 2>&1 >/dev/null

#!/usr/bin/perl -w
use DateTime;
use File::Path;
use MIME::Lite;
use DBI;

my $zabbix      = "zabbix.domain.com/zabbix";
my $login       = 'zabbix';     # zabbix api user
my $pass        = 'password' # zabbix api pass
my $cookie      = "/tmp/cookie.txt";

my $width       = 570;
my $height      = 234;

my $dsn         = 'DBI:mysql:zabbix:zabdb';
my $db_user_name = 'zabbix';
my $db_password = 'zabbix';
my %user_screen;
my %graphs;
my $period;
my $type;

my $path 	= '/tmp/graphreport';
if(-e $path) { rmtree($path); }
mkdir($path);

my $dt = DateTime->now( time_zone => 'floating' );    # create a DateTime object set to the current date and time, with no particular timezone

if ($ARGV[0] eq "-month") {
	$dt->subtract( months => 1 )->set_day(1)->set_hour(0)->set_minute(0);
	$lt = $dt->clone->add( months => 1 )->subtract( days => 1 )->set_hour(0)->set_minute(0);
	$period	= 2592000;
	$type	= "Month of";
} else {
	$dt->subtract( days => 7 )->set_hour(0)->set_minute(0);
	$lt = $dt->clone->add( days => 7 )->set_hour(0)->set_minute(0);
	$period	= 604800;
	$type	= "Week of";
}


#if ($dt->day >= 25) {
#    $dt->set_month( $dt->month - 1 );    # set the month to it's current value plus 1
#    $dt->set_day( 1 );    # set the day to the 1st
#}
my $stime 	= $dt->strftime("%Y%m%d%H%M%S"); 
my $repstart 	= $dt->strftime("%a, %b %d, %Y"); 
my $repend 	= $lt->strftime("%a, %b %d, %Y"); 

my $query	= " SELECT p.value_id, IF(s.resourceid IS NULL,p.value_id,s.resourceid) as graphid, p.idx, IF(s.resourceid IS NOT NULL,ss.name,'Graphs') as title, 
			u.name, u.surname, u.userid, m.sendto, p.source FROM profiles p
			LEFT JOIN screens_items s ON ( p.value_id = s.screenid )
			LEFT JOIN screens ss ON ( s.screenid = ss.screenid )
			LEFT JOIN users u ON ( p.userid = u.userid )
			LEFT JOIN media m ON ( u.userid = m.userid ) 
		    WHERE (p.idx like 'web.favorite.screenids' OR p.idx like 'web.favorite.graphids')
			AND m.mediatypeid = 1;";

my $db		= DBI->connect($dsn, $db_user_name, $db_password);
my $zab		= $db->prepare($query);
$zab->execute();

my $cmdLogin 	= `curl  -s -c $cookie -d 'form=1&form_refresh=1&name=$login&password=$pass&enter=Enter' $zabbix/index.php`;

while (my ($value_id, $graphid, $idx, $title, $first, $last, $userid, $email, $source) = $zab->fetchrow_array()) {
        my $output = "$path/$title.$graphid.png";
        if ($source eq "itemid") {
		$chart = "chart.php?itemid";
	} else {
		$chart = "chart2.php?graphid";
	}
	my $cmdImage = `curl  -s -b $cookie -o "$output" '$zabbix/$chart=$graphid&period=$period&stime=$stime&width=$width&height=$height'`;

	$user_screen{$userid}{'email'} = $email;
	$user_screen{$userid}{'name'} 	= $first;
	push( @{ $user_screen{$userid}{'graphs'} }, $title.":".$graphid ); 
	push( @{ $user_screen{$userid}{'titles'}{"$title"} }, $graphid ); 
}


$db->disconnect();

foreach $userid (keys(%user_screen)) {
	print "Sending: $user_screen{$userid}{'name'} => $user_screen{$userid}{'email'}\n";
	my $subject 	= "$type $repstart: Zabbix report for $user_screen{$userid}{'name'}";
	my $msg 	= MIME::Lite->new(
		From    => '[email protected]',
                To      => $user_screen{$userid}{'email'},
                Subject => $subject,
                Type    => 'multipart/related' );
	my @usergraphs 	= @{ $user_screen{$userid}{'graphs'} };
	my $phtml 	= "zzzzzz";
	my $pattach 	= "zzzzzz";
        
	my $str 	= "<body style=\"font-family: Helvetica, sans-serif\;\">\n";
	$str		.="<p style=\"font-size: 18px\; font-weight: bold\;\">$type $repstart through $repend</p>\n";
	foreach my $title (keys%{$user_screen{$userid}{'titles'}}) {
		#print "$title => @{ $user_screen{$userid}{'titles'}{$title} }\n";
		$str .= "<br>\n<h3>$title</h3>\n"; 
        	my $i = 0;
        	foreach my $g (@{ $user_screen{$userid}{'titles'}{$title} }){
                	$str .="<br>\n<div align=\"center\"><img src=\"cid:$g.$i.png\"></div>\n";
                	$i++;
			#print "  Attaching: $g\n";
		}
		$str .= "<br><hr width=\"300\" align=\"center\"><br>\n";
    	}
	$str		.= "<p>You can change the graphs sent to you by changing your saved favorite graphs/items from the dashboard.</p>\n";
    	$str 		.= "</body>\n";
    	$msg->attach( Type => 'text/html; charset= utf-8',#windows-1251', 
			Data => $str );

	foreach my $title (keys%{$user_screen{$userid}{'titles'}}) {
		my $i = 0;
		foreach my $g (@{ $user_screen{$userid}{'titles'}{$title} }){
			my $file = "$path/$title.$g.png";
                	$msg->attach( Type        => 'image/png',
                        	      Path        => $file,
                      	      	      Id          => "$g.$i.png");
               		#print "$g.$i.png   =>   $file\n";
                	$i++;
		}       
        }
	$msg->send();
}

exit ;

[subba5678]

Hi danrog,
Where i need to copy the below script . Please help me in this

Thanks,
Subbu

[danrog]

Where ever you want. This is run from either cron or manually. As long as you have remote access to the DB and web interface, you can run this script from anywhere. The only dependencies are perl and a few CPAN modules.

[subba5678]

Hi Danrog,

Thanks for the replay , I already installed perl and a few CPAN modules as well as i have remote access to the DB . Can you please help me where i need to add the below query . So that i can use this feature in zabbix



Thanks,
Subbu

[nelsonab]

I already installed perl and a few CPAN modules as well as i have remote access to the DB . Can you please help me where i need to add the below query . So that i can use this feature in zabbix

Your question is a little ambiguous. What do you mean by "where i need to add?" You mean where do you put the script? Anywhere you want. You just need to modify the variables near the top to apply to your installation with the appropriate values. Then execute the script. From what I can tell it pretty much takes care of the rest, sending emails to the addresses given within Zabbix itself per user.

[subba5678]

hi nelsonab,

Thanks for the update . I tried to send the report through the E-mail and assaigned a cron Job for the same , for testing i gave the todays date and time but i didnt get any mail for the same . Please find the below cronjob details and help me out .

Cron Job

23 10 * * * /root/subbu /home/zabbix/scripts/graphreport.pl 2>&1 >/dev/null


Cron Job Logs:

Dec 20 23:10:01 localhost crond[11694]: (root) CMD (/usr/lib/sa/sa1 1 1)

Thanks,
Subbu

[nelsonab]

Cron Job

23 10 * * * /root/subbu /home/zabbix/scripts/graphreport.pl 2>&1 >/dev/null


Cron Job Logs:

Dec 20 23:10:01 localhost crond[11694]: (root) CMD (/usr/lib/sa/sa1 1 1)

hmm.... what is the script /root/subbu ?

Also try not piping stderr and stdout to /dev/null, then check the email for the user who is running cron after the job should have run.

[subba5678]

Hi nelsonab ,

Thanks for the replay. /root/subbu consists of the above code( DB NAME ; Mail ID e.tc) . I changed the cronjob to test it still i didnt get the email . Please help me out.

00 10 * * * /usr/bin/subbu /home/zabbix/scripts/graphreport.pl

Thanks,
Subbu

[nclemons]

@danrog, that's exactly what I was looking for. I changed the curl calls to have -k in them so they don't care about SSL certificate verification since I'm passing them against localhost, but otherwise that did the trick.

Thanks very much!

[nclemons]

I did notice a problem with the screen logic between created graphs and simple graphs. The following change resolved the issue:

my $query   = " SELECT p.value_id, IF(s.resourceid IS NULL,p.value_id,s.resourceid) as graphid, s.resourcetype, p.idx, IF(s.resourceid IS NOT NULL,ss.name,'Graphs') as title, 
            u.name, u.surname, u.userid, m.sendto, p.source FROM profiles p
            LEFT JOIN screens_items s ON ( p.value_id = s.screenid )
            LEFT JOIN screens ss ON ( s.screenid = ss.screenid )
            LEFT JOIN users u ON ( p.userid = u.userid )
            LEFT JOIN media m ON ( u.userid = m.userid ) 
            WHERE (p.idx like 'web.favorite.screenids' OR p.idx like 'web.favorite.graphids')
            AND m.mediatypeid = 1;";

my $db      = DBI->connect($dsn, $db_username, $db_password);
my $zab     = $db->prepare($query);
$zab->execute();

my $cmdLogin    = `curl -k -s -c $cookie -d 'form=1&form_refresh=1&name=$login&password=$pass&enter=Enter' $zabbix/index.php`;

while (my ($value_id, $graphid, $rtype, $idx, $title, $first, $last, $userid, $email, $source) = $zab->fetchrow_array()) {
    my $output = "$path/$title.$graphid.png";
    if ($source eq "itemid") {
        $chart = "chart.php?itemid";
    } else {
        if ($rtype == 1) {
            $chart = "chart.php?itemid";
        } else {
            $chart = "chart2.php?graphid";
        }
    }

[exkg]

Is IT a choice ?


[]s,
Luciano

[nclemons]

PDF isn't what I'm interested in... I could do that with Jasper, but I want inline images.

The script danrog provided does what I want, I've just had to tweak it slightly to get it perfect

[ke_sheng_jie]

Awesome!

This works great, with 1.8.10, after I modified it for my own needs. If you're using distributed monitoring, you have to modify the query so that the mediatypeid is in the form 200200000000001 instead of 1.

[hammer]

Has anyone has any issue with perl dependencies like DBI::mysql. I'm running a 64 bit mysql db on solaris and it's constantly failing with DBD::mysql