Skip to main content

Custom Report Types and Column Reference

Every custom report type, what each is for, which date control it uses, and the columns available in each. Includes the pageCounts and supplies column naming pattern.

Written by Joshua Kern

Every custom report must specify a report type. The type determines which data is available to your query and which default query you start from. A Device report only has device columns; a Volume Analysis report has columns comparing meter reads between two dates.

Columns are scoped to the type. A column that works under one type will throw no such column under another, so pick the type first and let the schema panel show you what you have.


​

Which Type Should You Use?

Type

Use it for

Date control

Device

Fleet composition and device details. No meter values.

None

Install

Data collection agent status, versions, and health.

None

Current Meter

The latest meter and supply values for each device. No history.

None

Volume Analysis

Usage between two dates you choose.

Date range

Billing Period

Volume per device for a billing cycle.

Single billing date

Alerts

Alert events with acknowledgement and resolution status.

None

Monthly Volume (beta)

Month-over-month volume per device across the last year. May appear as Monthly Device Volume.

None (always the last 12 months)

Supplies (beta)

Every supply, currently installed and already replaced, with yield and cost metrics.

None

Billable Devices (beta)

Which devices Print Tracker billed you for, by month.

Start date and end date


​

Combining report types

Most report types can only query their own table. A few can also read a related type's data: a Device report can also use Install and Current Meter data, and an Alerts report can join to the meters table (see the Alerts example below). Monthly Volume cannot be combined with any other type. Referencing a table your report type does not allow fails with an error like report type 'X' cannot be combined with 'Y'.


​

Device

Device details only, with no meter values. Good for understanding fleet composition.

Column

Type

Description

id

varchar

Unique identifier for the device

dealer_id, dealer_name

varchar

The dealer entity. If you are a dealer, this is your root-level entity.

entity_id, entity_name

varchar

The entity the device is directly attached to, not the top of the tree

entity_relative_genealogy

text

The chain of entities from the one you ran the report on down to the device's entity. Filter with entity_relative_genealogy LIKE '%Parent Name%' to catch every device under a parent, including those in child entities.

integration_id

varchar

The device identifier imported from an integration such as e-Automate

asset_id

varchar

Your own identifier for the device

make, model

varchar

Manufacturer and model

serial_number, custom_serial_number

varchar

As reported by the device, and as overridden by a user in Web Admin

location, custom_location

varchar

As reported by the device, and as configured by a user

ip_address, mac_address, hostname, system_name

varchar

Network identity. system_name is the SNMP system name.

firmware

varchar

Firmware version or datecode currently installed

note

varchar

Notes attached to the device in Print Tracker

source_install_name

varchar

Machine name of the agent managing this device. Useful for tracing a device back to its install.

source_install_private_ip_address, source_install_public_ip_address

varchar

Private and public IP of the agent host

created_timestamp, modified_timestamp, latest_meter_timestamp

datetime

When created, when last modified, and the most recent meter upload

managed

boolean

Whether the device is managed or non-managed

Example: days since each device last reported. Useful for spotting stale devices.

SELECT
entity_name as 'Entity',
make as Make,
model as Model,
serial_number as 'Serial Number',
asset_id as 'Asset ID',
ip_address as 'IP Address',
DATETIME(latest_meter_timestamp) as 'Latest Meter Timestamp',
ROUND(JULIANDAY(datetime()) - JULIANDAY(latest_meter_timestamp)) as 'Days Since Last Meter',
managed as 'Managed'
FROM devices
WHERE managed = true
ORDER BY latest_meter_timestamp desc

Example: device count by make and model.

SELECT
make as Make,
model as Model,
COUNT(*) as 'Number of Devices'
FROM devices
GROUP BY make, model
ORDER BY COUNT(*) DESC


​

Install

Information about the data collection agents themselves.

Column

Type

Description

id, entity_id, entity_name

varchar

Install identity and the entity it belongs to

hostname

varchar

Hostname of the agent host

created_timestamp, modified_timestamp, last_check_in_timestamp

datetime

When registered, last modified, and last checked in. The check-in column is what identifies a stale install.

ip_address, public_ip_address

varchar

Private and public IP of the install

version

varchar

Agent version. Use this to find installs that are falling behind.

os, os_family, os_version, cpu_architecture

varchar

Host operating system details. Useful for finding end-of-life operating systems that block auto-upgrade.

antivirus

varchar

