Asset report

[key-key]

HI

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"

Thanks

KJ

[smacdonald2008]

AEM Assets do not contain properties that store this information.

[Gaurav-Behl]

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

aem-samples/ReferencedAssetsServlet.java at master · Adobe-Marketing-Cloud/aem-samples · GitHub

AssetReferenceSearch ("The Adobe AEM Quickstart and Web Application.")

SQL2 reference -

http://drfits.com/jcr-sql2-query-with-examples/

9 JCR-SQL2 Queries Every AEM Developer Should Know - Blog - 6D Global

[key-key]

thank you!

[Lokesh_Shivalingaiah]

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