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
MAIL 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 ASC

COUNT 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 ASC

PAGES 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
Info extracted from Atlassian
Posted by:.

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s