This Groovy script can be used to delete assets that have never been published or deactivated assets that are not referenced in any pages or DAM. It is a cleanup script. Optionally you can generate a report. It requires the Groovy Console

delete-unpublished-nonreferenced-assets.groovy

import javax.jcr.Node;
import javax.jcr.NodeIterator;
import javax.jcr.RepositoryException;
import javax.jcr.Session;
import javax.jcr.query.Query;
import javax.jcr.query.QueryManager;
import javax.jcr.query.QueryResult;

final String BASE_UNPUBLISHED_STATEMENT = "select * from [dam:Asset] as a where([jcr:content/cq:lastReplicationAction] is null or [jcr:content/cq:lastReplicationAction] = 'Deactivate') and isdescendantnode(a, '%s')";

final String unpublishedAssetsStatement = String.format(BASE_UNPUBLISHED_STATEMENT, "/content/dam/test");

try {
    QueryManager queryManager = session.workspace.queryManager;
    Query query = queryManager.createQuery(unpublishedAssetsStatement, Query.JCR_SQL2);
    QueryResult result = query.execute();
    ArrayList report = [];
    
    final NodeIterator nodeIterator = result.getNodes();

    while (nodeIterator.hasNext()) {
        final Node assetNode = nodeIterator.nextNode();

        if (isAssetNotReferenced(assetNode.getPath(), queryManager)) { // The asset is not referenced, it can be deleted
            println "Deleting asset node: ${assetNode.getPath()}";
            report.add(assetNode.getPath()); // Add deleted node to the report.
            assetNode.remove();
            session.save();
        } else { // The asset is referenced somewhere, no action should be taken
            println "Node is referenced ${assetNode.getPath()}, it has not been deleted";
        }
    }
    
    saveReport(report);

} catch (Exception e) {
    println "Exception, stacktrace: ${e}";
}
            
boolean isAssetNotReferenced(String assetPath, QueryManager queryManager) throws RepositoryException {
    final String BASE_PAGE_REFERENCES_STATEMENT = "select * from [cq:Page] as s where contains(s.*, \"%s\") and ISDESCENDANTNODE([/content])";
    final String BASE_ASSET_REFERENCES_STATEMENT = "select * from [dam:Asset] as s where contains(s.*, \"%s\") and ISDESCENDANTNODE([/content/dam])";


    String getAssetReferencesStatament = String.format(BASE_PAGE_REFERENCES_STATEMENT, assetPath);

    Query isAssetReferenced = queryManager.createQuery(getAssetReferencesStatament, Query.JCR_SQL2);

    final QueryResult isAssetPageReferencedResult = isAssetReferenced.execute();
    final NodeIterator isAssetPageReferencedIterator = isAssetPageReferencedResult.getNodes();

    if (isAssetPageReferencedIterator.getSize() == 0) {

        getAssetReferencesStatament = String.format(BASE_ASSET_REFERENCES_STATEMENT, assetPath);

        isAssetReferenced = queryManager.createQuery(getAssetReferencesStatament, Query.JCR_SQL2);

        final QueryResult isAssetReferencedResult = isAssetReferenced.execute();
        final NodeIterator isAssetReferencedIterator = isAssetReferencedResult.getNodes();

        return (isAssetReferencedIterator.getSize() == 0);
    } else {
        return false;
    }
}

def saveReport(ArrayList report) {
  AssetManager assetManager = resourceResolver.adaptTo(AssetManager.class);
  String filename = "report.csv";
  File csvFile = new File(filename);
  FileWriter fileWriter = new FileWriter(csvFile);
  
  for(String line : report) {
      fileWriter.write(line + "," + System.lineSeparator());
  }

  fileWriter.close();

  InputStream fileInputStream = new FileInputStream(csvFile);

  assetManager.createAsset("/content/dam/test/"+ filename,fileInputStream, null, true);
}