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

[Daniel Eads {unmonitored account} · Accepted solution]

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.

[Chris Solgat]

@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 {unmonitored account}]

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.

[Chander Inguva]

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

[Siddheshwar mhetre]

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.

[Matt Tracy]

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.