Redmine Alert on New Admin or Public Project

September 24, 2010

Problem:
How can I receive an email when the Redmine admin list changes or a project is changed to publicly viewable? You will need to run some mysql commands / queries in windows batch mode.

Solution:

  • Download blat and place in c:\windows\system32
  • Create directory c:\1admin and place these files in it:

These are the queries you will be running in mysql batch mode….
redmineAdminUsers.sql

use bitnami_redmine;
SELECT mail INTO OUTFILE "C:/1admin/bitnami_admins.txt"
FIELDS TERMINATED BY ',' OPTIONALLY ENCLOSED BY '"'
LINES TERMINATED BY "\n"
FROM users where admin = 1;

redminePublicProjects.sql

use bitnami_redmine;
SELECT name INTO OUTFILE "C:/1admin/bitnami_projects.txt"
FIELDS TERMINATED BY ',' OPTIONALLY ENCLOSED BY '"'
LINES TERMINATED BY "\n"
FROM projects where is_public = 1;

This batch file will execute the queries in mysql batch mode and compare their previous query results
bitnami_compare_changes.bat

cd ..
cd \
c:
cd ..
cd ..
cd 1admin
del bitnami_admins_old.txt
move bitnami_admins.txt bitnami_admins_old.txt
mysql --database=bitnami_redmine --user=root --host=localhost --password=[your_root_password] < redmineAdminUsers.sql fc bitnami_admins.txt bitnami_admins_old.txt | FIND "FC: no dif" > nul
   IF ERRORLEVEL 1 goto :s_files_are_different

GOTO :checkProjects

:s_files_are_different
del c:\1admin\diffuser.txt
fc bitnami_admins.txt bitnami_admins_old.txt 1>>c:\1admin\diffuser.txt 2>&1
blat c:\1admin\diffuser.txt -to [email protected] -f [email protected] -s "Redmine Admin List Has Changed" -server smtp.example.com

:checkProjects
cd ..
cd \
c:
cd ..
cd ..
cd 1admin
del bitnami_projects_old.txt
move bitnami_projects.txt bitnami_projects_old.txt
mysql --database=bitnami_redmine --user=root --host=localhost --password=[your_root_password] < redminePublicProjects.sql fc bitnami_projects.txt bitnami_projects_old.txt | FIND "FC: no dif" > nul
   IF ERRORLEVEL 1 goto :s_files_are_different2

GOTO :End

:s_files_are_different2
del c:\1admin\diffproject.txt
fc bitnami_projects.txt bitnami_projects_old.txt 1>>c:\1admin\diffproject.txt 2>&1
blat c:\1admin\diffproject.txt -to [email protected] -f [email protected] -s "Redmine Project Public Viewable List Has Changed" -server smtp.example.com

:End

Now set up a windows scheduled task to that .bat file every thirty minutes. Optionally only allow users SYSTEM and the user you are running the bat file as access to the file (to protect root password).