Bulk Clear Failed Commands with MySQL Event

[chzar]

Background: I work as a Helpdesk Manager at a school with a 1:1 iPad Program. Our department was having a problem where failed commands seem to prevent further commands from completing successfully. It seems this is a common problem with JSS or APN. Whichever the case, me and a coworker created a MySQL event to clear all failed commands from the JSS database every night. I wanted to share this with the rest of the nation because a) it seems that other people are having the same problem and b) I am not a great database programmer and need the hivemind to help.

Disclaimer: I'm not a dev and I am not exactly sure how JSS uses its own database. I don't know if deleting the failed commands from the database actually makes JSS allow subsequent commands to complete or just makes them disappear from the JSS GUI. I simply have no way to test this for sure. However, in my experience it does seem to have the exact same effect as hitting the "cancel" button next to failed commands.

Tools Needed: MySQL Workbench - much easier to use than the MySQL terminal

Procedure: So the idea behind this is to create a stored procedure with the query we want to run and then to call it within a MySQL event. This is mostly so we can schedule the query to run on a schedule or call it whenever we want (mostly for testing). Open MySQL workbench and login (the user shouldn't matter but I used root). Open a new query and paste in the following:

DELIMITER //
CREATE PROCEDURE JSS_Cleanup ()
BEGIN #Removes failed commands from Mobile Device Management Commands SELECT COUNT(*) FROM jamfsoftware.mobile_device_management_commands where apns_result_status = 'Error'; DELETE FROM jamfsoftware.mobile_device_management_commands where apns_result_status = 'Error';
END //

DELIMITER ;

This query will create a procedure that searches the mobile_device_management_commands table for failed commands and delete them. The SELECT line is not strictly needed but is useful for testing. When you are ready, run this query.

Open a new query and paste in the following.

CREATE EVENT JSS_Cleanup_Event
ON SCHEDULE EVERY 1 DAY STARTS '2016-01-1 03:00:00'
DO CALL JSS_Cleanup

This query will schedule an event that occurs every day at 3AM and calls the JSS_cleanup procedure.
Run this command and you are finished.

Things I would like this to do: Some kind of logging. MySQL can output to a file, but it cannot append or even overwrite a file. So either you need to create a new file every time this runs or just do without. A log file would be useful for troubleshooting and easily checking if the event is triggering as it should.

In closing, this is the best solution I have found to managing failed commands. I would like to investigate changing the status of commands from "error" to "timedout" or something instead of just deleting them so I at least have a record of devices that are just continually receiving failed commands but maybe when I have more time.

CREDITS

Users ClarkPoole and dgreening for the tip off on where failed commands are stored in the jamfsoftware database.

[bpavlov]

Obligatory feature requests so that solutions like this are not needed. Honestly this should be a high priority because at the end of the day these failures make admins lose confidence in MDM.

https://jamfnation.jamfsoftware.com/featureRequest.html?id=2816

https://jamfnation.jamfsoftware.com/featureRequest.html?id=3610

https://jamfnation.jamfsoftware.com/featureRequest.html?id=3619

[yadin]

Insane that this is an issue, it's a basic failure of the product to do what it's designed for. We have devices that will never succeed because they failed once upon a time and that can't ever be fixed unless you delete the device and start all over. Unacceptable.