Motion Detection With Zoneminder Via SQL Triggers

Tags: #<Tag:0x00007f617ff6cc18> #<Tag:0x00007f617ff6c790> #<Tag:0x00007f617ff6c600>

A TRIGGER is essentially a bit of SQL automatically executed by the database when operations occur within a table — table activity triggers execution. When ZoneMinder first detects motion, an event is recorded in the database. When motion is no longer detected, the motion event is updated with event info (number of frames, event duration). Since both inserting a motion event and updating the event when motion ends are events within tables, a trigger can use the openHAB REST API to update an item state without much impact to system load.

To add triggers, you will need a SQL client logged into the ZoneMinder database. I use mysql in this tutorial.

Adding UDF’s To MariaDB:

SQL code does not natively POST data to a URI. Creating a trigger which can execute external binaries requires creating a UDF (user-defined function). I am using lib_mysqludf_sys which defines sys_get, sys_set, sys_exec, and sys_eval functions. The sys_get and sys_set functions are used for setting/getting environment variables. The sys_exec function returns the return code from execution, whereas sys_eval returns the output from execution.

After cloning the lib_mysqludf_sys repo to the database server, edit Makefile and set LIBDIR to the appropriate directory for the MariaDB installation (/usr/lib64/mariadb/plugin/ in my case). I also needed to modify the install.sh compilation line to:

gcc -fPIC -Wall -I/usr/include/mysql/server -I. -shared lib_mysqludf_sys.c -o $(LIBDIR)/lib_mysqludf_sys.so

Run install.sh to install and register the user-defined functions in the MariaDB server. Use the SQL CLI to verify sys_exec is working:

MariaDB [zmdb]> SELECT sys_exec('cat /etc/fedora-release'); 
+-------------------------------------+ 
| sys_exec('cat /etc/fedora-release') | 
+-------------------------------------+
| 0                                   |
+-------------------------------------+ 
1 row in set (0.012 sec)

Creating the SQL Trigger:

To create a trigger for motion events, there needs to be a mapping between the monitorID used in ZoneMinder and your openHAB motion alert item name. Obtain a list of monitors from the ZoneMinder database:

MariaDB [zmdb]> select Id, Name from Monitors;
+----+-----------------------------------+ 
| Id | Name                              | 
+----+-----------------------------------+ 
| 10 | IPCam10 - Area 123                | 
| 11 | IPCam11 - Area 234                | 
| 12 | IPCam12 - Area 345                | 
| 13 | IPCam13 - Area 456                | 
| 14 | IPCam14 - Area 567                | 
+----+-----------------------------------+

“Name”, here, is the name shown within ZoneMinder. Identify the corresponding openHAB item name for each camera ID.

Make changes, as needed, to the SQL triggers provided below:

  • Update the IF/THEN section of the trigger with the correct camera ID and openHAB item names.
  • Update the strOpenHABHost variable to your server URL.
  • Our alert items are on when motion is detected. If your item states differ, change the curl command “-d” value
  • If you are using HTTPS to communicate with openHAB, you may need to add “–insecure” to the curl command to ignore certificate errors (or use –cacert to to establish a trust chain).

To create the TriggerMotionOnNewEvent trigger, paste the following into your SQL client:

-- SHOW TRIGGERS 
-- DROP TRIGGER zmdb.TriggerMotionOnNewEvent; 
DELIMITER @@ 
CREATE TRIGGER TriggerMotionOnNewEvent 

AFTER INSERT ON `Events` 
FOR EACH ROW 
BEGIN 

-- variable for local openHAB REST API hostname and port 
DECLARE strOpenHABHost CHAR(64); 
SET strOpenHABHost='http://openhabhost.example.com:8080'; 

DECLARE strCommand CHAR(255); 
DECLARE strCameraName CHAR(64); 
DECLARE iCameraID INT(10); 
DECLARE iResult INT(10); 

