For my homework project I need to oversee a web server and a wordpress website on it with a Nagios server. Both are LAMP Debian 9 server, all is setup and working.
I'm using Nagios XI and MariaDB with the default wordpress database explain here.
For the last part I need to check all comments posted in the last 4 hours to avoid a Spam campaign. I need a warning threshold set on 4 comments in last 4 hours, and a critical threshold set on 10 comments in last 4 hours.
I found check_mysql_query.pl plugin for Nagios. But I'am a little bit lost for making work this with a SQL query. According to wordpress doc, I try something like :
SELECT comment-date FROM wp-comments WHERE comment-data > ($current_time - 4h00)
I want to extract the number of new comment add to databases the last 4 hours.
I don't know the sytaxe for ($current_time - 4h00)
And for my plugin command I have something like :
./check_mysql_query.pl -H 192.168.0.1 -u user -p password -d wordpress -q mysqlquery -w 4 -c 10
That's where I am, but the method is free, so if you know how complete this command (with some basics explanations please), it's great. But if you know other ways to do that, I'd be happy to see them too. Thanks.
Ok with the help from the Ярослав Машко I found the perfect commande. What it was awnsered give out a list of comments posted in last 4 hours. This was not exactly the need because it was unusable by Nagios plugins to put a threshold alert. So to have the number of comments in last four hours I've put a count() in th SQL request. This is the good way for me :
SELECT COUNT(c.comment_date) FROM wp_comments as c where hour(timediff( localtime(), c.comment_date ) ) < 4;
I've put this in the check_mysql_query plugin (native in Nagios XI) and it's working fine. Thanks for your support.