Jamf Pro MySQL commands

jamfProMySQLCommands.sh

# Find failed commands
select count(*) from mobile_device_management_commands where apns_result_status='Error';

# Find failed VPP commands
select count(*) from mobile_device_management_commands where command='InstallApplication' and apns_result_status='Error';

# Show devices with failed commands
select device_id from mobile_device_management_commands where apns_result_status='Error';

# Show devices with failed VPP commands
select device_id from mobile_device_management_commands where command='InstallApplication' and apns_result_status='Error';

# Delete failed commands
delete from mobile_device_management_commands where apns_result_status="Error";

# Delete failed VPP commands
delete from mobile_device_management_commands where command='InstallApplication' and apns_result_status="Error";

# Show failed MDM commands
select count(*) from mobile_device_management_commands where command NOT IN ("EnableActivationLock","ClearActivationLockBypassCode","EnableLostMode","EraseDevice","PlayLostModeSound","EnableRemoteDesktop","DeviceLock") and apns_result_status="Error";

# Clear failed MDM commands
delete from mobile_device_management_commands where command NOT IN ("EnableActivationLock","ClearActivationLockBypassCode","EnableLostMode","EraseDevice","PlayLostModeSound","EnableRemoteDesktop","DeviceLock") and apns_result_status="Error";

# Create database
CREATE DATABASE <db>;

# Create user
CREATE USER '<dbuser>'@'<host>' IDENTIFIED WITH mysql_native_password BY '<password>';

# Drop user
DROP USER '<dbuser>'@'<host>';

# Show all users
SELECT User, Host FROM mysql.user;

# Grant privileges
GRANT ALL ON <db>.* TO '<dbuser>'@'<host>';
GRANT ALL ON *.* TO 'root'@'%';

# Revoke privileges
REVOKE ALL ON <db>.* FROM '<dbuser>'@'<host>';

# Change user password
ALTER USER '<dbuser>'@'<host>' IDENTIFIED WITH mysql_native_password by '<password>';

# Show grants
SELECT USER,HOST FROM mysql.db where db='<db>';

# Restore DB
mysql -u root -p <dbname> < ./<dbname>.sql

# Repair 
mysqlcheck -u root -p --repair <db>
mysqlcheck -u root -p --optimize <db>

# Upload via mysql client 
zcat < /path/to/*.sql.gz | mysql -h $hostname -u $username -p $dbname

# Show table sizes
SELECT
  TABLE_NAME AS `Table`,
  ROUND((DATA_LENGTH + INDEX_LENGTH) / 1024 / 1024) AS `Size (MB)`
FROM
  information_schema.TABLES
WHERE
  TABLE_SCHEMA = "mmudb"
ORDER BY
  (DATA_LENGTH + INDEX_LENGTH)
DESC;sql