Create a report of all confluence space with the below details using scriptrunner * Last updated date * Space creation date * Size of the space

Hi @Srinatha Tondihal Thanks for the reply, can you clarify couple of questions too. * From the above query we are able to fetch:- space key, space name, creation date, last modified date, attachments size per space (which is like not accurate). * Can we also get the number of pages per space, number of active users per space and last viewed date of the the space? Appreciate your help in here!!

nav[aria-label="Primary Navigation"] { padding: 0; & ul { list-style: none; width: 100%; display: flex; flex-direction: row; justify-content: start; align-items: start; gap: 30px; padding: 0; & li { margin: 0; } & ul li { list-style: none; } } }

Is there a way to generate a report for all spaces in confluence with there activity status?

Mohammed Siyad

Create a report of all confluence space with the below details using scriptrunner

Last updated date
Space creation date
Size of the space

Accepted answers

All comments

Srinatha Tondihal

Hi @Mohammed Siyad ,

Welcome to Atlassian community.

I am not sure about script runner as i have never used it. But I think we can get the info from the dabase by writing a SQL query. If you are interested then i can write one for you. It should be achieve-able.

Have a good day!

Thanks,

Srinath T

Srinatha Tondihal

@Mohammed Siyad ,

I just went through script runner docs and I think below doc has the info you are looking for.

Space statistics - Script runner

I hope the info helps. have a good day

Thanks,

Srinath T

Mohammed Siyad

Probably SQL query would work fine, as I can get the data all fetched out by puting SQL in script and return as a report.

Srinatha Tondihal

@Mohammed Siyad ,

I just wrote one and seems like it does the job. You can always customise as per your need.

SELECT t1.spacename, t1.spacekey, t1.creationdate, t1."Total Attachments Size", t1."Total Number of Attachments", t2."Pages Size", t1."Last Modified Date"
FROM (
SELECT s.spacekey, s.creationdate, (ROUND(SUM(longval)/1048576,2) || 'MB' )
AS "Total Attachments Size", COUNT(*)
AS "Total Number of Attachments", s.spacename,
MAX(c.lastmoddate) AS "Last Modified Date"
FROM contentproperties cp
JOIN content c
ON cp.contentid = c.contentid
JOIN spaces s
ON s.spaceid = c.spaceid
WHERE propertyname = 'FILESIZE'
GROUP BY s.spacekey, s.spacename, s.creationdate
) t1
JOIN (
SELECT s.spacekey, (ROUND(SUM(char_length(bc.body))/1048576,2) || 'MB')
AS "Pages Size"
FROM content c
JOIN bodycontent bc
ON c.contentid = bc.contentid
JOIN spaces s
ON s.spaceid = c.spaceid
GROUP BY s.spacekey
) t2
ON t1.spacekey = t2.spacekey;

Thanks,

Srinath T

Mohammed Siyad

Thanks Srinath, but seems like it is not fetching the result and throwing up error. @Srinatha Tondihal

Mohammed Siyad

@Srinatha Tondihal Could you please help me with the queries. Seems like it throws up error.

Mohammed Siyad

Hi @Srinatha Tondihal

Is it possible we can get the count of active users tagged to a space (extranet users) along with this data, then it would be fine. And also the query you have posted, just wanted to tell, that it doesnt retreive the spaces which has 0 attachment size. Can that be also setup?

sam.girish

Hi @Srinatha Tondihal , I would like to get the SQL query to get the list of all confluence spaces with the following details:

Last updated date
Space creation date
Owners

Also, if you could point me to where I can run this SQL query would also be greatly appreciated.

Thanks
Sam

Mohammed Siyad

Hi @Srinatha Tondihal

Thanks for the reply, can you clarify couple of questions too.

From the above query we are able to fetch:- space key, space name, creation date, last modified date, attachments size per space (which is like not accurate).
Can we also get the number of pages per space, number of active users per space and last viewed date of the the space?