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

Mohammed Siyad November 1, 2022

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

3 answers

0 votes
Srinatha T
Atlassian Team
Atlassian Team members are employees working across the company in a wide variety of roles.
November 1, 2022

@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 November 2, 2022

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

Mohammed Siyad November 6, 2022

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

Mohammed Siyad November 23, 2022

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?

extranet.PNG

Srinatha T
Atlassian Team
Atlassian Team members are employees working across the company in a wide variety of roles.
December 5, 2022

@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

0 votes
Srinatha T
Atlassian Team
Atlassian Team members are employees working across the company in a wide variety of roles.
November 1, 2022

@Mohammed Siyad ,

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

I hope the info helps. have a good day

Thanks,

Srinath T

Mohammed Siyad December 5, 2022

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
Atlassian Team
Atlassian Team members are employees working across the company in a wide variety of roles.
December 5, 2022

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:

Thanks,

Srinath T

0 votes
Srinatha T
Atlassian Team
Atlassian Team members are employees working across the company in a wide variety of roles.
November 1, 2022

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 November 1, 2022

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 December 4, 2022

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

Suggest an answer

Log in or Sign up to answer
TAGS
AUG Leaders

Atlassian Community Events