...

This example query retrieves a list of devices OVER 3 years old. The query uses today (NOW) and system.purchase_date as the reference point. You could do something similar with system.warranty_expires and look for a warranty expiration date within the next 90-days or already expired.

SELECT system.id AS `system.id`, system.purchase_date AS 'system.purchase_date', system.type AS `system.type`, system.name AS `system.name`, system.last_seen AS `system.last_seen`, system.manufacturer AS `system.manufacturer`, system.model AS `system.model`, system.description AS `system.description`, system.function AS `system.function`, locations.name AS `locations.name` FROM system LEFT JOIN locations ON (system.location_id = locations.id) LEFT JOIN windows ON (system.id = windows.system_id AND windows.current = 'y') LEFT JOIN orgs ON (system.org_id = orgs.id) WHERE @filter HAVING system.purchase_date < DATE_SUB(NOW(),INTERVAL 3 YEAR)

Devices with Expired Warranties

This example uses system.warranty_expires and looks for a warranty expiration date prior to today.

SELECT system.id AS `system.id`, system.warranty_expires AS 'system.warranty_expires', system.type AS `system.type`, system.name AS `system.name`, system.last_seen AS `system.last_seen`, system.manufacturer AS `system.manufacturer`, system.model AS `system.model`, system.description AS `system.description`, system.function AS `system.function`, locations.name AS `locations.name` FROM system LEFT JOIN locations ON (system.location_id = locations.id) LEFT JOIN windows ON (system.id = windows.system_id AND windows.current = 'y') LEFT JOIN orgs ON (system.org_id = orgs.id) WHERE @filter HAVING system.warranty_expires <= CURDATE()

Database Schema

The schema for the database is below. It can also be found in the application if the user has database::read permission by going to menu: Manage -> Database -> List Database, then clicking on the "queries" table.

...