Need a SQL statement to find a list of all spaces not updated in X amount of time and the corresponding space administrators

 

3 answers

1 accepted

0 vote
Daniel Eads Community Champion Jan 06, 2016

Hi Matt,

This should get what you need - it grabs the administrators for all spaces and then subtracts any spaces that have updates SINCE the date you set. The result is a list of spaces with administrators that haven't been updated since the date in the query. Be sure to set the correct date on line 12. The format is dd-MMM-yy.

SELECT (c.SPACEID), s.SPACENAME, u.USERNAME
FROM CONTENT c
JOIN SPACES s on s.SPACEID = c.SPACEID
JOIN SPACEPERMISSIONS p ON s.SPACEID = p.SPACEID
JOIN user_mapping u ON p.PERMUSERNAME = u.user_key
WHERE s.SPACETYPE = 'global'
MINUS SELECT (c.SPACEID), s.SPACENAME, u.USERNAME
FROM CONTENT c
JOIN SPACES s on s.SPACEID = c.SPACEID
JOIN SPACEPERMISSIONS p ON s.SPACEID = p.SPACEID
JOIN user_mapping u ON p.PERMUSERNAME = u.user_key
where c.LASTMODDATE > '01-JAN-16'
ORDER BY SPACENAME

** Note that this won't show you spaces where the content is old but no users are listed as administrators. It ignores groups!

 

Alternatively, check out the Archiving Plugin for Confluence which will help you find and do things with old pages.

@Daniel Eads

That query was great help!  I had to modify it slightly to be compatible with MS SQL.  Also the table/column names are case sensitive so I had to fix a couple of those as well.  

 

I'm posting my query in case someone comes looking for MSSQL version of the query.  Also, just in case it helps someone else, that is the actual date format that I had to use to get the correct results.

SELECT (c.SPACEID), s.SPACENAME, u.username
FROM CONTENT c
JOIN SPACES s on s.SPACEID = c.SPACEID
JOIN SPACEPERMISSIONS p ON s.SPACEID = p.SPACEID
JOIN user_mapping u ON p.PERMUSERNAME = u.user_key
WHERE s.SPACETYPE = 'global'
EXCEPT SELECT (c.SPACEID), s.SPACENAME, u.username
FROM CONTENT c
JOIN SPACES s on s.SPACEID = c.SPACEID
JOIN SPACEPERMISSIONS p ON s.SPACEID = p.SPACEID
JOIN user_mapping u ON p.PERMUSERNAME = u.user_key
where c.LASTMODDATE > '2015-01-01 00:00:00.00'
ORDER BY SPACENAME
Daniel Eads Community Champion Jan 06, 2016

Good note! I'm on Oracle which for the most part is PostgreSQL/MySQL query compatible (Atlassian recommended DBs). MSSQL does have its quirks. Interesting that the date format used requires a time (even midnight) in MSSQL.

Hi Matt,

            I am very poor at SQL, but wanted to give a shot.

 

SELECT s.SPACENAME, u.username FROM SPACES s
 JOIN SPACEPERMISSIONS p ON s.SPACEID = p.SPACEID
 JOIN user_mapping u ON p.PERMUSERNAME = u.user_key
  WHERE (p.PERMTYPE = 'SETSPACEPERMISSIONS'
  • The above query  will give you a list of all Spaces with Space Administrators.

 

(SELECT distinct(SPACEID) FROM CONTENT where LASTMODDATE < '2015-01-01' AND SPACEID IN (SELECT SPACEID FROM SPACES WHERE SPACETYPE = 'global')));
  • The above query should  give you all Global Spaces where its Content has not been modified after Jan 1st 2015. 

 

I am more interested to see how we get this query using above two queries.

 

Regards

Chander Inguva

 

Hello Matt,

 I have created SQL query to find a list of all spaces not updated from X date.

Please try below query,

select spacename from spaces where lastmoddate < '<DATE>'

Also for finding space and space administrator, who not updated from X date, try below query

select spacename, creator from spaces where lastmoddate < '<DATE>'

Note: The creator's name will be displayed in decoding format.

Hello, Thank you for the response; however, I need a list of spaces not updated in X amount of time, with the corresponding space administrators. I am not interested in who created the space.

Suggest an answer

Log in or Sign up to answer
How to earn badges on the Atlassian Community

How to earn badges on the Atlassian Community

Badges are a great way to show off community activity, whether you’re a newbie or a Champion.

Learn more
Community showcase
Posted Jul 10, 2018 in Confluence

We want to see the templates you've created in Confluence!

Hi Community, Jessica here from the Confluence Product Marketing team!  July’s community challenge is all about sharing pictures  — and as an extension of our first post on what ...

870 views 23 12
Join discussion

Atlassian User Groups

Connect with like-minded Atlassian users at free events near you!

Find a group

Connect with like-minded Atlassian users at free events near you!

Find my local user group

Unfortunately there are no AUG chapters near you at the moment.

Start an AUG

You're one step closer to meeting fellow Atlassian users at your local meet up. Learn more about AUGs

Groups near you