nav[aria-label="Primary Navigation"] { padding: 0; & ul { list-style: none; width: 100%; display: flex; flex-direction: row; justify-content: start; align-items: start; gap: 30px; padding: 0; & li { margin: 0; } & ul li { list-style: none; } } }

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

Accepted answers

Mirko Skramusky [Communardo]

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.

All comments

Mirko Skramusky [Communardo]

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 &gt; '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

Welcome!

It looks like you're new here. Sign in or register to get started.

Quick Links

Find more posts tagged with

confluence