Custom scripts for Confluence health-check: find out how effective your tool is

In terms of periodical Confluence performance audit, we also check how effective the tool and licensing are. 

How to boost the speed of Confluence and processes in it without additional investing in servers? The first thing you need to do is to evaluate the current performance. We’re glad to share the scripts to check various criteria that help to make decisions about the system optimization. 

The scripts work with the PostgreSQL, as it’s the most popular database for Confluence. They can be easily adapted to other databases like Oracle, MySQL, MS SQL. Let us know if you need some help. 

7 ready-to-go scripts for Confluence:

25 biggest attachments 

We search for the biggest attachments and analyze what they are, and if there’s a reason to keep them.

SELECT DISTINCT c.title AS attachmentTitle, u.username AS uploadedBy, s.spacekey, co.title AS pageTitle, cn.longval/1024/1024 as mbytes
FROM CONTENT AS c
JOIN USER_MAPPING AS u
ON u.user_key = c.creator
JOIN CONTENT AS co
ON c.pageid = co.contentid
JOIN CONTENTPROPERTIES AS cn
ON cn.contentid = c.contentid
JOIN SPACES AS s
ON c.spaceid = s.spaceid
WHERE c.contenttype = 'ATTACHMENT'
AND cn.longval IS NOT NULL
ORDER BY mbytes DESC LIMIT 25;

Inactive users

We check inactive users to delete them if needed.

SELECT cu.user_name, cu.first_name, cu.last_name, cu.created_date, cu.updated_date, cd.directory_name
FROM cwd_user cu
JOIN cwd_directory cd ON cu.directory_id = cd.id
WHERE cu.active = 'F';

Users with old last login data

We check the users who didn’t log-in in Confluence lately, and decide whether you need to spend licenses on them. 

SELECT cu.user_name, cu.first_name, cu.last_name, cu.email_address,
       li.successdate, li.PREVsuccessdate
FROM logininfo li
JOIN user_mapping um ON um.user_key = li.username
JOIN cwd_user cu ON um.username = cu.user_name
JOIN cwd_directory cd ON cu.directory_id = cd.id
where li.successdate < '2019-10-01'
ORDER BY successdate;

Spaces with large volume of attachments

We search for spaces with the largest volume of attachments, analyze and optimize them.

select
    s.spacekey,
    sum(LONGVAL/1024/1024)
FROM
    contentproperties cp
JOIN content c
ON
    cp.contentid = c.contentid
JOIN spaces s
ON
    s.spaceid = c.spaceid
WHERE
    c.contenttype = 'ATTACHMENT'
AND
    cp.propertyname = 'FILESIZE'
GROUP BY
    s.spacekey
oRDER BY sum(LONGVAL) desc
;

Largest pages

Analyzing the largest pages and optimizing them.

SELECT s.spacekey, c.title, LENGTH(bc.body)
FROM BODYCONTENT bc
JOIN CONTENT c ON bc.contentid = c.contentid
JOIN SPACES s ON c.spaceid = s.spaceid
WHERE c.prevver IS NULL AND c.contenttype IN ('BLOGPOST','PAGE')
ORDER BY LENGTH(bc.body) DESC LIMIT 25;

Pages with the biggest sum of version sizes

Searching for pages with the biggest sum of version sizes. Sometimes there can be dozens and hundreds of thousands versions. Let’s get rid of unnecessary ones.

SELECT s.spacekey, c.title, subq.sum_size_in_bytes / 1024/1024
FROM CONTENT c
JOIN SPACES s ON c.spaceid = s.spaceid
JOIN (
    SELECT c2.prevver, SUM(LENGTH(bc.body)) AS sum_size_in_bytes
    FROM BODYCONTENT bc
    JOIN CONTENT c2 ON bc.contentid = c2.contentid
    WHERE c2.prevver IS NOT NULL AND c2.contenttype IN ('BLOGPOST','PAGE')
    GROUP BY c2.prevver
    ORDER BY SUM(LENGTH(bc.body)) DESC LIMIT 25
) AS subq ON c.contentid = subq.prevver
ORDER BY subq.sum_size_in_bytes DESC;

All about calendars

Calendars in Confluence have complex and resource-consuming structure. We analyze what’s going on with them. Sometimes there can be hundreds of forgotten calendars. Let’s get rid of them.

SELECT um.lower_username as creator, tc."NAME" as calendar_name, to_timestamp(CAST(tc."CREATED" AS bigint)/1000) as creation_date
FROM "AO_950DC3_TC_SUBCALS" tc
JOIN user_mapping um ON um.user_key = tc."CREATOR"
WHERE tc."PARENT_ID" IS NULL
ORDER BY creator;

It’s just the tip of the iceberg, a small part of our expertise to get better performance, reliability and fail free Confluence.

Didn’t manage to find a solution? Feel free to schedule a free 30-min consultation, and discuss your project.