def check_host(hostname, results):
    fqdn = spicerack.netbox_server(hostname.split('.')).mgmt_fqdn
    redfish = spicerack.redfish(fqdn, 'root')
    try:
        storage = redfish.request('get', '/redfish/v1/Systems/System.Embedded.1/Storage').json()
        key = 'Drives'
    except Exception:
        storage = redfish.request('get', '/redfish/v1/Systems/System.Embedded.1/Storage/Controllers').json()
        key = 'Devices'
    for controller in storage['Members']:
        drives = redfish.request('get', controller['@odata.id']).json()[key]
        if key == 'Devices':
            if hostname not in results:
                results[hostname] = drives
            else:
                results[hostname].extend(drives)
            for drive in drives:
                logging.warning('Host %s has unknown media type for drive %s %s', hostname, drive['Manufacturer'], drive['Model'])
        else:
            for drive in drives:
                data = redfish.request('get', drive['@odata.id']).json()
                results[hostname] = data
                if data['MediaType'] != 'SSD':
                    logging.error('Host %s has MediaType %s for drive %s', hostname, data['MediaType'], drive['@odata.id'])

hosts = spicerack.remote().query('YOUR_QUERY_HERE').hosts
results = {}
for host in hosts:
    logging.info(host)
    check_host(host, results)
    
# here the 'results' variable will have all the data if you need to inspect any host more in depth without re-doing the calls

WARNING:root:Host db1115.eqiad.wmnet has unknown media type for drive SEAGATE ST4000NM0265 2DC
ERROR:root:Host es1025.eqiad.wmnet has MediaType HDD for drive /redfish/v1/Systems/System.Embedded.1/Storage/Drives/Disk.Bay.0:Enclosure.Internal.0-1:RAID.Slot.4-1

root@db1169.eqiad.wmnet[information_schema]> select * from INNODB_TABLESPACES_SCRUBBING limit 3;
+-------+------------------------+------------+----------------------+-----------------------+------------------------------+---------------------------+-------------------------------+--------+
| SPACE | NAME                   | COMPRESSED | LAST_SCRUB_COMPLETED | CURRENT_SCRUB_STARTED | CURRENT_SCRUB_ACTIVE_THREADS | CURRENT_SCRUB_PAGE_NUMBER | CURRENT_SCRUB_MAX_PAGE_NUMBER | ON_SSD |
+-------+------------------------+------------+----------------------+-----------------------+------------------------------+---------------------------+-------------------------------+--------+
|     0 | innodb_system          |          0 | NULL                 | NULL                  |                         NULL |                         0 |                             0 |      0 |
|   660 | enwiki/__wmf_checksums |          0 | NULL                 | NULL                  |                         NULL |                         0 |                             0 |      0 |
|   661 | enwiki/abuse_filter    |          0 | NULL                 | NULL                  |                         NULL |                         0 |                             0 |      0 |
+-------+------------------------+------------+----------------------+-----------------------+------------------------------+---------------------------+-------------------------------+--------+
3 rows in set (0.001 sec)

On Linux, the detection will be based on the files
/sys/block/*/queue/rotational and /sys/block/*/dev.
Especially for USB storage, it is possible that
/sys/block/*/queue/rotational will wrongly report 1 instead of 0.

fil_node_t::on_ssd: Whether the InnoDB data file resides on
solid-state storage.

fil_system_t::ssd: Collection of Linux block devices that reside on
non-rotational storage.

fil_system_t::create(): Detect ssd on Linux based on the contents
of /sys/block/*/queue/rotational and /sys/block/*/dev.

fil_system_t::is_ssd(dev_t): Determine if a Linux block device is
non-rotational. Partitions will be identified with the containing
block device by assuming that the least significant 4 bits of the
minor number identify a partition, and that the "partition number"
of the entire device is 0.

root@db1169:~# cat /sys/block/*/queue/rotational
1
1
root@db1169:~# cat /sys/block/*/dev
254:0
8:0

root@db1169.eqiad.wmnet[information_schema]> show global variables like '%flush_neigh%';
+------------------------+-------+
| Variable_name          | Value |
+------------------------+-------+
| innodb_flush_neighbors | 1     |
+------------------------+-------+
1 row in set (0.001 sec)