https://github.com/osquery/osquery logo
Powered by
#general
Title
# general
e

ET

07/30/2020, 5:27 PM
@fritz good idea, let me check
šŸ‘ 1
f

fritz

07/30/2020, 5:28 PM
That is the cleanest method I am aware of, anything else is going to rely either on disk_events per @Hugh (Zercurity)ā€™s suggestion, or some lossy attempts at association through non-unique things like 
model_name
e

ET

07/30/2020, 5:30 PM
Sounds like thet
Thanks
f

fritz

07/30/2020, 11:52 PM
One thing you should consider @ET is that a device which interfaces via PCI-Express (eg. an NVME SSD within a Thunderbolt 3 enclosure) will not register itself as a USB device
eg.
Copy code
osquery> SELECT * FROM block_devices WHERE type = 'PCI-Express' AND model = 'Sabrent';
+--------------+------------+--------+---------+------------+------------+--------------------------------------+-------------+----------------------+
| name         | parent     | vendor | model   | size       | block_size | uuid                                 | type        | label                |
+--------------+------------+--------+---------+------------+------------+--------------------------------------+-------------+----------------------+
| /dev/disk5   |            |        | Sabrent | 2000409264 | 512        |                                      | PCI-Express | Sabrent Media        |
| /dev/disk5s1 | /dev/disk5 |        | Sabrent | 409600     | 512        | 53623F23-DEFD-45A4-AC8E-4EA90F1F7774 | PCI-Express | EFI System Partition |
| /dev/disk5s2 | /dev/disk5 |        | Sabrent | 1999737440 | 512        | 06324AB6-396F-4A65-8758-93E466C8D9B2 | PCI-Express | Untitled 2           |
+--------------+------------+--------+---------+------------+------------+--------------------------------------+-------------+----------------------+
e

ET

08/02/2020, 8:14 AM
I tried your idea and I got a duplicate answer:
Copy code
+---------------+-------------+---------+--------------+-----------+------------+------+------+----------------------------+
| name          | parent      | vendor  | model        | size      | block_size | uuid | type | label                      |
+---------------+-------------+---------+--------------+-----------+------------+------+------+----------------------------+
| /dev/disk11   |             | SanDisk | Cruzer Blade | 120225792 | 512        |      | USB  | SanDisk Cruzer Blade Media |
| /dev/disk11s1 | /dev/disk11 | SanDisk | Cruzer Blade | 120223744 | 512        |      | USB  | Untitled 1                 |
+---------------+-------------+---------+--------------+-----------+------------+------+------+----------------------------+
Do you know why?
f

fritz

08/03/2020, 1:30 PM
@ET the query I provided is looking at all block_devices which will grab each partition on a given disk, hence the different labels for parent container and the child partition 
SanDisk Cruzer Blade Media/Untitled 1
, if you wanted to filter down the results you could look for block_devices with no 
parent
e

ET

08/03/2020, 1:31 PM
10x šŸ™‚
f

fritz

08/03/2020, 1:31 PM
Try this for example:
Copy code
SELECT * FROM block_devices WHERE parent = '';
e

ET

08/03/2020, 1:32 PM
That's exactly what I did!
f

fritz

08/03/2020, 1:32 PM
šŸ‘ nicely done šŸ™‚
@ET I haven't done as much removable media exploration as I would like but it appears from my poking that you can also get 
mounted_at
time from the 
fseventd-uuid
file present in the 
/.fseventsd
path of the mount:
Copy code
WITH removable_media AS (
  select 
    m.device,
    m.path, 
    bd.type,
    ROUND((m.blocks * m.blocks_size / (1000 * 1000 * 1000)), 2) volume_total_gb,
    ROUND((1.0 * m.blocks_free * blocks_size / (1000 * 1000 * 1000)), 2) volume_free_gb,
    ROUND((100.0 * m.blocks_free / m.blocks), 2) AS volume_free_percent,
    bd.vendor, 
    bd.model, 
    bd.uuid, 
    bd.label 
  FROM mounts m, block_devices bd 
  WHERE m.device = bd.name 
  AND path like '/Volumes/%' 
  AND bd.type != 'Virtual Interface')
SELECT 
  rm.*, 
  datetime(f.mtime, 'unixepoch') AS mounted_at 
FROM removable_media rm, file f 
WHERE f.path = rm.path || '/.fseventsd/fseventsd-uuid';
Copy code
device = /dev/disk2s2
               path = /Volumes/Samsung-SSD
               type = USB
    volume_total_gb = 999.0
     volume_free_gb = 130.65
volume_free_percent = 13.07
             vendor = Samsung
              model = Portable SSD T5
               uuid = CCDBD7BF-5E12-4911-B2A2-13088037964D
              label = Untitled 2
         mounted_at = 2020-07-17 20:43:26

             device = /dev/disk5s2
               path = /Volumes/Jeyi
               type = PCI-Express
    volume_total_gb = 1023.0
     volume_free_gb = 996.7
volume_free_percent = 97.35
             vendor = 
              model = Sabrent
               uuid = 06324AB6-396F-4A65-8758-93E466C8D9B2
              label = Untitled 2
         mounted_at = 2020-07-30 23:51:04

             device = /dev/disk4s1
               path = /Volumes/ESD-USB
               type = USB
    volume_total_gb = 34.0
     volume_free_gb = 2.05
volume_free_percent = 5.98
             vendor = 
              model = Patriot Memory
               uuid = 
              label = Untitled 1
         mounted_at = 2020-08-03 14:09:10
Run Time: real 0.033 user 0.010018 sys 0.010998
e

ET

08/03/2020, 2:23 PM
"/Volumes/Samsung-SSD" is your external SSD?
f

fritz

08/03/2020, 2:25 PM
One of, yes
e

ET

08/03/2020, 2:41 PM
Nice!
Look really good ! @fritz - Thanks !
f

fritz

08/03/2020, 3:22 PM
NP @ET! Glad I could be a help šŸ™‚
Powered by