Top Alerts Produces Database Query

[abhishek]

Hi,
I want to generate Top alerts Producers report from backend . Kindly any one suggest me what is the mysql query for that .

[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

[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.

[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;
}

[dwhitfield]

@abhishek, did that solution work for you?

Thanks @gormank!

[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

[dwhitfield]

@abhishek, did that solution work for you?

Thanks @gormank!

[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

[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