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 |
| varchar | Unique identifier for the device |
| varchar | The dealer entity. If you are a dealer, this is your root-level entity. |
| varchar | The entity the device is directly attached to, not the top of the tree |
| text | The chain of entities from the one you ran the report on down to the device's entity. Filter with |
| varchar | The device identifier imported from an integration such as e-Automate |
| varchar | Your own identifier for the device |
| varchar | Manufacturer and model |
| varchar | As reported by the device, and as overridden by a user in Web Admin |
| varchar | As reported by the device, and as configured by a user |
| varchar | Network identity. |
| varchar | Firmware version or datecode currently installed |
| varchar | Notes attached to the device in Print Tracker |
| varchar | Machine name of the agent managing this device. Useful for tracing a device back to its install. |
| varchar | Private and public IP of the agent host |
| datetime | When created, when last modified, and the most recent meter upload |
| 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 |
| varchar | Install identity and the entity it belongs to |
| varchar | Hostname of the agent host |
| datetime | When registered, last modified, and last checked in. The check-in column is what identifies a stale install. |
| varchar | Private and public IP of the install |
| varchar | Agent version. Use this to find installs that are falling behind. |
| varchar | Host operating system details. Useful for finding end-of-life operating systems that block auto-upgrade. |
| varchar | Antivirus installed on the host |
| boolean | Whether the agent is on a laptop, which makes it an unreliable host |
| boolean | Whether the agent is configured to track USB-connected devices (Windows only) |
| 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_andsupplies_. It has no meter history.Volume Analysis compares two dates you pick, so each counter appears twice, prefixed
first_andlast_. 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.
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 |
| string | Unique identifier for the alert |
| datetime | When the alert was raised |
| string | Human-readable description |
| string | One of: Low supply, Low maintenance supply, Replacement, Premature replacement, Service, Volume |
| string | One of: New, In Progress, Resolved |
Acknowledgement workflow
Column | Type | Description |
| bool | Whether the alert has been acknowledged |
| string (nullable) | Who acknowledged it |
| string (nullable) | Optional note added at acknowledgement |
| datetime (nullable) | When it was acknowledged. Pair with |
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 |
| Supply name, classification (toner, drum, waste), and color channel where applicable |
| Identifiers and the OEM description as reported by the device |
| Lifecycle dates. |
| When the supply is expected to reach 0%, from historical printing patterns |
| Current level, level when first seen, and level when replaced. The last one shows how much supply was thrown away. |
| Actual pages, the yield the supply should have delivered, and the difference when replaced early |
| 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_groupMonth bucket:
year,month_number,month,meter_read_id,meter_read_timestampClosing counters for the month:
total,total_black,total_colorVolume 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.
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, andlife. 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_totalColorpageCounts_life_total,pageCounts_life_totalBlack,pageCounts_life_totalColorpageCounts_default_total, which follows the billing format you selected for the devicesupplies_blackToner_pctRemaining,supplies_blackToner_type,supplies_blackToner_descriptionsupplies_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