Sorry for the delete/resubmit, I had a mistake in my SQL and my edit time had expired! The following is an instructional blog-post on how you can pivot data from the

registry

or

plist

table using the

MAX

&

CASE

functions: https://blog.kolide.com/manipulating-plist-and-registry-output-in-osquery-ad98b067c574 An example of that type of query below:

SELECT  SPLIT(subkey, '/', 0) AS device_id,
MAX(CASE WHEN subkey LIKE '%Device Class'
         THEN value END) AS device_class,
MAX(CASE WHEN subkey LIKE '%Product Type'
         THEN value END) AS product_type,
MAX(CASE WHEN subkey LIKE '%Serial Number'
         THEN value END) AS serial_number,
MAX(CASE WHEN subkey LIKE '%Connected'
         THEN datetime(CAST(value as integer), 'unixepoch') 
                    END) AS connected
FROM plist WHERE path LIKE '/Users/%/Library/Preferences/com.apple.iPod.plist'
AND device_id != ''
GROUP BY device_id;
+------------------+--------------+--------------+---------------+---------------------+
| device_id        | device_class | product_type | serial_number | connected           |
+------------------+--------------+--------------+---------------+---------------------+
| 8BC5A72B8A81A73E | iPhone       | iPhone7,2    | F18N993KMN12  | 2018-09-18 12:29:39 |
| 90BF179953411CA6 | iPad         | iPad7,3      | DMPVLSM4821L  | 2019-10-08 13:49:15 |
| B799411B2DA7C67E | iPhone       | iPhone8,4    | F17TU12JDR3E  | 2019-11-22 16:22:48 |
+------------------+--------------+--------------+---------------+---------------------+