SQL Queries for Confluence usage stats

[Jim Birch]

Does anyone have any usage stas sql queries for Confluence they'd like to share? I'm interested in number of updates and new pagees by date, most prolific updaters, etc.

We currently run the Usage plugin but it is possibly implicated in some brownouts...

[Bob Swift OSS (Bob Swift Atlassian Apps) · Accepted solution]

Site Statistics

[Jim Birch]

Thanks Bob.

[Vijay Sv]

Here it is...........

select 'Total Spaces', count(*) from SPACES -----Total Spaces
select 'Personal Spaces', count(*) from SPACES where [SPACETYPE] ='personal' ---Personal Spaces
select 'Site Spaces',count(*) from SPACES where [SPACETYPE] = 'global' -- Site/global Spaces
SELECT 'Content (All Versions)', count(*) FROM [CONTENT] ---- Content (All Versions)
select 'Content (Current Versions)', count(*) from CONTENT where PREVVER is null; --- Content (Current Versions)
select count(*) from [dbo].[cwd_group] ---Local Groups
select count(*) from [dbo].[cwd_user] ----Local Users
SELECT 'Blogposts',count(*) FROM [CONTENT] WHERE CONTENTTYPE = 'BLOGPOST';
select 'All Pages',count(*) from CONTENT where CONTENTTYPE ='PAGE';
SELECT 'Comments',count(*) FROM CONTENT WHERE CONTENTTYPE = 'COMMENT';
SELECT 'All Content Attachments', count(*) FROM [dbo].[CONTENT] WHERE CONTENTTYPE = 'ATTACHMENT'; --All Content Attachments

Thanks,
VJ