What it is

The Devices report lists one row per device in your fleet. It includes ownership (entity/dealer), identifiers (serials, asset IDs), network/system details (IP/MAC/hostname/firmware), location and grouping, management state, data-freshness timestamps, and average monthly volumes (black/color/total). Use it for inventory, lifecycle tracking, and operational reporting.

Practical applications

Inventory & audits: Produce clean asset lists with make/model/serial, IP/MAC, and locations for customers or internal audits.
Data freshness checks: Find devices whose latest_meter_timestamp is stale (e.g., >14 days) to triage connectivity issues.
Change tracking: Monitor ip_address_changed_timestamp to catch DHCP changes or misconfigured statics.
Fleet planning: Rank by average_monthly_volume to right-size contracts, toner stocking, or device placement.
Grouping & routing: Filter by dynamic_device_group, entity_relative_genealogy, or dealer_name for workload routing.
Billing format governance: Compare selected_billing_meter_format to available_billing_meter_formats to ensure consistent billing rollups.
Install footprint: Use install_ids to understand which collectors/installs report each device.

Supported columns

Identity & ownership

Column	Type	Description
`id`	varchar(24)	Internal device ID (primary key).
`entity_id`	varchar(24)	Owning entity (customer/site) ID.
`entity_name`	varchar(255)	Owning entity name.
`entity_relative_genealogy`	text	Entity’s hierarchical path for grouping/reporting formatted as a JSON array. SQLite's JSON operators can be used to work with this column. It includes the genealogy relative to the entity where the report was executed (excluding any hierarchy above this entity).
`dealer_id`	varchar(24)	Dealer ID.
`dealer_name`	varchar(255)	Dealer name.

Install & integration

Column	Type	Description
`install_ids`	text	JSON array of install/collector IDs associated with the device. SQLite's JSON operators can be used to work with this column.
`integration_id`	varchar(255)	External system ID (e.g., ERP/PSA mapping).
`source_install_name`	varchar(255)	Name of the install/collector that sourced the latest data.
`source_install_private_ip_address`	varchar(255)	Private IP of the source install.
`source_install_public_ip_address`	varchar(255)	Public IP of the source install.

Device metadata

Column	Type	Description
`make`	varchar(255)	Manufacturer (e.g., HP, Brother).
`model`	varchar(255)	Model name/number.
`serial_number`	varchar(255)	Reported device serial (as read from the device).
`device_serial_number`	varchar(255)	Device serial (canonicalized form when available).
`custom_serial_number`	varchar(255)	User-provided serial override.

Network & system

Column	Type	Description
`firmware`	varchar(255)	Firmware/version string.
`ip_address`	varchar(255)	Last known IP address.
`ip_address_changed_timestamp`	datetime	When the IP last changed (if tracked).
`mac_address`	varchar(255)	MAC address.
`hostname`	varchar(255)	DNS hostname.
`system_name`	varchar(255)	SNMP system name.

Location & asseting

Column	Type	Description
`location`	varchar(255)	Device-reported or discovered location.
`custom_location`	varchar(255)	User-provided location override.
`asset_id`	varchar(255)	Internal asset/inventory ID.
`dynamic_device_group`	varchar(255)	Dynamic group label matched by rules.

Status, notes, and console

Column	Type	Description
`managed`	boolean	Whether the device is managed.
`note`	text	Free-form device note.
`console_message`	text	Message shown in UI/console for this device.

Data freshness & lifecycle

Column	Type	Description
`latest_meter_timestamp`	datetime	Most recent meter data timestamp for this device.
`created_timestamp`	datetime	When the device record was created.
`modified_timestamp`	datetime	When the device record was last updated.

Billing meter formats

Column	Type	Description
`selected_billing_meter_format`	text	String indicating the chosen billing meter format for this device.
`available_billing_meter_formats`	text	JSON array of supported billing meter formats for this device.

Average monthly volumes

Column	Type	Description
`average_monthly_volume`	real	Estimated total monthly volume.
`average_monthly_volume_black`	real	Estimated monthly black volume.
`average_monthly_volume_color`	real	Estimated monthly color volume.

