SQL script to export data from Jamf Pro MySQL

jamfmobiledevicedataexport.sql

-- ==============================================================
-- SQL to export selected data for reporting. 
-- Main interests at this point are MD denormalized, apps, 
-- ==============================================================
-- A lot of the fields could be omitted. Just dump what you really need.
-- ==============================================================


-- Dates to be exported in this format...
SET @EUR=GET_FORMAT(DATETIME,'EUR');

# Export md denormalized
SELECT
  "mobile_device_id",
  "display_name",
  "asset_tag",
  "udid",
  "last_report_id",
  "last_report_date",
  "last_backup_time",
  "last_enrolled_date",
  "is_managed",
  "wifi_mac_address",
  "bluetooth_mac_address",
  "serial_number",
  "ip_address",
  "disk_size_mb",
  "disk_available_mb",
  "disk_percent_full",
  "phone_number",
  "model",
  "model_identifier",
  "model_version_number",
  "os_version",
  "os_version_comparable",
  "os_build",
  "modem_firmware",
  "last_location_id",
  "username",
  "realname",
  "email",
  "department_id",
  "building_id",
  "department_name",
  "building_name",
  "room",
  "phone",
  "position",
  "po_number",
  "vendor",
  "applecare_id",
  "leased",
  "purchased",
  "lease_date",
  "po_date",
  "warranty_date",
  "purchase_price",
  "life_expectancy",
  "purchasing_account",
  "purchasing_contact",
  "hardware_encryption_capabilities",
  "passcode_present",
  "block_level_encryption",
  "file_level_encryption",
  "data_protection",
  "passcode_is_compliant",
  "passcode_is_compliant_with_profile",
  "passcode_lock_grace_period_enforced",
  "iccid",
  "imei",
  "meid",
  "cellular_technology",
  "voice_roaming_enabled",
  "network",
  "carrier",
  "carrier_settings_version",
  "current_mobile_country_code",
  "current_mobile_network_code",
  "home_mobile_country_code",
  "home_mobile_network_code",
  "data_roaming_enabled",
  "is_roaming",
  "battery_level",
  "is_supervised",
  "exchange_active_sync_device_id",
  "is_personal",
  "is_byo_profile_current",
  "tv_password",
  "locales",
  "languages",
  "device_id",
  "device_locator_service_enabled",
  "do_not_disturb_in_effect",
  "activation_lock_enabled",
  "cloud_backup_enabled",
  "last_cloud_backup_date",
  "mdm_profile_removable",
  "system_integrity_state",
  "ble_capable",
  "location_services_enabled",
  "device_name_type",
  "managed_device_name",
  "itunes_store_account_is_active",
  "itunes_store_account_hash",
  "device_certificate_expiration",
  "lost_mode",
  "lost_mode_enabled_date",
  "is_device_multiuser",
  "diagnostic_submission_enabled",
  "app_analytics_enabled",
  "device_type",
  "is_network_tethered"