Antivirus installed on the host

is_laptop

boolean

Whether the agent is on a laptop, which makes it an unreliable host

is_local

boolean

Whether the agent is configured to track USB-connected devices (Windows only)

reporting_devices, active_devices

integer

Managed devices that reported a meter this month, versus all managed devices tracked. A gap between the two is a warning sign.

Example: installs that have not checked in for more than 24 hours, with a clickable link to each one.

SELECT
entity_name as 'Entity Name',
hostname as 'Hostname',
version as 'Version',
os as 'Operating System',
antivirus as 'Antivirus',
is_laptop as 'Laptop',
ip_address as 'IP Address',
DATETIME(created_timestamp) as 'Created',
DATETIME(last_check_in_timestamp) as 'Check-in',
printf('%d days', ROUND(JULIANDAY(DATE('now')) - JULIANDAY(last_check_in_timestamp), 0)) as 'Offline',
printf('https://app.printtrackerpro.com/entity/%s/installs/list?searchTerm=%s', entity_id, id) as 'Link'
FROM installs
WHERE status = 1
AND last_check_in_timestamp < DATE('now', '-24 hours')
ORDER BY last_check_in_timestamp DESC


​

Current Meter, Volume Analysis, and Billing Period

These three all read from meter data and share most of their columns. They differ in how they treat time.

  • Current Meter gives only the latest values, with columns prefixed pageCounts_ and supplies_. It has no meter history.

  • Volume Analysis compares two dates you pick, so each counter appears twice, prefixed first_ and last_. Volume is the difference between them.

  • Billing Period has the same columns as Volume Analysis, but instead of a date range you give it a single billing date.

All three carry the same device context columns: device_id, entity_id, entity_name, integration_id, asset_id, make, model, serial_number, device_serial_number, custom_serial_number, location, custom_location, ip_address, mac_address, hostname, system_name, firmware, latest_meter_timestamp, and managed. These device columns are never prefixed, even under Volume Analysis and Billing Period.

The first_ and last_ columns exist only under Volume Analysis and Billing Period. Using them under Current Meter throws no such column. Rather than hand-writing these names, set the report type first and copy them from the default query it loads.

Example: current counter values per device.

SELECT
entity_name as 'Entity',
make as Make,
model as Model,
serial_number as 'Serial Number',
asset_id as 'Asset ID',
ip_address as 'IP Address',
DATETIME(latest_meter_timestamp) as 'Latest Meter Timestamp',
pageCounts_default_total as 'Total',
pageCounts_default_totalBlack as 'Total Black',
pageCounts_default_totalColor as 'Total Color'
FROM meters
WHERE managed = true
ORDER BY latest_meter_timestamp desc

Example: total volume per device, using Volume Analysis. The last column estimates an average monthly volume from the days between the two reads.

SELECT
entity_name as 'Entity',
make as 'Make',
model as 'Model',
serial_number as 'Serial Number',
asset_id as 'Asset ID',
location as 'Location',
DATETIME(first_meter_timestamp) as 'First Timestamp',
DATETIME(last_meter_timestamp) as 'Last Timestamp',
ROUND(JULIANDAY(last_meter_timestamp) - JULIANDAY(first_meter_timestamp), 2) as 'Days Between Meters',
last_pageCounts_default_total - first_pageCounts_default_total as 'Total Volume',
ROUND((last_pageCounts_default_total - first_pageCounts_default_total) * 30.44 / (JULIANDAY(last_meter_timestamp) - JULIANDAY(first_meter_timestamp))) as 'Avg Monthly Volume'
FROM meters


​

How the billing period works

The billing period report has the same schema (columns) as the volume analysis report, however instead of picking a start date and an end date like you would for a volume analysis report, you specify only a billing date. This report is based on a device's billing schedule. Devices are included in this report as long as the device's billing schedule aligns with your chosen billing date.

Billing periods are sometimes tricky to understand, so let's walk through an example. Let's assume that we have the following devices each with a different billing schedule:

Device

Billing Schedule

HP LaserJet M477

1st day of the month, bi-monthly

HP OfficeJet 8700

1st day of the month, monthly

You may notice that while these two devices have differing billing schedules, they actually both need to be billed on the same day, every other month. In the following diagram, notice how in month 1 (first blue circle) we only bill the customer for the HP OfficeJet 8700 device. However in the second month (second blue circle) we bill the customer for both devices, since one of the devices should only be billed bi-monthly.
​