Examples

Device inventory (common columns), newest first

SELECT
  d.entity_name                           AS "Entity",
  d.make || ' ' || d.model                AS "Device",
  d.serial_number                         AS "Serial (Reported)",
  COALESCE(d.custom_serial_number, d.device_serial_number, d.serial_number)
                                           AS "Serial (Preferred)",
  d.asset_id                              AS "Asset ID",
  d.ip_address                            AS "IP Address",
  d.mac_address                           AS "MAC",
  d.hostname                              AS "Hostname",
  COALESCE(d.custom_location, d.location) AS "Location",
  DATETIME(d.latest_meter_timestamp)      AS "Latest Meter (UTC)",
  d.managed                               AS "Managed"
FROM devices d
ORDER BY d.latest_meter_timestamp DESC, d.entity_name, d.make, d.model;

Devices with IP address changes in the last 7 days

SELECT
  d.entity_name                      AS "Entity",
  d.make || ' ' || d.model           AS "Device",
  d.serial_number                    AS "Serial",
  d.ip_address                       AS "Current IP",
  DATETIME(d.ip_address_changed_timestamp) AS "IP Changed (UTC)"
FROM devices d
WHERE d.ip_address_changed_timestamp IS NOT NULL
  AND DATETIME(d.ip_address_changed_timestamp) >= DATETIME('now', '-7 days')
ORDER BY d.ip_address_changed_timestamp DESC;

Managed devices with stale meters (no update in 14 days)

SELECT
  d.entity_name                      AS "Entity",
  d.make || ' ' || d.model           AS "Device",
  d.serial_number                    AS "Serial",
  DATETIME(d.latest_meter_timestamp) AS "Latest Meter (UTC)",
  ROUND((julianday('now') - julianday(d.latest_meter_timestamp)), 1)
                                      AS "Days Since Meter"
FROM devices d
WHERE d.managed = 1
  AND (d.latest_meter_timestamp IS NULL
       OR DATETIME(d.latest_meter_timestamp) < DATETIME('now', '-14 days'))
ORDER BY d.latest_meter_timestamp NULLS FIRST;

Top 50 devices by average monthly volume (total), with black/color split

SELECT
  d.entity_name                         AS "Entity",
  d.make || ' ' || d.model              AS "Device",
  d.serial_number                       AS "Serial",
  ROUND(d.average_monthly_volume)       AS "AMV (Total)",
  ROUND(d.average_monthly_volume_black) AS "AMV (Black)",
  ROUND(d.average_monthly_volume_color) AS "AMV (Color)"
FROM devices d
WHERE d.average_monthly_volume IS NOT NULL
ORDER BY d.average_monthly_volume DESC
LIMIT 50;

Count devices per dynamic group and managed state

SELECT
  COALESCE(d.dynamic_device_group, '(none)') AS "Dynamic Group",
  d.managed                                   AS "Managed",
  COUNT(*)                                    AS "Devices"
FROM devices d
GROUP BY COALESCE(d.dynamic_device_group, '(none)'), d.managed
ORDER BY "Devices" DESC;

How many installs (data collection agents) report each device (JSON)

SELECT
  d.entity_name                           AS "Entity",
  d.make || ' ' || d.model                AS "Device",
  d.serial_number                         AS "Serial",
  json_array_length(d.install_ids)        AS "Install Count"
FROM devices d
ORDER BY "Install Count" DESC, d.entity_name;

Devices where the selected billing format is not in the available list

SELECT
  d.entity_name                                AS "Entity",
  d.make || ' ' || d.model                     AS "Device",
  d.serial_number                              AS "Serial",
  d.selected_billing_meter_format              AS "Selected Format",
  d.available_billing_meter_formats            AS "Available Formats (JSON)"
FROM devices d
WHERE d.selected_billing_meter_format IS NOT NULL
  AND NOT EXISTS (
    SELECT 1
    FROM json_each(d.available_billing_meter_formats) af
    WHERE af.value = d.selected_billing_meter_format
  )
ORDER BY d.entity_name, d.make, d.model;

Getting started with custom reports

Alerts Custom Report

Devices Custom Report