![www.MrAddon.com ®: Jira & Confluence Administration Support Blog & Ethereum Dev Blog – [www.MrAddon.blog] ®](https://jirasupport.files.wordpress.com/2022/09/cropped-logo-mr-addon-vertical-1.png){kind=logo}
MySQL query to get the last login of all members of the confluence-users group
SELECT distinct u.lower_user_name, l.successdate
FROM cwd_user u
LEFT JOIN logininfo l ON u.user_name = l.username
JOIN cwd_membership m ON u.id = m.child_user_id
JOIN cwd_group g ON m.parent_id = g.id
JOIN spacepermissions sp ON g.group_name = sp.permgroupname
WHERE permtype='USECONFLUENCE' order by u.lower_user_name;
CONTENT CREATED PER DAY
select contenttype, min(number_of_changes), max(number_of_changes), avg(number_of_changes)
from (
select contenttype, date_trunc('day', creationdate) , count(*) as number_of_changes
from content
where content.creationdate > date '2007-01-01' and version = 1
group by contenttype, date_trunc('day', creationdate)
) as dates
group by contenttype- Example result:
| contenttype | min | max | avg |
|---|---|---|---|
| DRAFT | 4 | 6 | 5 |
| 1 | 1 | 1 | |
| COMMENT | 1 | 54 | 20 |
| USERINFO | 1 | 45 | 15 |
| SPACEDESCRIPTION | 1 | 3 | 1 |
| PAGE | 1 | 119 | 21 |
| BLOGPOST | 1 | 64 | 5 |
CONTENT EDITED PER DAY
select contenttype, min(number_of_changes), max(number_of_changes), avg(number_of_changes)
from (
select contenttype, date_trunc('day', lastmoddate) as changedate, count(*) as number_of_changes
from content
where content.creationdate > date '2007-01-01'
group by contenttype, date_trunc('day', lastmoddate)
) as dates
group by contenttype- Example result:
contenttype min max avg BLOGPOST 1 718 14 COMMENT 1 73 23 DRAFT 4 6 5 MAIL 1 1 1 PAGE 1 4658 130 SPACEDESCRIPTION 1 4 1 USERINFO 1 48 16
NUMBER OF EXISTING PAGES
select contenttype, count(*) from content group by content.contenttype- Example result:
contenttype count MAIL 7914 COMMENT 12983 SPACEDESCRIPTION 232 DRAFT 10 PAGE 81465 USERINFO 13782 BLOGPOST 3308
NUMBER OF LINKS PER PAGE
select http, max(linkcount), min(linkcount), avg(linkcount), stddev_pop(linkcount), stddev_samp(linkcount), var_pop(linkcount), var_samp(linkcount)
from
(
select contentid, (links.destspacekey = 'http') as http, count(*) as linkcount
from links group by contentid, (links.destspacekey = 'http')
) as links_per_page
group by http- Example result:
http max min avg stddev_pop stddev_samp var_pop var_samp false 1994 1 5 32 32 1069 1069 true 189 1 2 6 6 40 40
select max(blength), min(blength), avg(blength), stddev(blength), variance(blength)
from (select length(body) as blength from bodycontent) as bodylengths
where blength > 0- Example result:
max min avg stddev variance 488707 1 2826 8858 78477292
(Note this query takes a long time to execute.)
NUMBER OF CHARACTERS PER PAGE BODY
select max(blength), min(blength), avg(blength), stddev(blength), variance(blength)
from (select length(bodycontent.body) as blength
from bodycontent, content
where bodycontent.contentid = content.contentid and contenttype='PAGE'
) as bodylengths
where blength > 0- Example result:
max min avg stddev variance 488707 1 3333 9884 97700121
ATTACHMENTS
select count(*), max(filesize), min(filesize), avg(filesize), stddev(filesize), sum(filesize) from attachments;- Example result:
count max min avg stddev sum 16082 107431588 0 207641 2447904 3339287543
ATTACHMENTS PER PAGE
List the stats for attachments per page, only for those pages that actually have attachments.
select count(*) as pages_with_attachments, avg(attachments_per_page), max(attachments_per_page), min(attachments_per_page), stddev(attachments_per_page) from
( select count(*) as attachments_per_page from attachments group by attachments.pageid ) as app- Example result:
pages_with_attachments avg max min stddev 4197 3 231 1 10
CONFIGURATION / PLUGIN DATA STORED IN BANDANA
- Just the global context
select count(*), sum(length(bandanavalue)) from bandana where bandanacontext = '_GLOBAL'
- Example result:
count sum 84 47729
- All of the information
select count(*), sum(length(bandanavalue)) from bandana
- Example result:
count sum 665 153094
COUNT OF COMMENTS PER PAGE IN A GIVEN SPACE:
SELECT c.title, (
SELECT count(contentid)
FROM content
WHERE contenttype = 'COMMENT'
AND pageid = c.contentid
) AS numComments
FROM content c
LEFT JOIN spaces s on c.spaceid = s.spaceid
WHERE c.contenttype = 'PAGE'
AND s.spacename = 'Demonstration Space'
GROUP BY c.title, c.contentid
ORDER BY c.title ASCCOUNT OF EDITS PER PAGE IN A GIVEN SPACE:
SELECT c.title, max(c.version) as NumEdits
FROM content c
LEFT JOIN spaces s on c.spaceid = s.spaceid
WHERE c.contenttype = 'PAGE'
AND s.spacename = 'Demonstration Space'
GROUP BY s.spacename, c.title
ORDER BY c.title ASCPAGES THAT HAVE BEEN EDITED:
SELECT c.title, max(c.version) as NumEdits
FROM content c
LEFT JOIN spaces s on c.spaceid = s.spaceid
WHERE c.contenttype = 'PAGE'
AND s.spacename = 'Demonstration Space'
GROUP BY s.spacename, c.title
HAVING max(c.version) > 1
ORDER BY c.title ASC
www.MrAddon.com ®: Jira & Confluence Administration Support Blog & Ethereum Dev Blog - [www.MrAddon.blog] ® 