SQL batch command to run a nightly clean up?

[Malcolm]

Is there any way of setting up a batch command, to do the following nightly task?:
i was looking at the mysql command line client, but I wasn't sure if there was a way to run with load with root privileges?

use jamfsoftware;
DELETE FROM jamfsoftware.mobile_device_app_deployment_queue where current_state!="Managed";
DELETE FROM mobile_device_management_commands where apns_result_status != 'Acknowledged';
quit;

[thoule]

To run SQL from command line bash, put the commands in a text file "commands.sql". Then run the mysql command with "<" at the end to pump the file contents into it. Note that there is no space between the -p for password and the secret password. You can also put your name and password in ~/.my.cnf

$ mysql -h "server-name" -u "root" "-pSekretWord" "jamfsoftware" < "/tmp/commands.sql"
$ srm /tmp/commands.sql

Apply obvious disclaimers here.

[Malcolm]

@thoule
after a lot of messing around I've translated it to windows server from a batch file...

"C:Program FilesMySQLMySQL Server 5.6inmysql.exe" -h localhost -u "root" -pROOTPW "jamfsoftware" -e ". c:commands.sql"

Out of curiosity do you know if there is any commands, to force all IOS devices to update?

[Emmert]

You might need to use the API for that one.

[StaciW]

Hey everyone! I just wanted to chime in here and mention that deleting directly from the database is never a good idea, unless we specifically know what we are troubleshooting and why it needs to be done. This can cause issues, such as null pointers, with databases when you've deleted one portion of the information out, but have not removed it from other tables. I would suggest if you are having problems where you believe you need to clear out your pending and failed commands, that you reach out to your JAMF Buddy to investigate the issue.

[nick_pierce]

If you want to run nightly jobs, look into things like launchd on mac: http://superuser.com/questions/126907/how-can-i-get-a-script-to-run-every-day-on-mac-os-x

or cron jobs for linux systems:
http://www.unixgeeks.org/security/newbie/unix/cron-1.html

or AT (command line) or Windows Task Scheduler:
http://stackoverflow.com/questions/132971/what-is-the-windows-version-of-cron

As Staci Dreysse mentioned above though, directly accessing and modifying your database can be quite risky. Without knowing the entire swath of all areas of the system a single row can affect, deleting it or modifying the contents of the information in the database can have rippling effects across the system and especially so when the tables are MDM related.