Page 1 of 1
Top Alerts Produces Database Query
Posted: Mon Jan 16, 2017 2:57 am
by abhishek
Hi,
I want to generate Top alerts Producers report from backend . Kindly any one suggest me what is the mysql query for that .
Re: Top Alerts Produces Database Query
Posted: Mon Jan 16, 2017 1:03 pm
by gormank
I get the following from nagios.log for the current day. Since the logs are archived, you can go back as far the saved logs.
Top n alerting hosts
Top n alerting services
Top n alerting host/service pairs
Re: Top Alerts Produces Database Query
Posted: Mon Jan 16, 2017 1:04 pm
by rkennedy
That would be a nice grep @gormank! Thanks for the addition.
We have a 'Reports' tab that can generate reports, specifically one for 'Top Alert Producers' - check it out.
As for custom SQL queries, this is not something we'll be able to provide support for. We do not recommend making any direct SQL calls to the database either.
Re: Top Alerts Produces Database Query
Posted: Mon Jan 16, 2017 1:17 pm
by gormank
You can see the commands in $cmd below. The first $cmd just has the beginning of the command, and the next 3 are appended to the 1st.
You can make 3 commands by removing the HTML junk and maybe some escaping. $top is the number of rows or count of top alerters to grab.
Code: Select all
sub get_top_alerters {
my @data;
my $top = 5;
my $cmd = 'grep \'SERVICE ALERT\' /usr/local/nagios/var/nagios.log | grep -v OK | awk ';
my $i = 0;
my @titles = ( "<br>Top $top alerting hosts", "<br>Top $top alerting services", "<br>Top $top alerting host/service pairs" );
my @commands = (
$cmd . "'{print \$4}' | awk -F \\; '{print \$1}' | sort | uniq -c | sort -rn | head -" . $top,
$cmd . "-F \\; '{print \$2}' | sort | uniq -c | sort -rn | head -" . $top,
$cmd . "'{print \$4}' | awk -F \\; '{print \$1 \" \" \$2}' | sort | uniq -c | sort -rn | head -" . $top
);
for my $tmpcmd ( @commands ) {
# print $tmpcmd, "\n";
# next;
my @values = `$tmpcmd`;
chomp @values;
push @data, $trdbs2 . "<b>" . $titles[$i] . ":</b></TD><TD>" . $trde;
for my $row ( @values ) {
my @vals = split(/\s+/, $row);
push @data, $trdb . $vals[2] . "</TD><TD>" . $vals[3] . "</TD><TD>" . $vals[1] . $trde;
}
$i++;
}
return @data;
}
Re: Top Alerts Produces Database Query
Posted: Mon Jan 16, 2017 1:28 pm
by dwhitfield
@abhishek, did that solution work for you?
Thanks
@gormank!
Re: Top Alerts Produces Database Query
Posted: Mon Jan 16, 2017 1:37 pm
by gormank
I removed the escapes so these should work...
Code: Select all
grep 'SERVICE ALERT' /usr/local/nagios/var/nagios.log | grep -v OK | awk '{print $4}' | awk -F \; '{print $1}' | sort | uniq -c | sort -rn | head -5
grep 'SERVICE ALERT' /usr/local/nagios/var/nagios.log | grep -v OK | awk -F \; '{print $2}' | sort | uniq -c | sort -rn | head -5
grep 'SERVICE ALERT' /usr/local/nagios/var/nagios.log | grep -v OK | awk '{print $4}' | awk -F \; '{print $1 " " $2}' | sort | uniq -c | sort -rn | head -5
Re: Top Alerts Produces Database Query
Posted: Mon Jan 16, 2017 2:00 pm
by dwhitfield
@abhishek, did that solution work for you?
Thanks @gormank!
Re: Top Alerts Produces Database Query
Posted: Fri Jan 20, 2017 3:27 am
by abhishek
Hi dwhitfield/gormank ,
Thanks gormank and dwhitfield , In you solution i am only able to fetch host name and total alerts from log file . But i want exact the same structure like in nagios report "Top Alert Producers Section" . If we can generate a Top Alert Producers Data from back end then i want to generate report from external Reporting servers like jasper . Basically , i want to generate all report of nagios via some external Reporting server .
Total Alert Host Service Latest Alert
Re: Top Alerts Produces Database Query
Posted: Fri Jan 20, 2017 1:47 pm
by mcapra
Roughly all of the data used for the "Top Alerts Producers" report is pulled from the
nagios.nagios_statehistory table. If you wanted to programatically generate your own reports, I'd suggest starting there. Rebuilding the query from the ground-up would take quite a lot of time to be honest, but the code used on the back-end is avaialable via the
get_topalertproducers_xml_output function in
/usr/local/nagiosxi/html/includes/utils-xmlreports.inc.php.
Otherwise, you can always download the Top Alerts Producers report as a CSV and feed that into Jasper in some fashion:
2017_01_20_12_45_51_grepWin_C_Users_mcapra_Documents_git_nagiosxi.png