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