How do i get list of my devices from Profile Manager in to a .csv file?

Question

Level 1

2 points

How do i get list of my devices from Profile Manager in to a .csv file?

Hi

I have Apple Profile Manager set up as my MDM solution and i have created tens of device groups for about 500 devices.

Now i need to get a list of my devices (names, serial numbers and groups) in to a file that i can read and modify with Excel. Like a report of MDM enrolled devices by device groups..

I didn't find any tools in the server itself for such report. I looked around internet and i managed to run a console command that created an .sql dump of the database but this is where i get stuck. I can't seem to find any solution to successfully convert that .sql file in to a .csv or .xhtml or anything readable.

Can anyone help me how to do this?

Mac mini, OS X Yosemite (10.10.5)

Posted on Dec 8, 2015 2:38 AM

Reply

Answer 1

Feb 9, 2017 11:06 AM in response to Strontium90

Hello:

This has been very helpful!

I do have another question, however.

I need to include the User (assigned user) to the output records. What would I use? Is there somewhere I can find a list of the names of the available field names?

Thank you!

Reply

Answer 2

Dec 8, 2015 8:16 AM in response to PlasmaGsus

I've got you covered. You need to connect to the database and dump the records you want. It is easy. Follow these steps:

1: Connect to the database

sudo psql -U _devicemgr -d devicemgr_v2m0 -h /Library/Server/ProfileManager/Config/var/PostgreSQL

This will enter you into the interactive shell.

2: Select the database you want to interact with

\c devicemgr_v2m0;

3: Dump all data about your devices.

select * from devices;

4: If you want names and serial numbers, use:

select "DeviceName","SerialNumber" from devices;

5: When done quit the shell

\q

Plenty of data can be found in there. Be careful. Only use selects.

Reid

Apple Consultants Network

Author "El Capitan Server – Foundation Services" :: Exclusively available in Apple's iBooks Store

Author "El Capitan Server – Control & Collaboration" :: Exclusively available in Apple's iBooks Store

Author of Yosemite Server and Mavericks Server books

Reply

Answer 3

Dec 9, 2015 1:45 AM in response to PlasmaGsus

Select all from table name (devices) and save as a file (as terminal only shows 25 lines at a time…) This makes a "devices.txt" file in the root of your home drive (or the machine your SSH'd in to).

sudo psql -h "/Library/Server/ProfileManager/Config/var/PostgreSQL" -U _devicemgr -d devicemgr_v2m0 -c "select * from public.devices" > devices.txt

Then open the .txt file, do a find and replace. Replace "|" with a blank "tab" character. So go to another text file, press tab on the keyboard and copy the white space and paste that in to the "replace with" area if you get me? You can then select everything in the newly edited "devices.txt" and paste that bad boy in to excel and it will put the correct rows / headers in for you.

Reply