iot_devices (device_id, model) lists the fleet. device_attrs (attr_id, device_id, attr_name, attr_value, recorded_at) is an attribute log written by several agents over the years: one row per reported value.
Build one row per device with four attribute columns — os, firmware, site and owner:
- •Attribute names match ignoring letter case and surrounding spaces (
' OS' is os). Any other attribute name is ignored. - •A device's value for an attribute is the one with the latest
recorded_at; at the same time, the larger attr_id. Readings are sometimes back-filled, so attr_id doesn't follow recorded_at. - •A NULL
attr_value means the attribute was cleared. If the latest value is NULL, the column is NULL — don't fall back to an older value. - •Values are text: return them exactly as recorded.
- •Every device appears, including one with no attributes.
Columns: device_id, model, os, firmware, site, owner. Sort by device_id.