How to get these Statistics from confluence using SQL plugin

[Atos Origin]

Hello,

We are in the process of generating report where we need these final requirements fulfilled:

• Would it be possible to have also the total number of Comments per Space?
• Would it be also possible to have the total number of Labels used per Space?
• Same for the Likes (but I am not sure that this one really exists – it is linked to the Like feature available on each page)?
• The report we would need has to help us in identifying which Wiki spaces are still in use in terms of contribution since the 1^st of February this year, and which ones have not got any contribution before that date. An optional additional requirement would be to make the solution flexible enough in order to be able to get the same report but changing the date to consider (from 1^st of February to 1^st of January, as an example).

Thank you in advance for any help possible on forming these queries. (We are using Oracle 11g)

Regards,

Navin Vijaykumar

[Mirko Skramusky · Accepted solution]

Hi Navin!

Some thoughts about the Confluence data schema, because I currently don't know the Oracle syntax:

• for comments query table "content" by content type "COMMENT" and content status "current", and finally group by space
• for labels use the "content_label" table and join the "content" table to group by space
• for likes use the "like" table and join the "content" table to group by space
• for contributions also query the table "content" and filter by modification date and content status "current"

Hope this helps.

[Atos Origin]

Hello Mirko,

Thanks a ton for your reply. It worked for me as you suggested.

I am pasting my SQL for others who might need this.

Total number of Labels used per Space

SELECT T3.SPACENAME, COUNT(T1.CONTENTID) as "Number of Labels"
FROM CONTENT_LABEL T1
JOIN CONTENT T2 ON T1.CONTENTID = T2.CONTENTID
JOIN SPACES T3 ON T2.SPACEID = T3.SPACEID
WHERE T1.LABELABLEID IS NOT NULL
GROUP BY T3.SPACENAME

Total number of Labels used per Space

SELECT T3.SPACENAME, COUNT(T1.CONTENTID) as "Number of Likes"
FROM LIKES T1
JOIN CONTENT T2 ON T1.CONTENTID = T2.CONTENTID
JOIN SPACES T3 ON T2.SPACEID = T3.SPACEID
GROUP BY T3.SPACENAME

Wiki spaces are still in use in terms of contribution

SELECT T2.SPACENAME, count(CONTENTID) as "Number of Contributions"
FROM CONTENT T1
JOIN SPACES T2 ON T1.SPACEID = T2.SPACEID
WHERE T1.LASTMODDATE > '2014-02-01 00:00:00' AND T1.CONTENT_STATUS = 'current'
GROUP BY T2.SPACENAME

Regards,

Navin Vijaykumar

[Pat Condon]

Navin,

Could you please provide details on T3,T2 and T1?  I doubt they are literal but have no idea what to substitute and am not SQL person

Thanks for your help

[Pat Condon]

With the connection working, the queries work as is except the "Wiki spaces are still in use in terms of contribution" with "Error rendering macro 'sql-query' : java.sql.SQLDataException: ORA-01861: literal does not match format string" error

I have tried a few versions on line four without luck

Any suggestions would be appreciated