Is there an existing method to find the expired assets in aem as a cloud?

[ReddyIshanvi]

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?

[lukasz-m · Accepted solution]

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

• https://experienceleague.adobe.com/docs/experience-manager-cloud-service/content/assets/manage/drm.h...

[ReddyIshanvi]

Thank you @lukasz-m