useful reports?

Don't know if these will be of use to anyone - developed on V7022 with MS SQL backend.

Servers with age in years

SELECT RS.RESOURCENAME, DATEADD(s, RS.ACQUISITIONDATE / 1000, '1970-01-01 00:00:00') AS 'Purchase Date', DATEDIFF(yy, DATEADD(s,
RS.ACQUISITIONDATE / 1000, '1970-01-01 00:00:00'), GETDATE()) AS 'Age'
FROM ComponentDefinition AS CD INNER JOIN
Resources AS RS ON CD.COMPONENTID = RS.COMPONENTID INNER JOIN
ComponentType AS CT ON CD.COMPONENTTYPEID = CT.COMPONENTTYPEID
WHERE (CT.COMPONENTTYPENAME = 'Server') OR
(CT.COMPONENTTYPENAME = 'Servers')

Servers and Workstations by age ( excluding disposed and expired )

SELECT CT.COMPONENTTYPENAME AS 'Hardware', DATEDIFF(yy, DATEADD(s, RS.ACQUISITIONDATE / 1000, '1970-01-01 00:00:00'), GETDATE()) AS 'Age',
COUNT(*) AS 'Qty'
FROM ComponentDefinition AS CD INNER JOIN
Resources AS RS ON CD.COMPONENTID = RS.COMPONENTID INNER JOIN
ComponentType AS CT ON CD.COMPONENTTYPEID = CT.COMPONENTTYPEID
WHERE (CT.COMPONENTTYPENAME = 'Server' OR
CT.COMPONENTTYPENAME = 'Servers' OR
CT.COMPONENTTYPENAME = 'Workstation') AND (RS.RESOURCESTATEID <> 4) AND (RS.RESOURCESTATEID <> 5) GROUP BY CT.COMPONENTTYPENAME, DATEDIFF(yy, DATEADD(s, RS.ACQUISITIONDATE / 1000, '1970-01-01 00:00:00'), GETDATE()) ORDER BY 'Hardware', 'Age'

Server and Workstation Summary

SELECT Hardware, F123 AS 'Item Age', COUNT(*) AS 'Count'
FROM (SELECT CASE WHEN rs.componentid IN
(SELECT componentid
FROM componentdefinitionlaptop) THEN 'Laptop' ELSE CT.COMPONENTTYPENAME END AS Hardware, DATEDIFF(yy, DATEADD(s,
RS.ACQUISITIONDATE / 1000, '1970-01-01 00:00:00'), GETDATE()) AS F123
FROM ComponentDefinition AS CD INNER JOIN
Resources AS RS ON CD.COMPONENTID = RS.COMPONENTID INNER JOIN
ComponentType AS CT ON CD.COMPONENTTYPEID = CT.COMPONENTTYPEID
WHERE (CT.COMPONENTTYPENAME = 'Servers' OR
CT.COMPONENTTYPENAME = 'Servers' OR
CT.COMPONENTTYPENAME = 'Workstation') AND (RS.RESOURCESTATEID <> 4) AND (RS.RESOURCESTATEID <> 5)) AS HW GROUP BY Hardware, F123 ORDER BY Hardware, 'Item Age'

Servers and Workstations with deployment without purchase date or over 4 years

SELECT WAF.UDF_CHAR1 AS 'Deployment', CT.COMPONENTTYPENAME AS 'Hardware', DATEDIFF(yy, DATEADD(s, RS.ACQUISITIONDATE / 1000,
'1970-01-01 00:00:00'), GETDATE()) AS 'Age', COUNT(*) AS 'Qty'
FROM ComponentDefinition AS CD INNER JOIN
Resources AS RS ON CD.COMPONENTID = RS.COMPONENTID INNER JOIN
ComponentType AS CT ON CD.COMPONENTTYPEID = CT.COMPONENTTYPEID LEFT OUTER JOIN
Workstation_Fields AS WAF ON RS.RESOURCEID = WAF.WORKSTATIONID
WHERE (CT.COMPONENTTYPENAME = 'Server' OR
CT.COMPONENTTYPENAME = 'Servers' OR
CT.COMPONENTTYPENAME = 'Workstation') AND (RS.RESOURCESTATEID <> 4) AND (RS.RESOURCESTATEID <> 5) GROUP BY WAF.UDF_CHAR1, CT.COMPONENTTYPENAME, DATEDIFF(yy, DATEADD(s, RS.ACQUISITIONDATE / 1000, '1970-01-01 00:00:00'), GETDATE()) ORDER BY 'Deployment', 'Hardware', 'Age'

The last one we created a Workstation additional field, the first one, and define whether the item is designated for Production, Pre-Prod, UAT, Test, Dev, DR/BR etc
It just helps justify a 7 year old server for instance that is only being used for development duties or highlights items where the purchase date isn't entered or Production items that are out of refresh cycle - assuming you replace within 4 years.

To use any of these with MySQL you will have to change the date calculations to use the appropriate Unix format date functions.

Regards,
Andy