Akom's Tech Ruminations

I use motion as a surveillance system for a sizable office (9 cameras). Motion is good. But the current package of motion in Ubuntu 12.04 is no longer compiled with MySQL support (used to have it in 10.04). Compiling motion from source really didn't sound appealing... but I do want to add events to a database for my own in-house motion history browser. What to do?

Motion supports command execution on events, such as on_movie_end and on_picture_save... which makes it really easy to do a one-liner like this:

on_movie_end bash -c "echo insert into security(camera, filename, frame, file_type, time_stamp, event_time_stamp, event_id) values('%t', '%f', '%q', '%n', '%Y-%m-%d %T', '%C', '%v') | mysql -uUSER -pPASS"

if you'd like to do something fancier, you can break it out into a shell script:

#!/bin/bash

logger -t test "$0 executing with $*"
sql="insert into security(camera, filename, frame, file_type, time_stamp, event_time_stamp, event_id) values('$1', '$2', '$3', '$4', '$5', '$6', '$7')"
echo $sql | mysql -u$U -p$P motion
 

and invoke like this:

on_movie_end U=USER P=PASS bash /usr/local/sbin/motion-insert-event.sh '%t' '%f' '%q' '%n' '%Y-%m-%d %T' '%C' '%v

(same for on_picture_save, if needed). You can also daisy-chain commands with semicolon if you have multiple. Also, note that the query above is for my table, yours may have different columns. The rest are my specific examples of usage:

Continue reading "motion drops MySQL support, but you can get around it with a shell script"