krypted.com

Tiny Deathstars of Foulness

If you fire up a connection to Postgres on a Profile Manager server, you can see a list of all the databases and tables on the server, respectively: sudo -u _devicemgr psql -h /Library/Server/ProfileManager/Config/var/PostgreSQL devicemgr_v2m0 devicemgr_v2m0=# \list devicemgr_v2m0=# \dt The list of tables is as follows: Name | Owner | Encoding | Collate | Ctype | Access privileges ----------------+------------+----------+---------+-------+--------------------------- devicemgr_v2m0 | _devicemgr | UTF8 | C | C | postgres | _devicemgr | UTF8 | C | C | template0 | _devicemgr | UTF8 | C | C | =c/_devicemgr + | | | | | _devicemgr=CTc/_devicemgr template1 | _devicemgr | UTF8 | C | C | =c/_devicemgr + | | | | | _devicemgr=CTc/_devicemgr The list of relations is much more lengthy, but if you parse it then you can then use a string of commands to dump the contents of each table into a stand-alone CSV file: sudo -u _devicemgr psql -h /Library/Server/ProfileManager/Config/var/PostgreSQL devicemgr_v2m0 -c "Copy (Select * From abstract_asm_library_items) To STDOUT With CSV HEADER DELIMITER ',';" > ~/pmexport/abstract_asm_library_items.csv sudo -u _devicemgr psql -h /Library/Server/ProfileManager/Config/var/PostgreSQL devicemgr_v2m0 -c "Copy (Select * From abstract_asm_users) To STDOUT With CSV HEADER DELIMITER ',';" > ~/pmexport/abstract_asm_users.csv sudo -u _devicemgr psql -h /Library/Server/ProfileManager/Config/var/PostgreSQL devicemgr_v2m0 -c "Copy (Select * From active_locales) To STDOUT With CSV HEADER DELIMITER ',';" > ~/pmexport/active_locales.csv sudo -u _devicemgr psql -h /Library/Server/ProfileManager/Config/var/PostgreSQL devicemgr_v2m0 -c "Copy (Select * From app_configurations) To STDOUT With CSV HEADER DELIMITER ',';" > ~/pmexport/app_configurations.csv sudo -u _devicemgr psql -h /Library/Server/ProfileManager/Config/var/PostgreSQL devicemgr_v2m0 -c "Copy (Select * From asset_metadata) To STDOUT With CSV HEADER DELIMITER ',';" > ~/pmexport/asset_metadata.csv sudo -u _devicemgr psql -h /Library/Server/ProfileManager/Config/var/PostgreSQL devicemgr_v2m0 -c "Copy (Select * From assets) To STDOUT With CSV HEADER DELIMITER ',';" > ~/pmexport/assets.csv sudo -u _devicemgr psql -h /Library/Server/ProfileManager/Config/var/PostgreSQL devicemgr_v2m0 -c "Copy (Select * From assets_localized_data) To STDOUT With CSV HEADER DELIMITER ',';" > ~/pmexport/assets_localized_data sudo -u _devicemgr psql -h /Library/Server/ProfileManager/Config/var/PostgreSQL devicemgr_v2m0 -c "Copy (Select * From auto_join_profile_usage) To STDOUT With CSV HEADER DELIMITER ',';" > ~/pmexport/auto_join_profile_usage.csv sudo -u _devicemgr psql -h /Library/Server/ProfileManager/Config/var/PostgreSQL devicemgr_v2m0 -c "Copy (Select * From auto_join_profiles) To STDOUT With CSV HEADER DELIMITER ',';" > ~/pmexport/auto_join_profiles.csv sudo -u _devicemgr psql -h /Library/Server/ProfileManager/Config/var/PostgreSQL devicemgr_v2m0 -c "Copy (Select * From auto_join_profiles_device_groups) To STDOUT With CSV HEADER DELIMITER ',';" > ~/pmexport/auto_join_profiles_device_groups.csv sudo -u _devicemgr psql -h /Library/Server/ProfileManager/Config/var/PostgreSQL devicemgr_v2m0 -c "Copy (Select * From certificates) To STDOUT With CSV HEADER DELIMITER ',';" > ~/pmexport/certificates.csv sudo -u _devicemgr psql -h /Library/Server/ProfileManager/Config/var/PostgreSQL devicemgr_v2m0 -c "Copy (Select * From completed_tasks) To STDOUT With CSV HEADER DELIMITER ',';" > ~/pmexport/completed_tasks.csv sudo -u _devicemgr psql -h /Library/Server/ProfileManager/Config/var/PostgreSQL devicemgr_v2m0 -c "Copy (Select * From data_files) To STDOUT With CSV HEADER DELIMITER ',';" > ~/pmexport/data_files.csv sudo -u _devicemgr psql -h /Library/Server/ProfileManager/Config/var/PostgreSQL devicemgr_v2m0 -c "Copy (Select * From db_notifications) To STDOUT With CSV HEADER DELIMITER ',';" > ~/pmexport/db_notifications.csv sudo -u _devicemgr psql -h /Library/Server/ProfileManager/Config/var/PostgreSQL devicemgr_v2m0 -c "Copy (Select * From deleted_media) To STDOUT With CSV HEADER DELIMITER ',';" > ~/pmexport/deleted_media.csv sudo -u _devicemgr psql -h /Library/Server/ProfileManager/Config/var/PostgreSQL devicemgr_v2m0 -c "Copy (Select * From deleted_objects) To STDOUT With CSV HEADER DELIMITER ',';" > ~/pmexport/deleted_objects.csv sudo -u _devicemgr psql -h /Library/Server/ProfileManager/Config/var/PostgreSQL devicemgr_v2m0 -c "Copy (Select * From device_enrollment_settings) To STDOUT With CSV HEADER DELIMITER ',';" > ~/pmexport/device_enrollment_settings.csv sudo -u _devicemgr psql -h /Library/Server/ProfileManager/Config/var/PostgreSQL devicemgr_v2m0 -c "Copy (Select * From device_group_memberships) To STDOUT With CSV HEADER DELIMITER ',';" > ~/pmexport/device_group_memberships sudo -u _devicemgr psql -h /Library/Server/ProfileManager/Config/var/PostgreSQL devicemgr_v2m0 -c "Copy (Select * From device_groups) To STDOUT With CSV HEADER DELIMITER ',';" > ~/pmexport/device_groups.csv sudo -u _devicemgr psql -h /Library/Server/ProfileManager/Config/var/PostgreSQL devicemgr_v2m0 -c "Copy (Select * From device_groups_devices) To STDOUT With CSV HEADER DELIMITER ',';" > ~/pmexport/device_groups_devices.csv sudo -u _devicemgr psql -h /Library/Server/ProfileManager/Config/var/PostgreSQL devicemgr_v2m0 -c "Copy (Select * From devices) To STDOUT With CSV HEADER DELIMITER ',';" > ~/pmexport/devices.csv sudo -u _devicemgr psql -h /Library/Server/ProfileManager/Config/var/PostgreSQL devicemgr_v2m0 -c "Copy (Select * From dm_schema_information) To STDOUT With CSV HEADER DELIMITER ',';" > ~/pmexport/dm_schema_information.csv sudo -u _devicemgr psql -h /Library/Server/ProfileManager/Config/var/PostgreSQL devicemgr_v2m0 -c "Copy (Select * From dynamic_attributes_defaults) To STDOUT With CSV HEADER DELIMITER ',';" > ~/pmexport/dynamic_attributes_defaults.csv sudo -u _devicemgr psql -h /Library/Server/ProfileManager/Config/var/PostgreSQL devicemgr_v2m0 -c "Copy (Select * From ebooks) To STDOUT With CSV HEADER DELIMITER ',';" > ~/pmexport/ebooks.csv sudo -u _devicemgr psql -h /Library/Server/ProfileManager/Config/var/PostgreSQL devicemgr_v2m0 -c "Copy (Select * From edu_classes) To STDOUT With CSV HEADER DELIMITER ',';" > ~/pmexport/edu_classes.csv sudo -u _devicemgr psql -h /Library/Server/ProfileManager/Config/var/PostgreSQL devicemgr_v2m0 -c "Copy (Select * From edu_classes_library_items) To STDOUT With CSV HEADER DELIMITER ',';" > ~/pmexport/edu_classes_library_items sudo -u _devicemgr psql -h /Library/Server/ProfileManager/Config/var/PostgreSQL devicemgr_v2m0 -c "Copy (Select * From edu_devices_users) To STDOUT With CSV HEADER DELIMITER ',';" > ~/pmexport/edu_devices_users.csv sudo -u _devicemgr psql -h /Library/Server/ProfileManager/Config/var/PostgreSQL devicemgr_v2m0 -c "Copy (Select * From enterprise_apps) To STDOUT With CSV HEADER DELIMITER ',';" > ~/pmexport/enterprise_apps.csv sudo -u _devicemgr psql -h /Library/Server/ProfileManager/Config/var/PostgreSQL devicemgr_v2m0 -c "Copy (Select * From installed_applications) To STDOUT With CSV HEADER DELIMITER ',';" > ~/pmexport/installed_applications.csv sudo -u _devicemgr psql -h /Library/Server/ProfileManager/Config/var/PostgreSQL devicemgr_v2m0 -c "Copy (Select * From installed_books) To STDOUT With CSV HEADER DELIMITER ',';" > ~/pmexport/installed_books.csv sudo -u _devicemgr psql -h /Library/Server/ProfileManager/Config/var/PostgreSQL devicemgr_v2m0 -c "Copy (Select * From installed_ios_applications) To STDOUT With CSV HEADER DELIMITER ',';" > ~/pmexport/installed_ios_applications.csv sudo -u _devicemgr psql -h /Library/Server/ProfileManager/Config/var/PostgreSQL devicemgr_v2m0 -c "Copy (Select * From installed_media) To STDOUT With CSV HEADER DELIMITER ',';" > ~/pmexport/installed_media.csv sudo -u _devicemgr psql -h /Library/Server/ProfileManager/Config/var/PostgreSQL devicemgr_v2m0 -c "Copy (Select * From installed_osx_applications) To STDOUT With CSV HEADER DELIMITER ',';" > ~/pmexport/installed_osx_applications.csv sudo -u _devicemgr psql -h /Library/Server/ProfileManager/Config/var/PostgreSQL devicemgr_v2m0 -c "Copy (Select * From installed_profiles) To STDOUT With CSV HEADER DELIMITER ',';" > ~/pmexport/installed_profiles.csv sudo -u _devicemgr psql -h /Library/Server/ProfileManager/Config/var/PostgreSQL devicemgr_v2m0 -c "Copy (Select * From internal_tasks) To STDOUT With CSV HEADER DELIMITER ',';" > ~/pmexport/internal_tasks.csv sudo -u _devicemgr psql -h /Library/Server/ProfileManager/Config/var/PostgreSQL devicemgr_v2m0 -c "Copy (Select * From knob_sets) To STDOUT With CSV HEADER DELIMITER ',';" > ~/pmexport/knob_sets.csv sudo -u _devicemgr psql -h /Library/Server/ProfileManager/Config/var/PostgreSQL devicemgr_v2m0 -c "Copy (Select * From knob_sets_assets) To STDOUT With CSV HEADER DELIMITER ',';" > ~/pmexport/knob_sets_assets.csv sudo -u _devicemgr psql -h /Library/Server/ProfileManager/Config/var/PostgreSQL devicemgr_v2m0 -c "Copy (Select * From knob_sets_devices) To STDOUT With CSV HEADER DELIMITER ',';" > ~/pmexport/knob_sets_devices.csv sudo -u _devicemgr psql -h /Library/Server/ProfileManager/Config/var/PostgreSQL devicemgr_v2m0 -c "Copy (Select * From knob_sets_printers) To STDOUT With CSV HEADER DELIMITER ',';" > ~/pmexport/knob_sets_printers.csv sudo -u _devicemgr psql -h /Library/Server/ProfileManager/Config/var/PostgreSQL devicemgr_v2m0 -c "Copy (Select * From knob_sets_system_applications) To STDOUT With CSV HEADER DELIMITER ',';" > ~/pmexport/knob_sets_system_applications.csv sudo -u _devicemgr psql -h /Library/Server/ProfileManager/Config/var/PostgreSQL devicemgr_v2m0 -c "Copy (Select * From knob_sets_widgets) To STDOUT With CSV HEADER DELIMITER ',';" > ~/pmexport/knob_sets_widgets.csv sudo -u _devicemgr psql -h /Library/Server/ProfileManager/Config/var/PostgreSQL devicemgr_v2m0 -c "Copy (Select * From lab_sessions) To STDOUT With CSV HEADER DELIMITER ',';" > ~/pmexport/lab_sessions.csv sudo -u _devicemgr psql -h /Library/Server/ProfileManager/Config/var/PostgreSQL devicemgr_v2m0 -c "Copy (Select * From library_item_metadata) To STDOUT With CSV HEADER DELIMITER ',';" > ~/pmexport/.library_item_metadata.csv sudo -u _devicemgr psql -h /Library/Server/ProfileManager/Config/var/PostgreSQL devicemgr_v2m0 -c "Copy (Select * From library_item_settings) To STDOUT With CSV HEADER DELIMITER ',';" > ~/pmexport/library_item_settings.csv sudo -u _devicemgr psql -h /Library/Server/ProfileManager/Config/var/PostgreSQL devicemgr_v2m0 -c "Copy (Select * From library_item_tasks) To STDOUT With CSV HEADER DELIMITER ',';" > ~/pmexport/library_item_tasks.csv sudo -u _devicemgr psql -h /Library/Server/ProfileManager/Config/var/PostgreSQL devicemgr_v2m0 -c "Copy (Select * From library_items) To STDOUT With CSV HEADER DELIMITER ',';" > ~/pmexport/library_items.csv sudo -u _devicemgr psql -h /Library/Server/ProfileManager/Config/var/PostgreSQL devicemgr_v2m0 -c "Copy (Select * From library_items_assets) To STDOUT With CSV HEADER DELIMITER ',';" > ~/pmexport/library_items_assets.csv sudo -u _devicemgr psql -h /Library/Server/ProfileManager/Config/var/PostgreSQL devicemgr_v2m0 -c "Copy (Select * From mdm_targets) To STDOUT With CSV HEADER DELIMITER ',';" > ~/pmexport/mdm_targets.csv sudo -u _devicemgr psql -h /Library/Server/ProfileManager/Config/var/PostgreSQL devicemgr_v2m0 -c "Copy (Select * From mdm_tasks) To STDOUT With CSV HEADER DELIMITER ',';" > ~/pmexport/mdm_tasks.csv sudo -u _devicemgr psql -h /Library/Server/ProfileManager/Config/var/PostgreSQL devicemgr_v2m0 -c "Copy (Select * From media) To STDOUT With CSV HEADER DELIMITER ',';" > ~/pmexport/media.csv sudo -u _devicemgr psql -h /Library/Server/ProfileManager/Config/var/PostgreSQL devicemgr_v2m0 -c "Copy (Select * From network_lab_sessions) To STDOUT With CSV HEADER DELIMITER ',';" > ~/pmexport/network_lab_sessions.csv sudo -u _devicemgr psql -h /Library/Server/ProfileManager/Config/var/PostgreSQL devicemgr_v2m0 -c "Copy (Select * From od_library_items) To STDOUT With CSV HEADER DELIMITER ',';" > ~/pmexport/od_library_items.csv sudo -u _devicemgr psql -h /Library/Server/ProfileManager/Config/var/PostgreSQL devicemgr_v2m0 -c "Copy (Select * From od_nodes) To STDOUT With CSV HEADER DELIMITER ',';" > ~/pmexport/od_nodes.csv sudo -u _devicemgr psql -h /Library/Server/ProfileManager/Config/var/PostgreSQL devicemgr_v2m0 -c "Copy (Select * From od_searches) To STDOUT With CSV HEADER DELIMITER ',';" > ~/pmexport/od_searches.csv sudo -u _devicemgr psql -h /Library/Server/ProfileManager/Config/var/PostgreSQL devicemgr_v2m0 -c "Copy (Select * From os_updates) To STDOUT With CSV HEADER DELIMITER ',';" > ~/pmexport/os_updates.csv sudo -u _devicemgr psql -h /Library/Server/ProfileManager/Config/var/PostgreSQL devicemgr_v2m0 -c "Copy (Select * From os_updates_devices) To STDOUT With CSV HEADER DELIMITER ',';" > ~/pmexport/os_updates_devices.csv sudo -u _devicemgr psql -h /Library/Server/ProfileManager/Config/var/PostgreSQL devicemgr_v2m0 -c "Copy (Select * From owner_lab_sessions) To STDOUT With CSV HEADER DELIMITER ',';" > ~/pmexport/owner_lab_sessions.csv sudo -u _devicemgr psql -h /Library/Server/ProfileManager/Config/var/PostgreSQL devicemgr_v2m0 -c "Copy (Select * From preference_panes) To STDOUT With CSV HEADER DELIMITER ',';" > ~/pmexport/preference_panes.csv sudo -u _devicemgr psql -h /Library/Server/ProfileManager/Config/var/PostgreSQL devicemgr_v2m0 -c "Copy (Select * From printers) To STDOUT With CSV HEADER DELIMITER ',';" > ~/pmexport/printers.csv sudo -u _devicemgr psql -h /Library/Server/ProfileManager/Config/var/PostgreSQL devicemgr_v2m0 -c "Copy (Select * From profiles) To STDOUT With CSV HEADER DELIMITER ',';" > ~/pmexport/profiles.csv sudo -u _devicemgr psql -h /Library/Server/ProfileManager/Config/var/PostgreSQL devicemgr_v2m0 -c "Copy (Select * From sessions) To STDOUT With CSV HEADER DELIMITER ',';" > ~/pmexport/sessions.csv sudo -u _devicemgr psql -h /Library/Server/ProfileManager/Config/var/PostgreSQL devicemgr_v2m0 -c "Copy (Select * From settings) To STDOUT With CSV HEADER DELIMITER ',';" > ~/pmexport/settings.csv sudo -u _devicemgr psql -h /Library/Server/ProfileManager/Config/var/PostgreSQL devicemgr_v2m0 -c "Copy (Select * From system_applications) To STDOUT With CSV HEADER DELIMITER ',';" > ~/pmexport/system_applications.csv sudo -u _devicemgr psql -h /Library/Server/ProfileManager/Config/var/PostgreSQL devicemgr_v2m0 -c "Copy (Select * From target_tombstones) To STDOUT With CSV HEADER DELIMITER ',';" > ~/pmexport/target_tombstones.csv sudo -u _devicemgr psql -h /Library/Server/ProfileManager/Config/var/PostgreSQL devicemgr_v2m0 -c "Copy (Select * From user_group_memberships) To STDOUT With CSV HEADER DELIMITER ',';" > ~/pmexport/user_group_memberships.csv sudo -u _devicemgr psql -h /Library/Server/ProfileManager/Config/var/PostgreSQL devicemgr_v2m0 -c "Copy (Select * From user_groups) To STDOUT With CSV HEADER DELIMITER ',';" > ~/pmexport/user_groups.csv sudo -u _devicemgr psql -h /Library/Server/ProfileManager/Config/var/PostgreSQL devicemgr_v2m0 -c "Copy (Select * From user_groups_users) To STDOUT With CSV HEADER DELIMITER ',';" > ~/pmexport/user_groups_users.csv sudo -u _devicemgr psql -h /Library/Server/ProfileManager/Config/var/PostgreSQL devicemgr_v2m0 -c "Copy (Select * From user_tasks) To STDOUT With CSV HEADER DELIMITER ',';" > ~/pmexport/user_tasks.csv sudo -u _devicemgr psql -h /Library/Server/ProfileManager/Config/var/PostgreSQL devicemgr_v2m0 -c "Copy (Select * From users) To STDOUT With CSV HEADER DELIMITER ',';" > ~/pmexport/users.csv sudo -u _devicemgr psql -h /Library/Server/ProfileManager/Config/var/PostgreSQL devicemgr_v2m0 -c "Copy (Select * From vpp_assigned_licenses) To STDOUT With CSV HEADER DELIMITER ',';" > ~/pmexport/vpp_assigned_licenses.csv sudo -u _devicemgr psql -h /Library/Server/ProfileManager/Config/var/PostgreSQL devicemgr_v2m0 -c "Copy (Select * From vpp_products) To STDOUT With CSV HEADER DELIMITER ',';" > ~/pmexport/vpp_products.csv sudo -u _devicemgr psql -h /Library/Server/ProfileManager/Config/var/PostgreSQL devicemgr_v2m0 -c "Copy (Select * From widgets) To STDOUT With CSV HEADER DELIMITER ',';" > ~/pmexport/widgets.csv sudo -u _devicemgr psql -h /Library/Server/ProfileManager/Config/var/PostgreSQL devicemgr_v2m0 -c "Copy (Select * From work_tasks) To STDOUT With CSV HEADER DELIMITER ',';" > ~/pmexport/work_tasks.csv sudo -u _devicemgr psql -h /Library/Server/ProfileManager/Config/var/PostgreSQL devicemgr_v2m0 -c "Copy (Select * From xsan_networks) To STDOUT With CSV HEADER DELIMITER ',';" > ~/pmexport/xsan_networks.csv Now, if you were to just run a select * from devices; from within devicemgr_v2m0, you would get the following:
id | admin_temp_id | created_at | updated_at | updated_at_xid | library_item_type | order_name | mdm_target_type | user_id | last_checkin_time | last_push_time | first_push_time | last_update_info_time | last_auto_sync_profiles | last_auto_sync_media | processing_tasks | hp_singleton_tasks | lp_singleton_tasks | nn_singleton_tasks | singleton_task_type | singleton_uuid | supported_device_type | token | push_magic | push_avg_response_time | push_response_times | vpp_last_invite_requested | vpp_last_invite_delivered | pending_checkin_token | checkin_token_valid_at | active_checkin_token | DeviceName | ProductName | OSVersion | SerialNumber | udid | identifier | is_dep_device | is_multi_user | pending_user_id | supported_asset_types | mdm_acl | IMEI | MEID | IsSupervised | BluetoothMAC | EthernetMAC | WiFiMAC | DeviceID | airplay_password | color | assigned_dep_profile_uuid | dep_profile_uuid | dep_profile | activation_lock_bypass_code | mdm_activation_lock_bypass_code | last_mdm_refresh_ttl_days
These can then read into an array and dealt with as needed. For example, you can link lists of users and groups or use this as a separate form of backup. Another way to get this data, that would be a bit more future-proofed, would be to read all items in the schema for public on the desired database, and then build an array of name items and a loop. But this is a good start.

February 21st, 2017

Posted In: Mac OS X Server

Tags: , , , , , , ,

Create a backup copy of a table called Customers into a table called Customers2 on a running database called Backup: SELECT * INTO Customers2 IN 'Backup.mdb' FROM Customers; You can also specify multiple tables to pull data from when bringing that data into a new table, effectively merging data into a backup database: SELECT Customers.Site, IPs.IP INTO CustomerIPsbackup FROM Customers LEFT JOIN IPs ON Customers.Site=IPs.IP; Since this is more like replication than backup, MySQL also has a mysqldump command, used to dump a sql database to a file or screen, or whatever. Here, we’ll export all of our databases in a running MySQL instance into a file called dumpfile.sql: mysqldump --all-databases > dumpfile.sql

February 17th, 2016

Posted In: SQL

Tags: , , , ,