Atlassian Confluence - Collecting Usage Metrics

Below are the scripts I used to collect usage stats from Confluence 3.5.x

-- Average Number Of Edits
select contenttype, min(number_of_changes), max(number_of_changes), avg(number_of_changes)
from (
    select contenttype, cast(lastmoddate As Date) as changedate, count(*) as number_of_changes
    from content
    where content.creationdate > DATEADD(year,-2,getDate()) and version=1
    group by contenttype, cast(lastmoddate As Date)       
     ) as dates
group by contenttype
-- Number of users
select lastmodifier,count(*) from content where lastmoddate>DATEADD(year,-2,getDate())
group by lastmodifier having count(*)>10 order by 2 desc

-- New pages create in X years
select contenttype, count(*) from content where lastmoddate>DATEADD(year,-2,getDate()) AND prevver is NULL group by content.contenttype

-- Total number of pages
SELECT COUNT(*) FROM content WHERE contenttype='PAGE' AND 
prevver is NULL;
-- Links per page
select https, max(linkcount), min(linkcount), avg(linkcount), stdevp(linkcount), stdev(linkcount), varp(linkcount), var(linkcount)
from
(
    select contentid, 
    case 
    when links.destspacekey='http' then 1 
    when links.destspacekey!='http' then 0
    end as https, count(*) as linkcount
    from links group by contentid, 
    case 
    when links.destspacekey='http' then 1 
    when links.destspacekey!='http' then 0
    end
) as links_per_page
group by https

-- Attachment Size

select count(*), max(filesize), min(filesize), avg(filesize), stdev(filesize), sum(filesize) from attachments
--Users (based on your model)
select count(*) from cwd_user where active='T'
select entity_name, entity_key, date_val from os_propertyentry where entity_key like '%2014%' and date_val < (some date value);

select u.name from external_entities u where u.name not in (select creator from content where contenttype in ('BLOGPOST', 'COMMENT', 'PAGE') and year(creationdate) > 2010);

select distinct entity_name from os_propertyentry where 
entity_key like '%user.last.login.date%' 
and date_val > DATEADD(year,-1,getDate())

--entity_name like '%username%'

select distinct entity_name from os_propertyentry

select distinct entity_key from os_propertyentry  where entity_key like '%user.%'

select distinct entity_name from os_propertyentry where 
entity_key like '%user.last.login.date%' 
and date_val > DATEADD(month,-6,getDate()) and date_val <DATEADD(month,-1,getDate())

--Last login in last 300 days
select distinct entity_name from os_propertyentry where 
entity_key like '%user.last.login.date%' 
and date_val > DATEADD(day,-300,getDate()) 
-- Previous login in last 300 days
select distinct entity_name from os_propertyentry where 
entity_key like '%user.previous.login.date%' 
and date_val > DATEADD(day,-300,getDate()) 

-- Not so frequent users
select ll.entity_name,DATEDIFF(day,pl.date_val,ll.date_val),ll.date_val,pl.date_val
from ( select entity_name,date_val from os_propertyentry where 
entity_key like '%user.last.login.date%' 
--and date_val > DATEADD(day,-300,getDate()) 
) as ll inner join
(select entity_name,date_val from os_propertyentry where 
entity_key like '%user.previous.login.date%' 
--and date_val > DATEADD(day,-300,getDate()) 
) as pl 
on ll.entity_name=pl.entity_name
where DATEDIFF(day,pl.date_val,ll.date_val)>60
--and ll.date_val<DATEADD(month,-12,getDate())
order by 2 desc

-- last login grouped per day
select cast(date_val As Date), count(*) from os_propertyentry where 
entity_key like '%user.last.login.date%' 
group by cast(date_val As Date)
order by 1 desc

-- Avg
select max(logincnt) from (select cast(date_val As Date) as dd, count(*) logincnt 
from os_propertyentry where 
entity_key like '%user.last.login.date%'
and date_val > DATEADD(year,-1,getDate())  
group by cast(date_val As Date)) as cnts

select entity_name,cast(date_val As Date) as dd, count(*) logincnt 
from os_propertyentry where 
entity_key like '%user.last.login.date%'
and date_val > DATEADD(year,-1,getDate())   
group by cast(date_val As Date), entity_name
order by dd desc


select top 100 entity_name, entity_key, date_val from os_propertyentry order by date_val desc

select  u.username, p.date_val from os_user u
join OS_PROPERTYENTRY p on u.id = p.entity_ID
where entity_key='confluence.user.last.login.date'
order by date_val desc;

select u.name, p.date_val from external_entities u
join OS_PROPERTYENTRY p on u.id = p.entity_ID
where entity_key='confluence.user.last.login.date'

order by date_val desc;

References:

https://confluence.atlassian.com/pages/viewpage.action?pageId=154239308  

https://answers.atlassian.com/questions/254860/defining-confluence-usage-metrics

https://confluence.atlassian.com/display/CONFKB/User+base+audit+queries