Solved
Is there an existing method to find the expired assets in aem as a cloud?
I am writing a scheduler which runs a query to find the expired assets but it's not working. Is there an existing method/function to retrieve the expired assets?
Hi @reddyishanvi,
Assuming you would like to use OOTB Expires filed that can be set via asset properties.
OOTB Expires property is represented by prism:expirationDate property in crx, you can use below SQL2 query. Of course you have to set proper date value.
SELECT * FROM [dam:Asset] AS asset WHERE ISDESCENDANTNODE([/content/dam]) AND asset.[jcr:content/metadata/prism:expirationDate] <= CAST('2022-07-28T00:00:00.000Z' AS DATE)
Above query works for me without any issue on AEM as a Cloud Service version 2022.5.7575.20220530T152407Z
This filed is also used by OOTB asset filtering mechanism, below documentation describes expiration mechanism quite well