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

1. Last updated date
2. Space creation date
3. Size of the space

[Engin Tekin]

https://community.atlassian.com/t5/Confluence-Cloud-Admins-articles/See-and-learn-about-all-of-your-spaces-with-the-new-Space/ba-p/2703423

[Srinatha T]

@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 T 

[Mohammed Siyad]

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

[Mohammed Siyad]

Hi @Srinatha T

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?

[Srinatha T]

@Mohammed Siyad which database you are using? Can you check with your admin team and ask them to run this query against confluence DB?

Thanks,

Srinath T

[Srinatha T]

@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]

Hi @Srinatha T

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!!

[Srinatha T]

Hi @Mohammed Siyad ,

I have tested the query in my test environment and works fine for me. Not sure why it did not work for you. 

Since you have other requirements now. You might want to go for some third party plugin's which will fetch the details for you. Like Example:

• Advanced-spaces-list-statistics 

Thanks,

Srinath T

[Srinatha T]

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  

[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.

[sam.girish]

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

1. Last updated date
2. Space creation date
3. Owners

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

Thanks
Sam