How to get these Statistics from confluence using SQL plugin

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<sup>st</sup> 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<sup>st</sup> of February to 1<sup>st</sup> 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

2 answers

1 accepted

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.

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 &gt; '2014-02-01 00:00:00' AND T1.CONTENT_STATUS = 'current'
GROUP BY T2.SPACENAME

Regards,

Navin Vijaykumar



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

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

Suggest an answer

Log in or Sign up to answer
How to earn badges on the Atlassian Community

How to earn badges on the Atlassian Community

Badges are a great way to show off community activity, whether you’re a newbie or a Champion.

Learn more
Community showcase
Posted Monday in Confluence

Calling all marketing teams who use Confluence - we want to hear from you!

Hi Community! me again 🙂 If you’re a marketing team using Confluence, we want to hear your story! How did you start using Confluence? What are your use cases? What have been some of the benefits?...

151 views 3 3
Join discussion

Atlassian User Groups

Connect with like-minded Atlassian users at free events near you!

Find a group

Connect with like-minded Atlassian users at free events near you!

Find my local user group

Unfortunately there are no AUG chapters near you at the moment.

Start an AUG

You're one step closer to meeting fellow Atlassian users at your local meet up. Learn more about AUGs

Groups near you