Diagram of two devices with different billing schedules


The billing period report takes into consideration this idea of differing billing schedules by device and allows you to create a single report that includes all devices whose billing schedules land on the date that you select when configuring the report regardless of the interval of each device's billing schedule.
​

We mentioned previously that the billing period report has all the same columns as the volume analysis report, which means that the report gives us access to meter read columns from both the first and the last meters in the billing period. While the last meter read timestamps will almost always be the same date across your devices (the report's billing date), the first meter read can vary from device to device. In the previous example, when we receive our billing period report, the first meter read of the HP LaserJet M477 will be two months ago, and the first meter read of the HP OfficeJet 8700 will be one month ago.


​

Alerts

One row per alert event, with the device context attached so you can investigate or route work without joining anything.

Use it to triage unacknowledged alerts, drive proactive supply fulfillment, measure time to acknowledge or resolve, find devices generating excessive alert noise, and audit what happened on a device and who acknowledged it.


​

Alert metadata

Column

Type

Description

id

string

Unique identifier for the alert

timestamp

datetime

When the alert was raised

description

string

Human-readable description

alert_type

string

One of: Low supply, Low maintenance supply, Replacement, Premature replacement, Service, Volume

resolution_status

string

One of: New, In Progress, Resolved


​

Acknowledgement workflow

Column

Type

Description

acknowledged

bool

Whether the alert has been acknowledged

acknowledged_user, acknowledged_user_id

string (nullable)

Who acknowledged it

acknowledged_note

string (nullable)

Optional note added at acknowledgement

acknowledged_timestamp

datetime (nullable)

When it was acknowledged. Pair with timestamp to measure response time.


​

Device context and supply details

Device columns are prefixed device_ and hold current values: device_id, device_make, device_model, device_serial_number, device_mac_address, device_ip_address, device_hostname, device_firmware, device_location, device_asset_id, device_integration_id, device_managed, and device_note, plus entity_id, entity_name, and dynamic_device_group.

Supply columns describe the supply at alert time: supply, supply_type, supply_color, supply_description, supply_part_number, supply_serial_number, and remaining, which is usually a percentage from 0 to 100.

Fields marked nullable can be empty when the device does not provide the data, or it did not apply at alert time.

Alerts fire in response to a meter read crossing a configured threshold. That meter read is available through meter_id, which joins to the meters table, so you can report alert context and counter values together.


​

Example: low supply alerts in the last 24 hours, with counters

SELECT
DATETIME(a.timestamp) AS "Timestamp (UTC)",
a.entity_name AS "Entity",
a.device_make || ' ' || a.device_model AS "Device",
a.device_serial_number AS "Device Serial Number",
a.device_asset_id AS "Asset ID",
a.alert_type AS "Alert Type",
a.description AS "Description",
a.supply AS "Supply",
a.remaining AS "Remaining (%)",
m.pageCounts_default_total AS "Total Pages",
m.pageCounts_default_totalBlack AS "Total Black",
m.pageCounts_default_totalColor AS "Total Color",
a.acknowledged AS "Acknowledged",
a.acknowledged_user AS "Acknowledged By",
DATETIME(a.acknowledged_timestamp) AS "Acknowledged Timestamp"
FROM alerts a
LEFT JOIN meters m ON m.meter_id = a.meter_id
WHERE DATETIME(a.timestamp) >= DATETIME('now', '-24 hours')
AND a.alert_type = 'Low supply'
ORDER BY a.timestamp DESC;

Swap the alert_type filter for 'Service' to get service alerts instead, or drop it and filter on a.remaining < 25 to find every alert raised on a low supply.


​

Supplies (beta)

Every supply in your devices, both currently installed and already replaced. This is the type to use for yield, waste, and cost analysis, and for each supply's estimated depletion date.

Device columns are prefixed device_. Supply columns include:

Column

Description

name, type, color

Supply name, classification (toner, drum, waste), and color channel where applicable

part_number, serial_number, description

Identifiers and the OEM description as reported by the device

installed_date, replaced_date, confirmed_replaced_date

Lifecycle dates. replaced_date is null while the supply is still installed, and the confirmed date is usually a few hours later.

estimated_depletion_date

When the supply is expected to reach 0%, from historical printing patterns

remaining, remaining_at_install, remaining_at_replacement

Current level, level when first seen, and level when replaced. The last one shows how much supply was thrown away.

pages_printed, expected_yield, lost_pages

Actual pages, the yield the supply should have delivered, and the difference when replaced early

fill_rate, actual_cost_per_page

Coverage rate, and cost per page if a supply cost has been entered

Note that remaining_at_install is not always 100%: on high-volume devices Print Tracker does not always see the new supply the moment it goes in. See Managing Supplies for where these values are configured.


​

Monthly Volume (beta)

Aggregates daily meter reads into monthly buckets, one row per device per month, across the last 12 months. The earliest month in the window has no volume, because there is no prior month to subtract from. This report does not support including children, and it cannot be combined with any other report type.

Its table is named devices, but it is a different table from the Device report's devices, with its own column set:

  • Device identity: id, make, model, serial_number, mac_address, ip_address, hostname, system_name, firmware, location, asset_id, integration_id, dynamic_device_group

  • Month bucket: year, month_number, month, meter_read_id, meter_read_timestamp

  • Closing counters for the month: total, total_black, total_color

  • Volume printed during the month: total_volume, total_black_volume, total_color_volume

Monthly Volume has no entity_name or entity_id column. Querying either throws no such column. To label the rows by customer, export Monthly Volume including id, export a Device report with id and entity_name, and match the two files on id in a spreadsheet. Both id columns are the same device identifier.

Export it and build a pivot table in a spreadsheet. That is what the layout is designed for.

Monthly volume data in an Excel pivot table


​

Billable Devices (beta)

Print Tracker does not bill your customers. This report helps you understand your own Print Tracker costs and which customers drive them.

One record per device per month that Print Tracker billed you for it. Columns are the usual device identity fields plus entity_name, entity_id, dealer_name, dealer_id, year, month_number, and month. meter_read_timestamp is the date the device became billable that month. This report has no page count columns; for volume, use Volume Analysis or Monthly Volume.

Example: billable devices grouped by customer.

SELECT
entity_name as 'Entity',
month as 'Month',
year as 'Year',
count() as 'Billable Devices'
FROM billable_devices
GROUP BY entity_id, year, month
ORDER BY entity_name, year, month_number;

Swap entity_ for dealer_ throughout to group by dealer instead, which is the view a reseller wants.


​

Meter and Supply Column Names

Custom reports support over 450 page count columns and over 375 supply columns, and the list grows. They are not all listed here because they follow a predictable pattern:

pageCounts_<format>_<counter>
supplies_<supply>_<attribute>
  • Format is how the counter is measured. Common ones are equiv (equivalent), engine, and life. See Billing Meter Format Mismatch for what these mean.

  • Counter is what is counted: total, totalBlack, totalColor, totalCopies, totalPrints, and many more.

  • Supply is the supply name, such as blackToner, wasteToner, or cyanToner.

  • Attribute is the property. Most devices provide pctRemaining, description, and type; some also provide fill rate and estimated depletion.

Commonly used examples:

  • pageCounts_equiv_total, pageCounts_equiv_totalBlack, pageCounts_equiv_totalColor

  • pageCounts_life_total, pageCounts_life_totalBlack, pageCounts_life_totalColor

  • pageCounts_default_total, which follows the billing format you selected for the device

  • supplies_blackToner_pctRemaining, supplies_blackToner_type, supplies_blackToner_description

  • supplies_waste_pctRemaining

Inkjet devices report ink rather than toner, so cover both with COALESCE(supplies_blackToner_pctRemaining, supplies_blackInk_pctRemaining) (and the same for cyan, magenta, and yellow). For large-format counts, COALESCE(pageCounts_default_a3, pageCounts_default_totalLarge) covers both naming styles.

To find what a specific device actually reports, search pageCounts or supplies in the schema viewer with your report type selected. If the Table Schema panel is empty, click the column search box and press Enter; the panel fills once the report has run.

Not every counter a device reports is available in custom reports. Reports only expose the counters Print Tracker's report engine supports, so a counter can appear on a device's page in Web Admin and still be missing from the schema (for example, the duplex counter totalDuplex). If a counter you need is missing, contact support.


​

Contacting Support

Print Tracker support will help you configure a custom report. Describe the report you want and the data you expect to see.


​

Related Articles

  • Getting Started with Custom Reports

  • Scheduling Reports

  • Billing Meter Format Mismatch: Life vs Equivalent

  • Managing Supplies

Did this answer your question?