I a puzzling one!
IN AEM 6.1 how can one print a report for the following;
"Assets which have been published and on a live page for over a year"
try this query in /crx/de. Modify the date and other fields as required. This would just give the published assets and not the referenced assets -
select * from [dam:Asset] as node where node.[jcr:content/cq:lastReplicated] > CAST('2017-01-01T00:00:01.000Z' AS DATE) and node.[jcr:content/cq:lastReplicationAction] = 'Activate' and ISDESCENDANTNODE(node, [/content/dam/we-retail])
Now create a join for reference property or otherwise write code using
SQL2 reference -
This should be custom built but it should be pretty straight forward with the right query based on the properties you would need and objective of the report