UNION
SELECT
  mobile_device_id,
  display_name,
  asset_tag,
  udid,
  last_report_id,
  FROM_UNIXTIME((FLOOR(last_report_date_epoch/1000)), @eur),
  FROM_UNIXTIME((FLOOR(last_backup_time_epoch/1000)), @eur),
  FROM_UNIXTIME((FLOOR(last_enrolled_date_epoch/1000)), @eur),
  is_managed,
  wifi_mac_address,
  bluetooth_mac_address,
  serial_number,
  ip_address,
  disk_size_mb,
  disk_available_mb,
  disk_percent_full,
  phone_number,
  model,
  model_identifier,
  model_version_number,
  os_version,
  os_version_comparable,
  os_build,
  modem_firmware,
  last_location_id,
  username,
  realname,
  email,
  department_id,
  building_id,
  department_name,
  building_name,
  room,
  phone,
  position,
  po_number,
  vendor,
  applecare_id,
  leased,
  purchased,
  FROM_UNIXTIME((FLOOR(lease_date_epoch/1000)), @eur),
  FROM_UNIXTIME((FLOOR(po_date_epoch/1000)), @eur),
  FROM_UNIXTIME((FLOOR(warranty_date_epoch/1000)), @eur),
  purchase_price,
  life_expectancy,
  purchasing_account,
  purchasing_contact,
  hardware_encryption_capabilities,
  passcode_present,
  block_level_encryption,
  file_level_encryption,
  data_protection,
  passcode_is_compliant,
  passcode_is_compliant_with_profile,
  passcode_lock_grace_period_enforced,
  iccid,
  imei,
  meid,
  cellular_technology,
  voice_roaming_enabled,
  network,
  carrier,
  carrier_settings_version,
  current_mobile_country_code,
  current_mobile_network_code,
  home_mobile_country_code,
  home_mobile_network_code,
  data_roaming_enabled,
  is_roaming,
  battery_level,
  is_supervised,
  exchange_active_sync_device_id,
  is_personal,
  is_byo_profile_current,
  tv_password,
  locales,
  languages,
  device_id,
  device_locator_service_enabled,
  do_not_disturb_in_effect,
  activation_lock_enabled,
  cloud_backup_enabled,
  FROM_UNIXTIME((FLOOR(last_cloud_backup_date_epoch/1000)), @eur),
  mdm_profile_removable,
  system_integrity_state,
  ble_capable,
  location_services_enabled,
  device_name_type,
  managed_device_name,
  itunes_store_account_is_active,
  itunes_store_account_hash,
  FROM_UNIXTIME((FLOOR(device_certificate_expiration/1000)), @eur),
  lost_mode,
  FROM_UNIXTIME((FLOOR(lost_mode_enabled_date_epoch/1000)), @eur),
  is_device_multiuser,
  diagnostic_submission_enabled,
  app_analytics_enabled,
  device_type,
  is_network_tethered
INTO OUTFILE "/tmp/mobile_devices_denormalized.csv"
FIELDS TERMINATED BY ',' OPTIONALLY ENCLOSED BY '"'
LINES TERMINATED BY '\n'
FROM
  mobile_devices_denormalized;


-- =============================================================================
-- Currently installed apps in mobile_device_installed_applications
-- =============================================================================
-- Only send list as of last recon. 
-- omit the icon id column - not useful. 
-- Swap in deviceid for report id. 

SELECT 
 "device_id", 
 "application_name",
 "application_version",
 "application_short_version",
 "application_price",
 "purchased_by_apple_id",
 "purchased_by_username", 
 "purchase_date", 
 "purchase_price", 
 "bundle_size", 
 "dynamic_size", 
 "identifier", 
 "management_status", 
 "management_flags", 
 "is_validated", 
 "external_version_identifier", 
 "has_update_available", 
 "is_app_store_vendable", 
 "is_device_based_vpp", 
 "is_beta_app", 
 "is_adhoc_code_signed"
UNION ALL
SELECT 
 mdd.mobile_device_id, 
 mdia.application_name,
 mdia.application_version,
 mdia.application_short_version,
 mdia.application_price,
 mdia.purchased_by_apple_id,
 mdia.purchased_by_username, 
 from_unixtime (floor(mdia.purchase_date_epoch/1000), @eur), 
 mdia.purchase_price, 
 mdia.bundle_size, 
 mdia.dynamic_size, 
 mdia.identifier, 
 mdia.management_status, 
 mdia.management_flags, 
 mdia.is_validated, 
 mdia.external_version_identifier, 
 mdia.has_update_available, 
 mdia.is_app_store_vendable, 
 mdia.is_device_based_vpp, 
 mdia.is_beta_app, 
 mdia.is_adhoc_code_signed
INTO OUTFILE "/tmp/mobile_device_installed_applications-AsOfLastReport.csv"
FIELDS TERMINATED BY ',' OPTIONALLY ENCLOSED BY '"'
LINES TERMINATED BY '\n'
FROM 
  mobile_devices_denormalized AS mdd 
INNER JOIN 
  mobile_device_installed_applications AS mdia
ON 
  mdd.last_report_id = mdia.report_id;






================================================================================
================================================================================
================================================================================


