Reporting using Groovy scripts

It's often necessary to get different kinds of statistics on CQ data. You can do it in several ways:

• using build-in CQ reporting engine . Unfortunately it is pretty inflexible and requires lot's of configuration to be done, so I would recommend using it for simple JC queries only;

• writing custom components with required logic in ESP/JSP/Java/Groovy

• use third-party tools like Splunk which parses CQ logs (you should have required data in logs! ;-)  

In this blog post I focus on groovy scripts as one of the most quick ways to operate with CQ API.
To use groovy scripts you can use pretty nice groovy console, which is ultimately installed as a CQ package.

As an example, let's write a simple script which will gather DAM statistics (# of all assets in JCR repository, total DAM storage size, asset name, asset path, asset file size).

First you need to install groovy console according to instructions from https://github.com/Citytechinc/cq-groovy-console
Once installed, navigate to http://localhost:4502/groovyconsole. You should see code area in the center of the screen, Type the following code there:

import javax.jcr.query.*
import com.day.cq.dam.api.*
import javax.jcr.*
import org.apache.sling.api.resource.*

def query = createSQL2Query("/content/dam") // TODO start folder for search
def result = query.execute()
def rows = result.rows

totalSize = 0
rows.each { row ->
    Resource res = resourceResolver.getResource(row.path)
    Asset asset = res.adaptTo(Asset.class)
    fileSize = asset.getOriginal().getSize();
    totalSize += fileSize
//    println "Name=$asset.path; fileSize=$fileSize"
}
println "Total assets number: ${rows.size}; Total file size: ${formatFileSize(totalSize)}"


def createSQL2Query(startPage) {
    def queryManager = session.workspace.queryManager

    def statement = "select * from [nt:base] as p where (isdescendantnode (p, '$startPage')) and p.[jcr:primaryType] = 'dam:Asset'"

    def query = queryManager.createQuery(statement, Query.JCR_SQL2)

    query
}

// Convert a number of bytes to a more readable string
def formatFileSize(size) {
    labels = [ ' bytes', 'KB', 'MB', 'GB', 'TB' ]
    label = labels.find {
        if( size < 1024 ) {
            true
        } else {
            size /= 1024
            false
        }
    }
    "${new java.text.DecimalFormat('0.##').format( size )}$label"
}

After pressing "Run Script" button you will see output below console. You can of course substitute println to SLF4J methods, here it's just for brevity.