Menu Close

SQL query for DEP-assigned, Un-enrolled Devices

With thanks to @ted.johnsen, the following SQL query will output a .CSV of all DEP-assigned, un-enrolled devices.

SELECT serial_number, description, server_name, FROM_UNIXTIME(substring(device_assigned_date_epoch,1,10)) AS 'Date_Assigned'
FROM device_enrollment_program_devices
INNER JOIN device_enrollment_program_instances ON device_enrollment_program_instances.device_enrollment_program_instance_id = device_enrollment_program_devices.device_enrollment_program_instance_id
WHERE device_enrollment_program_device_id and device_type = 0
IN (select device_enrollment_program_device_id from computer_dep_prestages) and serial_number NOT IN (select serial_number from computers_denormalized)
ORDER BY server_name, Date_Assigned, description
INTO OUTFILE '/allowed/path/to/output/files/DEP-assigned_NOT_enrolled-yyyy-mm-dd-hhmmss.csv'
FIELDS TERMINATED BY ',' ENCLOSED BY '"' LINES TERMINATED BY '
';

Posted in Jamf Pro, SQL

Related Posts