/*
=============================================================================
mobile_device_installed_applications as of last Report
Omit the icon id column - not useful. 
Replace report ID with deviceid.
=============================================================================
*/
SELECT 
 "device_id",
 "application_name",
 "application_version",
 "application_short_version",
 "application_price",
 "purchased_by_apple_id",
 "purchased_by_username",
 "purchase_date",
 "purchase_price",
 "bundle_size",
 "dynamic_size",
 "identifier",
 "management_status",
 "management_flags",
 "is_validated",
 "external_version_identifier",
 "has_update_available",
 "is_app_store_vendable",
 "is_device_based_vpp",
 "is_beta_app",
 "is_adhoc_code_signed"
UNION ALL
SELECT 
 mdd.mobile_device_id,
 mdia.application_name,
 mdia.application_version,
 mdia.application_short_version,
 mdia.application_price,
 mdia.purchased_by_apple_id,
 mdia.purchased_by_username,
 from_unixtime (mdia.purchase_date,'EUR'),
 mdia.purchase_price,
 mdia.bundle_size,
 mdia.dynamic_size,
 mdia.identifier,
 mdia.management_status,
 mdia.management_flags,
 mdia.is_validated,
 mdia.external_version_identifier,
 mdia.has_update_available,
 mdia.is_app_store_vendable,
 mdia.is_device_based_vpp,
 mdia.is_beta_app,
 mdia.is_adhoc_code_signed
INTO OUTFILE "/tmp/mobile_device_currently_installed_applications.csv"
FIELDS TERMINATED BY ',' OPTIONALLY ENCLOSED BY '"'
LINES TERMINATED BY '\n'
FROM 
  mobile_devices_denormalized AS mdd 
INNER JOIN 
  mobile_device_installed_applications AS mdia
ON 
  mdd.last_report_id = mdia.report_id;




/*
-- Jamf saves a complete copy of inventory data for every recon and  
--  that makes tables very large to transfer and hard to report on. 
-- As a solution, we typically look at the last report ID in denormalized 
--  and then just export the child tables (apps, profiles, certs, etc.) 
--  for that key.  

-- If customer wants to purge their reporting db with every update
--  but still wants the ability to report on trends/history, we have a 
--  problem. The data tables can get huge because Jamf Pro saves a complete
--  copy of every inventory. There's no change log in the db, which is what 
--  most people will want. 

-- This exports mobile device app history, but omits any duplicates 
--  so you only export when a app was added or updated. 
-- To do… Create record to indicate when app is uninstalled? I.e mark
--  records with event type? (E.g. New, Version Change, Deleted?)
CREATE TEMPORARY TABLE mdia_ordered AS
(
SELECT 
  r.mobile_device_id,
  mdia.report_id,
  mdia.identifier,
  mdia.application_name,
  mdia.application_version,
  CONCAT(`identifier`, '|', `application_version`) AS `app_ident_version`
FROM
  mobile_device_installed_applications AS mdia 
INNER JOIN
  reports AS r
  ON   r.report_id = mdia.report_id 
ORDER BY
  r.mobile_device_id,
  mdia.identifier,
  mdia.application_version,
  mdia.report_id
);
SELECT * FROM
(
  SELECT 
    *,
    IF(@prevStatus = app_ident_version, 'N',
      IF(@prevStatus := app_ident_version, 'Y', 'Y')
    ) AS changedYN
  FROM
    mdia_ordered
) AS mdia_ordered_with_changeYN
WHERE
  changedYN='Y'
;
*/


/*
-- This example shows how to include an extension attribute value
SELECT
  cd.computer_name AS "ComputerName",
  IF(cd.is_managed=1, "Yes", "No") as "Managed",	-- Example of switching binary numeric boolean to string
  FROM_UNIXTIME(cd.last_report_date_epoch) as "LastReportDate",	-- example of switching unixtime to string
  eav01.value_on_client AS 'GateKeeper Status' -- Example of including an extension attribute
FROM
  computers_denormalized AS cd
LEFT JOIN 
  extension_attribute_values AS eav1 
ON 
  cd.last_report_id=eav1.report_id AND eav1.extension_attribute_id=1
;
*/