omdb command to list all omicron services associated with a particular physical disk

[askfongjojo]

In coming up with the technician procedures for disk expungement, I have to craft a direct database query to find out which services are impacted by the operation. Currently blueprint shows you most of the information but lacks the link between the physical disk and the pool id. For example, in the snippet below, there is no way to find out which serial corresponds to say oxp_0a4b67b3-7ffc-4e42-8387-b7ec118e421e:

  sled: 1f16b121-b3a5-4e04-ae84-ffe3eec1cdd2 (active)

    physical disks at generation 2:
    -----------------------------------
    vendor   model             serial  
    -----------------------------------
    1b96     WUS4C6432DSP3X3   A079DDCD
    1b96     WUS4C6432DSP3X3   A079DF2B
    1b96     WUS4C6432DSP3X3   A079DF42
    1b96     WUS4C6432DSP3X3   A079DF5A
    1b96     WUS4C6432DSP3X3   A079DF5C
    1b96     WUS4C6432DSP3X3   A079DFFB
    1b96     WUS4C6432DSP3X3   A079E3AC
    1b96     WUS4C6432DSP3X3   A079E3D6
    1b96     WUS4C6432DSP3X3   A079E435
    1b96     WUS4C6432DSP3X3   A079E4A7

    datasets at generation 1:
    -------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
    dataset name                                                                                                   dataset id                             disposition   quota     reservation   compression
    -------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
    oxp_0a4b67b3-7ffc-4e42-8387-b7ec118e421e/crypt/cockroachdb                                                     61196cd9-ffc8-443d-b66d-48a0511a7d22   in service    none      none          off        
    oxp_42b27489-4f4f-41de-8ac0-3ebb9eab63d9/crypt/cockroachdb                                                     069a43d3-27f6-4f5a-843b-91c1198a4913   in service    none      none          off        
    oxp_057cd60c-fc94-479a-8c59-8185384affa2/crucible                                                              6f13b20e-8827-4f9d-bea5-8e95f4f66dd5   in service    none      none          off        
    oxp_0a4b67b3-7ffc-4e42-8387-b7ec118e421e/crucible                                                              363fb3e4-7adf-4c61-908b-0544cd6a0aa9   in service    none      none          off        
    oxp_2f40f12e-6dd0-46a1-b88e-c67e884bddbe/crucible                                                              53bd8ab7-4e8c-4d42-9d89-559e9772f700   in service    none      none          off        
    oxp_42b27489-4f4f-41de-8ac0-3ebb9eab63d9/crucible                                                              7f15dcb2-330a-42bf-9f6e-5c78bc40cb91   in service    none      none          off        

The database query I'm using at this time is:

select bp_zone.sled_id, serial, bp_disk.id as physical_disk_id, zone_type, filesystem_pool
from bp_omicron_zone bp_zone
join bp_omicron_physical_disk bp_disk on bp_zone.filesystem_pool = bp_disk.pool_id
where bp_disk.blueprint_id = '$BLUEPRINT_ID'
and bp_disk.blueprint_id = bp_zone.blueprint_id
and bp_zone.disposition = 'in_service'
and bp_zone.filesystem_pool = '$ZPOOL_ID';

where blueprint id is the latest blueprint generated.

The query also saved me the step of calling omdb db physical-disks to look up the physical disk id based on a serial number (both disk id and serial are required when using the expunge-disk command). Ideally, it'll be good to return serial, physical disk id, and pool id all side-by-side since all of them get used in different context (e.g. zpool error, nvmeadm commands) to refer to the same disk.