-- Translate ZoneMinder IP camera ID with openHAB item name 
SET iCameraID = NEW.monitorID; 
IF(iCameraID = 10) THEN SET strCameraName='IPCam10_Alarm'; 
ELSEIF(iCameraID = 11) THEN SET strCameraName='IPCam11_Alarm'; 
ELSEIF(iCameraID = 12) THEN SET strCameraName='IPCam12_Alarm'; 
ELSEIF(iCameraID = 13) THEN SET strCameraName='IPCam13_Alarm'; 
ELSEIF(iCameraID = 14) THEN SET strCameraName='IPCam14_Alarm'; 
END IF; 

SET strCommand=CONCAT('/usr/bin/curl ', '-s --connect-timeout 10 -m 10 -X PUT --header "Content-Type: text/plain" --header "Accept: application/json" -d "ON" "',strOpenHABHost,'/rest/items/',strCameraName,'/state"'); 
SET iResult = sys_exec(strCommand); 
END; 
@@
DELIMITER ;

The second trigger clears the motion event — setting the camera motion alert item to off when there is no longer motion detected. ZoneMinder updates event records to record and EndTime for the event. This trigger executes any time an Event item is updated, but there is an IF statement that verifies that the EndTime is not null to avoid clearing the motion event too soon.

To create the ClearMotionOnEventEnd trigger, paste the following into your SQL client:

-- SHOW TRIGGERS 
-- DROP TRIGGER zmdb.ClearMotionOnEventEnd; 
DELIMITER @@ 

CREATE TRIGGER ClearMotionOnEventEnd 
AFTER UPDATE ON `Events` 
FOR EACH ROW 
BEGIN 

-- variable for local openHAB REST API hostname and port 
DECLARE strOpenHABHost CHAR(64); 
SET strOpenHABHost='http://openhabhost.example.com:8080'; 

DECLARE strCommand CHAR(255); 
DECLARE iResult int(10); 
DECLARE strCameraName CHAR(25); 
DECLARE iCameraID int(5); 

-- Translate ZoneMinder IP camera ID with openHAB item name 
SET iCameraID = NEW.monitorID; 
IF(iCameraID = 10) THEN SET strCameraName='IPCam10_Alarm'; 
ELSEIF(iCameraID = 11) THEN SET strCameraName='IPCam11_Alarm'; 
ELSEIF(iCameraID = 12) THEN SET strCameraName='IPCam12_Alarm'; 
ELSEIF(iCameraID = 13) THEN SET strCameraName='IPCam13_Alarm'; 
ELSEIF(iCameraID = 14) THEN SET strCameraName='IPCam14_Alarm'; 
END IF; 

IF NEW.EndTime IS NOT NULL THEN 
SET strCommand=CONCAT('/usr/bin/curl ', '-s --connect-timeout 10 -m 10 -X PUT --header "Content-Type: text/plain" --header "Accept: application/json" -d "OFF" "http://',strOpenHABHost,':',iOpenHABPort,'/rest/items/',strCameraName,'/state"'); SET iResult = sys_exec(strCommand); 
END IF; 

END; 
@@ 
DELIMITER ;

When new motion detection events are inserted into the Events database table, the openHAB item corresponding to the camera will be turned on. When the event record is updated with an end timestamp, the openHAB item corresponding to the camera will be turned off.

3 Likes

This is a good tutorial, but I’d there a reason to not just use the Zoneminder binding? IIRC, if the monitor is configured to do motion detection, the alarm channel would be set to ON.

It’s been awhile since I’ve done anything with zoneminder, but I believe there is a way to make it care a rest API call on openHAB when motion is detected too.

Maybe I’m wrong and remembering incorrectly.

The Zoneminder API had some breaking changes. In order to get the binding working, there were a few changes I needed to make in ZM every time I upgraded it. Bigger problem was that the binding polled Zoneminder and significantly increased the load on our server too.

You can set up Zoneminder to perform actions on events using “filers”. Architecturally, ‘now’ filters are not reliably effective. To avoid missing events, the filter had to be for events in the last five minutes. For actions that are not time sensitive, like file cleanup or roll-up reporting, that’s fine. But it’s not a good solution for identifying the FexEx guy who pulled into driveway three minutes ago, dropped the package, rang the bell, and is halfway down the driveway :slight_smile: Dropping FILTER_RELOAD_DELAY was a possibility, but that’s going to impact system load as well. So I thought I’d give the SQL triggers a try.