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]