def populate_device_smart_sata(cur, sata_smart_attr, device_id, report_id, ts):
for attr in sata_smart_attr.get('table', []):
device_smart_sata = {}
device_smart_sata['device_id'] = device_id
device_smart_sata['report_id'] = report_id
device_smart_sata['ts'] = ts
device_smart_sata['attr_id'] = attr['id']
device_smart_sata['attr_name'] = attr['name']
device_smart_sata['attr_raw'] = attr['raw']['value']
device_smart_sata['attr_raw_str'] = attr['raw']['string']
device_smart_sata['attr_norm'] = attr['value']
device_smart_sata['attr_worst'] = attr['worst']
sql = 'INSERT INTO device.smart_sata (%s) VALUES %s'
dbhelper.run_insert(cur, sql, device_smart_sata)
def populate_device_smart_nvme(cur, smart_attr_nvme, device_id, report_id, ts):
for k, v in smart_attr_nvme.items():
device_smart_nvme = {}
device_smart_nvme['device_id'] = device_id
device_smart_nvme['report_id'] = report_id
device_smart_nvme['ts'] = ts
device_smart_nvme['attr_name'] = k
device_smart_nvme['attr_val'] = parse_value_by_type(v)
if device_smart_nvme['attr_val'] is None:
print(
f"attr_name: {k} of device report id {report_id}, attr_val is of type list.\n"
)
sql = 'INSERT INTO device.smart_nvme (%s) VALUES %s'
dbhelper.run_insert(cur, sql, device_smart_nvme)
def fetch_device_id(cur, device):
cur.execute(
"""SELECT id FROM device.device
WHERE vmu = %s
""",
(device['vmu'],
)) # The ',' in (device['vmu'],) creates a tuple and is mandatory.
device_id = cur.fetchone()
# No such device in device table, inserting:
if not device_id:
sql = """INSERT INTO device.device (%s) VALUES %s
RETURNING id"""
dbhelper.run_insert(cur, sql, device)
device_id = cur.fetchone()
return device_id[0]
def populate_device_smart_nvme_vs(cur, device, report, device_id, report_id,
ts):
data = report.get('nvme_smart_health_information_add_log', {})
# 'Device stats' is found in Intel drives
dev_stats = data.get('Device stats') if data.get('Device stats') else data
dev_stats_parsed = {}
if dev_stats:
for k, v in dev_stats.items():
parse_nvme_vendor(k, v, dev_stats_parsed)
# No nested dictionaries at this point
for k, v in dev_stats_parsed.items():
device_smart_nvme_vs = {}
device_smart_nvme_vs['device_id'] = device_id
device_smart_nvme_vs['report_id'] = report_id
device_smart_nvme_vs['ts'] = ts
device_smart_nvme_vs['attr_name'] = k
device_smart_nvme_vs['attr_val'] = v
sql = 'INSERT INTO device.smart_nvme_vs (%s) VALUES %s'
dbhelper.run_insert(cur, sql, device_smart_nvme_vs)
def insert_into_all_tables(conn, report_id_serial, j):
cur = conn.cursor()
report_timestamp = j.get('report_timestamp')
cluster = {}
cluster['report_id'] = report_id_serial
cluster['cluster_id'] = j.get('report_id')
# If a field does not exist in the json then it will be inserted as "null" to the database
cluster['ts'] = report_timestamp
cluster['created'] = j.get('created')
if cluster['created'] == '0.000000':
print("Weird created value for cluster. Skipping\n")
return
cluster['channel_basic'] = 'basic' in j.get('channels', [])
cluster['channel_crash'] = 'crash' in j.get('channels', [])
cluster['channel_device'] = 'device' in j.get('channels', [])
cluster['channel_ident'] = 'ident' in j.get('channels', [])
cluster['total_bytes'] = j.get('usage', {}).get('total_bytes')
cluster['total_used_bytes'] = j.get('usage', {}).get('total_used_bytes')
cluster['osd_count'] = j.get('osd', {}).get('count')
cluster['mon_count'] = j.get('mon', {}).get('count')
cluster['ipv4_addr_mons'] = j.get('mon', {}).get('ipv4_addr_mons')
cluster['ipv6_addr_mons'] = j.get('mon', {}).get('ipv6_addr_mons')
cluster['v1_addr_mons'] = j.get('mon', {}).get('v1_addr_mons')
cluster['v2_addr_mons'] = j.get('mon', {}).get('v2_addr_mons')
cluster['rbd_num_pools'] = j.get('rbd', {}).get('num_pools')
cluster['fs_count'] = j.get('fs', {}).get('count')
cluster['hosts_num'] = j.get('hosts', {}).get('num')
cluster['pools_num'] = j.get('usage', {}).get('pools')
# Compatibility with older telemetry modules
cluster['pg_num'] = j.get('usage', {}).get('pg_num') or j.get('usage', {}).get('pg_num:')
sql = 'INSERT INTO grafana.ts_cluster (%s) VALUES %s'
dbhelper.run_insert(cur, sql, cluster)
for p in j.get('pools', []):
pool = {}
pool['ts'] = report_timestamp
pool['report_id'] = report_id_serial
pool['pool_idx'] = p.get('pool')
pool['pgp_num'] = p.get('pgp_num')
pool['pg_num'] = p.get('pg_num')
pool['size'] = p.get('size')
pool['min_size'] = p.get('min_size')
pool['cache_mode'] = p.get('cache_mode')
pool['target_max_objects'] = p.get('target_max_objects')
pool['target_max_bytes'] = p.get('target_max_bytes')
pool['pg_autoscale_mode'] = p.get('pg_autoscale_mode')
pool['type'] = p.get('type')
pool['ec_k'] = p.get('erasure_code_profile', {}).get('k')
pool['ec_m'] = p.get('erasure_code_profile', {}).get('m')
pool['ec_crush_failure_domain'] = p.get('erasure_code_profile', {}).get('crush_failure_domain')
pool['ec_plugin'] = p.get('erasure_code_profile', {}).get('plugin')
pool['ec_technique'] = p.get('erasure_code_profile', {}).get('technique')
sql = 'INSERT INTO grafana.pool (%s) VALUES %s'
dbhelper.run_insert(cur, sql, pool)
for entity, entity_val in j.get('metadata', {}).items():
for attr, attr_val in entity_val.items():
for value, total in attr_val.items():
metadata = {}
metadata['ts'] = report_timestamp
metadata['report_id'] = report_id_serial
metadata['entity'] = entity
metadata['attr'] = attr
metadata['value'] = value
metadata['total'] = total
sql = 'INSERT INTO grafana.metadata (%s) VALUES %s'
dbhelper.run_insert(cur, sql, metadata)
# Adding a normalized 'ceph_version' record
# to 'metadata' table by extracting the numeric version part
if attr == 'ceph_version':
metadata['attr'] = 'ceph_version_norm'
metadata['value'] = re.match('ceph version v*([0-9.]+|Dev).*', value).group(1)
dbhelper.run_insert(cur, sql, metadata)
for i in range(j.get('rbd', {}).get('num_pools', 0)):
rbd_pool = {}
rbd_pool['ts'] = report_timestamp
rbd_pool['report_id'] = report_id_serial
rbd_pool['pool_idx'] = i # This index is internal in the db
rbd_pool['num_images'] = j['rbd']['num_images_by_pool'][i] # FIXME Will crash in case key is missing
rbd_pool['mirroring'] = j['rbd']['mirroring_by_pool'][i]
sql = 'INSERT INTO grafana.rbd_pool (%s) VALUES %s'
dbhelper.run_insert(cur, sql, rbd_pool)
# Commiting once, so everything is one transaction
conn.commit()
def import_report(conn, r):
cur = conn.cursor()
# vmu stands for vendor_model_uuid
# (that's the original anonymized device id generated on the client side)
vmu = r['device_id']
# ts represents SMART scraping time
ts = r['report_stamp']
rep = json.loads(r['report'])
report_id = r['id']
error = rep.get('error')
"""
Order of insertion into tables:
1. device.spec
2. device.device (referencing device.spec['id'])
3. device.ts_device (referencing device.device['id])
"""
device_spec = {}
device = {}
ts_device = {}
device['vmu'] = vmu
device['host_id'] = rep.get('host_id', None)
# verify vmu.count('_') > 1 ?
device_spec['vendor'] = vmu[:vmu.find('_')]
device_spec['model'] = vmu[vmu.find('_') + 1:vmu.rfind('_')]
device_spec['capacity'] = parse_value_by_type(
rep.get('user_capacity', {}).get('bytes'))
ts_device['report_id'] = report_id
ts_device['ts'] = ts
ts_device['error'] = error
"""
In device.spec table a device's class can be 'normal' (which means we
recognize its vendor and model, and it's not reporting behind a VM or a HW
RAID controller). Yet, we may not know its complete spec, because all
devices by this spec are reporting invalid telemetry (due to an old version
of smartctl, sudoers issues, etc.). In this scenario the record in
device.spec table will contain values only for vendor, model, and class,
while either type, interface, capacity can be NULL.
"""
new_vendor, new_model, new_class = map_input(device_spec['vendor'],
device_spec['model'])
# Creating a record of the (vendor, model) mapping result for debugging:
mapping = {}
mapping['i_vendor'] = device_spec['vendor']
mapping['i_model'] = device_spec['model']
mapping['o_vendor'] = new_vendor
mapping['o_model'] = new_model
sql = """INSERT INTO device.mapping (%s) VALUES %s"""
dbhelper.run_insert(cur, sql, mapping)
# Assigning the new values
device_spec['vendor'] = new_vendor
device_spec['model'] = new_model
device_spec['class'] = new_class
rotation_rate = rep.get('rotation_rate')
interface = rep.get('device', {}).get('protocol')
device_spec['interface'] = interface.lower() if interface else None
"""
device.spec table holds unique (vendor, model) records and their specifications;
There are multiple devices with the same (vendor, model). Some devices report invalid smartctl
output - we can derive the correct spec from these which report valid stats alone.
"""
device_spec['type'] = get_device_type(error, device_spec['interface'],
rotation_rate, device_spec['class'],
report_id)
device['spec_id'] = fetch_spec_id(conn, device_spec)
device_id = fetch_device_id(cur, device)
ts_device['device_id'] = device_id
sql = """INSERT INTO device.ts_device (%s) VALUES %s"""
dbhelper.run_insert(cur, sql, ts_device)
smart_attr_nvme = rep.get('nvme_smart_health_information_log')
if smart_attr_nvme:
populate_device_smart_nvme(cur, smart_attr_nvme, device_id, report_id,
ts)
# Device's Vendor Specific extended SMART log page contents.
# Currently all records accidentally have this key, filtering nvme only:
if device_spec[
'interface'] == 'nvme' and 'nvme_smart_health_information_add_log' in rep:
populate_device_smart_nvme_vs(cur, device, rep, device_id, report_id,
ts)
sata_smart_attr = rep.get('ata_smart_attributes')
if sata_smart_attr:
populate_device_smart_sata(cur, sata_smart_attr, device_id, report_id,
ts)
# Committing everything as a single transaction
conn.commit()
cur.close()
def fetch_spec_id(conn, device_spec):
cur = conn.cursor()
"""
It's impossible to run "INSERT" with "ON CONFLICT (id) DO NOTHING",
and have the exiting id returned, thus we first check if the record
exists in device.spec before trying to insert.
"""
dict_cur_spec = conn.cursor(name='server_side_cursor_s_id',
withhold=True,
cursor_factory=psycopg2.extras.DictCursor)
dict_cur_spec.execute(
"""SELECT id, type, interface, capacity
FROM device.spec
WHERE vendor = %s AND model = %s
""", (device_spec['vendor'], device_spec['model']))
# fetchone() returns a None object in case of no results.
fetched_spec = dict_cur_spec.fetchone()
dict_cur_spec.close()
"""
The case where vendor & model exist in the DB, but other spec fields
couldn't be retrieved since the report was nearly empty (can happen when
smartctl version is < 7.0). In case these fields are empty, we assign
their corresponding values from newer reports (which might still be None).
"""
if fetched_spec is not None:
need_update = False
if fetched_spec['type'] is None and device_spec['type'] is not None:
fetched_spec['type'] = device_spec['type']
need_update = True
if fetched_spec['interface'] is None and device_spec[
'interface'] is not None:
fetched_spec['interface'] = device_spec['interface']
need_update = True
if fetched_spec['capacity'] is None and device_spec[
'capacity'] is not None:
fetched_spec['capacity'] = device_spec['capacity']
need_update = True
if need_update:
cur.execute(
"""UPDATE device.spec
SET
type = %s,
interface = %s,
capacity = %s
WHERE id = %s
""", (fetched_spec['type'], fetched_spec['interface'],
fetched_spec['capacity'], fetched_spec['id']))
cur.close()
return fetched_spec['id']
# These vendor & model are not in device_spec table, inserting:
else:
sql = """INSERT INTO device.spec (%s) VALUES %s
RETURNING id"""
dbhelper.run_insert(cur, sql, device_spec)
fetched_spec = cur.fetchone()
cur.close()
return fetched_spec[0]