Script to create stacked graph for one metric for all hosts associated with one group or one template.
Code:
#!/usr/bin/perl -w
#
# by Eugene Aleynikov
# alj <at> myway.com
# type perldoc scriptname
# to get help message
# this script works only with 1.4.2 version of zabbix, single node server
#
my $svnid='$Id:$';
use strict;
use DBI;
use DBD::mysql;
use Getopt::Long;
use Pod::Usage;
my $DEBUG=0;
my $verbose=0;
my @colors=qw(FFF200 FFAB00 562B29 FF0000 55009D 00FF00 008A77 6DC8FE C4FD3D
FF00FF 2175D9 C0C0C0 B90054 000000 AFECED B90054 8D00BA EA8F00 0000FF F51D30);
my $maxcounter=scalar @colors;
my $dbuser='root';
my $dbpass='';
#
my ($cmd,$template, $group, $itemkey, $graphname);
GetOptions("help" => sub { pod2usage(1) },
"template=s" => \$template,"group=s" => \$group,
"itemkey=s" => \$itemkey,"graphname=s" => \$graphname,
"debug" => \$DEBUG, "verbose"=> \$verbose, "cmd=s" => \$cmd);
pod2usage(-verbose => 1, -exitval => 2,
-message => 'Cmd should be specified') unless defined $cmd;
pod2usage(-verbose => 1, -exitval => 2,
-message => 'Either specify template or group') unless defined $template or defined $group;
if ($cmd=~/^create$/i) {
pod2usage(-verbose => 1, -exitval => 2,
-message => 'Itemkey must be defined') unless defined $itemkey;
pod2usage(-verbose => 1, -exitval => 2,
-message => 'Graph name needed') unless defined $graphname;
} elsif ($cmd=~/^list$/i) {
pod2usage(-verbose => 1, -exitval => 2,
-message => 'Itemkey must be defined') unless defined $itemkey;
} else {
pod2usage(-verbose => 1, -exitval => 2,
-message => 'Incorrect command');
}
my $hosts;
my $dsn='DBI:mysql:database=zabbix;host=127.0.0.1;port=3306';
my $sql;
#########
# read host groups
my $dbh=DBI->connect($dsn, $dbuser, $dbpass) or die $!;
if (defined $template) {
$sql="SELECT h.host AS host, h.hostid AS hostid, i.itemid AS itemid FROM hosts_templates AS h_tpl JOIN hosts AS h ON h.hostid = h_tpl.hostid JOIN hosts AS t ON t.hostid = h_tpl.templateid AND t.status = 3 JOIN items AS i ON h.hostid=i.hostid WHERE t.host = " . $dbh->quote($template) . ' AND i.key_ = ' . $dbh->quote($itemkey);
} else {
$sql="SELECT h.host AS host, h.hostid AS hostid, i.itemid AS itemid FROM hosts_groups AS h_grp JOIN hosts AS h ON h.hostid = h_grp.hostid JOIN groups AS g ON g.groupid = h_grp.groupid JOIN items AS i ON h.hostid=i.hostid WHERE g.name = " . $dbh->quote($group) . ' AND i.key_ = ' . $dbh->quote($itemkey);
}
my $sth=$dbh->prepare($sql) or die "$dbh->error";
$sth->execute or die "$dbh->error";
die "No hosts found" unless $sth->rows;
while (my $ref = $sth->fetchrow_hashref()) {
$hosts->{$ref->{host}}{hostid}=$ref->{hostid};
$hosts->{$ref->{host}}{itemid}=$ref->{itemid};
print "$ref->{host} -> $ref->{hostid} : $ref->{itemid}\n" if $DEBUG > 1;
}
$sth->finish;
$sth=undef;
if ($cmd=~/^create$/i) {
$sql='SELECT graphid FROM graphs WHERE name = '.
$dbh->quote($graphname);
$sth=$dbh->prepare($sql) or die "$dbh->error";
$sth->execute or die "$dbh->error";
die "Graph with this name already exists" if $sth->rows;
$sth->finish;
print "Creating graph $graphname\n" if $verbose;
$dbh->do("UPDATE ids SET nextid=nextid+1 where nodeid=0 and table_name='graphs' and field_name='graphid'") or die "$dbh->error";
$sql="select nextid from ids where nodeid=0 and table_name='graphs' and field_name='graphid'";
$sth=$dbh->prepare($sql) or die "$dbh->error";
$sth->execute or die "$dbh->error";
my $ref = $sth->fetchrow_hashref() or die "Emtpy ref\n";
my $graphid=$ref->{nextid};
$ref=undef;
$sth->finish;
die "Unable to calculate index\n" unless $graphid;
$sql=
'INSERT INTO graphs (graphid, name, width, height, yaxismax , graphtype) VALUES ( '
. $graphid . ', '. $dbh->quote($graphname) .' , 900, 200, 10000.0, 1)';
$dbh->do($sql) or die "$dbh->error";
my $counter=0;
foreach my $host (sort keys %{$hosts}) {
die "Max number $counter of graps reached\n"
if $counter > $maxcounter;
print "Adding $host\n";
$dbh->do("UPDATE ids SET nextid=nextid+1 where nodeid=0 and table_name='graphs_items' and field_name='gitemid'") or die "$dbh->error";
$sql="select nextid from ids where nodeid=0 and table_name='graphs_items' and field_name='gitemid'";
$sth=$dbh->prepare($sql) or die "$dbh->error";
$sth->execute or die "$dbh->error";
$ref = $sth->fetchrow_hashref() or die "Emtpy ref\n";
my $gitemid=$ref->{nextid};
$ref=undef;
$sth->finish;
die "Unable to calculate index\n" unless $gitemid;
$sql='INSERT INTO graphs_items (gitemid, graphid, itemid, sortorder, color, drawtype ) VALUES ( '
. $gitemid . ', '
. $dbh->quote($graphid) . ', ' . $hosts->{$host}{itemid}
. ', ' . $counter . ', ' . $dbh->quote($colors[$counter])
. ', 1 )';
$dbh->do($sql) or die "$dbh->error";
$counter++;
}
} elsif ($cmd=~/^list$/i) {
foreach my $host (sort keys %{$hosts}) {
print "$host\n";
}
}
$dbh->disconnect;
$dbh=undef;
__END__
=head1 Stacked Graph wizard
stackwizard - Provides easy way to create stacked graphs
=head1 SYNOPSIS
stackwizard --cmd=command [options]
=head1 OPTIONS
=over 8
=item B<--cmd=create --itemkey=key [--group=group|--template=template] \
--graphname='Graph Name'>
Creates new graph for hosts associated with group or template
=item B<--cmd=list --itemkey=key [--group=group|--template=template]>
Creates nothing, but just lists hosts
=item B<--debug>
Enables debug messages
=item B<--verbose>
Enables verbose output
=item B<--help>
Prints brief help
=back
=head1 DESCRIPTION
=cut
#################
# EOF
#################
Comment