Skip to end of metadata
Comment: Corrected links that should have been relative instead of absolute.
Go to start of metadata

Many folks are interested in site statistics, here are comments/wishlist ideas.

Madhusudan GR: I want to know if there is a macro or a portlet available that will give me the statistics of the page-visits in my space. This will give me the information i need on which are the pages that are widely used. Thanks in advance.

See Tracking Plugin

Tim Colson: I agree. I would like to report some items:

  • Total number of spaces
  • Total number of pages, comments, blogs per space
  • Number of page views per space
    • Ideally, would like to show views per page
  • Number of registered users
  • Number of currently active users
    • FYI: a servlet listener that hears session init/destroy evens can do this easily.
  • Number of users per day
  • Total page views for the entire site - by day, week, month.

FYI: I don't expect everything in version 1.3... but this is a wishlist . (smile)

Examples

Here are examples using SQL Plugin and Confluence Chart Plugin that give some of the static statistics requested above. Adjust the dataSource names according to your installation. You may need to adjust some of the SQL for your database.

  • Statistics.txt - works with mysql
  • Statistics.txt - works with PostgreSQL provided you add the following function to the Confluence database

Screen shots
Labels:
None
Edit Labels

35 Comments

Hide/Show Comments

  1. Nov 03, 2005

    Guy Fraser (Adaptavist)

    Oooh! I wonder if David Peterson can add these to his tracking macro?

  2. May 26, 2006

    Libor Tvrdík

    Hi, it's nice, but not function on PostgreSQL database.

    There is code for PostgreSQL 7.3.

    1. Oct 15, 2009

      Jim Severino [Atlassian]

      Postgres (possibly versions after 7.3) doesn't let you substring timestamp fields, so if you're using the code above you'll need to change lines that look like:

      to:

    2. Oct 29, 2009

      Jim Severino [Atlassian]

      Here's an updated and slightly different version of the script above (for Postgres > 7.3).

      Not the prettiest SQL ever produced, but it works.

    3. Jun 18, 2010

      michael regelin

      Hi everybody,

      I'm looking for the same script but for oracle DB. Is there a genius guy who could translate this script?

      Thanks again.

      Michael

      1. Jul 14, 2011

        Ken Carroll

        This has worked for some years for me

        1. Jul 14, 2011

          Ken Carroll

          With one recent problem on 3.5 - as the os_user and os_group tables are no longer used I've had to change this to users and groups. However, for some reason that doesn't work. It worked for years on many confluence versions but on 3.5.6 it fails by returning 0 users/groups - although it worked on 3.5.1.

          If anyone knows why this is the case I'd very much appreciate it.

  3. Jun 23, 2006

    Peter R.

    "Adjust the jndi names according to your installation."

     Newbie question, how do I do this? Do I just change the "ConfluenceDS" to the name of my Confluence DB?

    1. Jan 08, 2007

      Bob Swift

      jndi name refers to the datasource defined in your app server assuming you configured Confluence to use a datasource. If not, you will need to define a datasource in order to use the sql macro. Best to go to SQL Plugin and see configuration examples there. Also note that the jndi parameter is now called the datasource parameter (even though jndi also still works).

  4. Jun 24, 2006

    Peter R.

    Ok, now I've got a different error:

    sql: org.apache.tomcat.dbcp.dbcp.SQLNestedException: Cannot create JDBC driver of class '' for connect URL 'null'

    select contenttype as Type, count(distinct CONTENT.title) as "Count" from CONTENT group by contenttype having Count > 0
    union
    select contenttype as Type, count as "Count" from CONTENT where contenttype='COMMENT' group by contenttype having Count > 0
    union
    select "ATTACHMENTS" as Type, count(distinct title) as "Count" from ATTACHMENTS having Count > 0
    order by Count DESC

    1. Jan 08, 2007

      Bob Swift

      It is still your datasource configuration that is not correct. Check out some of the configuration related comments on the SQL Plugin.

      1. Sep 03, 2008

        dave007

        hi ,

           I would like to get the data from Microsoft Sql Server 2005 to wiki page to build metrics. I was able to get the data by writing sql queries. I have seen about this SQL Plugin does this support with sql server 2005 database.

  5. Nov 10, 2006

    Harvey Kandola

    And here is the code for SQL Server:

  6. Dec 15, 2006

    James Mortimer

    Is the schema for the Confluence database documented somewhere?

    1. Dec 17, 2006

      Agnes Ro

      Hi James,

      Unfortunately we don't have it documented due to the constant changes. If you have database access, you are probably better off getting a database schema from that (100% accuracy!) anyway.

      Agnes.

  7. Jul 11, 2007

    Kirk Mook

    My Users count is 1384, but that's just the people who are allowed to access, not the actual people who've logged in. How can I get that data from the SQL plugin? I know the number should be around 370 or so (from before we upgraded to the unlimited license that is what was shown).

    PS, I'm also not that familiar with SQL statements, currently testing this in MySQL test system.

    Thanks,

    Kirk

    1. Jul 11, 2007

      Peter R.

      Try this:

      That's a snippet I pulled from our "Confluence Queries" page which is maintained by our DBA. Heck, here's the whole page for whomever is interested:

      Types of wiki content

      Counts of object types (unique - not all versions)

      Counts of label data

      Max / Min counts

      Space Information

      Group Membership

      Wiki Contributors

      Get all Global Space Administrators

      Page Counts / Space

      List of All Personal Spaces

      SQL Script For Wiki Statistics Page

      1. Oct 30, 2007

        Andy Brook

        hey peter, just what I was looking for, great post (smile)

  8. Jul 11, 2007

    Kirk Mook

    Thanks Peter!

    What's the version=1 do in regards to a user count? I'm getting about 150 on my count.

    =- Kirk -=

    1. Jul 11, 2007

      Peter R.

      From my DBA:

      Short answer (in meeting): with our LDAP configuration, I was seeing an unrealistic number of people in our database until I constrained it ... YMMV. If there is a better way, I'd like to pursue.

      I should note that when we run it we get a little over 5200 users right now, about what we expect.

  9. Jul 02, 2009

    Stacy Shenker

    I hope someone can help me.. I am looking for something that will give me a list of all the spaces that have not had any activity within X time peried (i.e. 6 months)

    I think it can be done in an sql script but I am not a DBA. I was able to generate a list of spaces from the reporter plugin, but i cant' seem to figure out the parameters for what else I am looking for.

    thanks

  10. Jul 03, 2009

    Bob Swift

    A SQL query would do it. By no activity, I assume you mean no new pages or news, no updates, and no attachment changes? It requires looking at a few tables. I will do it for you if you can wait until I have computer access mid next week. Otherwise, look at the following tables: contents, attachments, spaces.

    1. Jul 03, 2009

      Stacy Shenker

      Thanks Bob, I can wait, no rush.  Thank you for the assistance.  And you are correct in your assumption of no activity.  We are trying to target spaces that have not had activity in 6 month or longer.

      1. Jul 08, 2009

        Bob Swift

        Try something like this (for postgresql):

        1. Jul 08, 2009

          Stacy Shenker

          Hey Bob..

          I have microsoft sql 2005 running.  when I put in your script I got the following error

                           Msg 195, Level 15, State 10, Line 3

                           'date_trunc' is not a recognized built-in function name.

          1. Jul 08, 2009

            Bob Swift

            Just remove that function - it is only cosmetic to make the date display nicer. MSSQL might have a similar function but its not necessary.

            1. Jul 09, 2009

              Stacy Shenker

              I removed the function and now the query looks like this:

              select
                s.spacename as "Space",
                date_trunc('hour', case when max(a.lastmoddate) > max(c.lastmoddate) then max(a.lastmoddate) else max(c.lastmoddate) end) as "Last modification date",
                date_trunc('hour', max(c.lastmoddate)) as "Last page or news change date",
                date_trunc('hour', max(a.lastmoddate)) as "Last attachment change date"
              from spaces as s
              inner join content as c on c.spaceid = s.spaceid
              left join attachments as a on a.pageid = c.contentid
              where c.contenttype in ('PAGE', 'BLOGPOST') and spacetype = 'global'
              group by s.spacename
              order by "Last modification date" asc

              I am not getting the following error:

              Msg 102, Level 15, State 1, Line 3

              Incorrect syntax near ','

              Thanks for you help:

              Stacy

              1. Jul 09, 2009

                Bob Swift

                Try this:

                1. Jul 09, 2009

                  Stacy Shenker

                  That worked Perfect!!!!!!

                  thanks so much

  11. Jan 06, 2010

    Stacy Shenker

    Bob,

    I don't know if you are still watching this page.  I just got back from maternity leave and need to use the above script.  I forget where I  am supposed to enter my date parameters.

    I would appreciate any help

    1. Jan 06, 2010

      Bob Swift

      I am (smile). Not sure what you mean by date parameters.

      1. Jan 07, 2010

        Stacy Shenker

        Hi Bob,

        I actually figured it out.  From some reason I thought I had to enter a date time from for the period I was looking for.  After re-running the script I got the info I was looking for. Thanks for responding.

  12. Apr 03, 2010

    Christian Veillette

    How would I limit the following just to a single space?

  13. May 10, 2010

    Mauricio Salles

    Hi!

    I'd like to find a feature/plugin/extension that lists the viewers users of a given page.

    I've look at this page , but didn't find any.

    Does anybody know how to deploy this into Confluence?

  14. Nov 23, 2011

    Damian Egli

    Hi all,

    thanks a lot for your efforts. I was curious about using that Ms SQL statistics. But everytime I go to the page including this script my confluence stalls. I have to restart confluence then.
    We run confluence on Win2008R2 Box using SQL Server 2008. I have 4 GB RAM and my start parameters are -Xms512m -Xmx768m -XX:MaxPermSize=512m?

    Where do I have to tweak?

    Thanks for any hints and regards,

    Damian