Number of pages created per user in Confluence

[Armon Tiongco]

Hello,

Is it possible to determine the total number of pages a user has created in Confluence for a particular space?

Thanks,

Armon

[Bob Swift]

There are SQL queries that can be modified to do this. statistics.txt from Site Statistics or some of the other information. For you specific request, look for this query:

select concat('[~', creator, ']') as "User", count(distinct CONTENT.title) as "Pages created" 
    from CONTENT 
    where creator IS NOT NULL 
    group by creator 
    order by "Pages created" desc

[Martin James]

This is exactly what I needed. Is there a way of being able to show the actual username or name of the person?

[Chhaya Gadade]

Where should we run this SQL query.

[Alexandr Marchenko]

Alternative way for confluence cloud with powershell:

$username = 'username@contoso.com'
$password = 'P@ssword'
$hostname = 'https://contoso.atlassian.net/wiki'

$headers = @{
  'Accept' = 'application/json'
  'Content-Type' = 'application/json'
  'Authorization' = ("Basic {0}" -f ([Convert]::ToBase64String([Text.Encoding]::ASCII.GetBytes(("{0}:{1}" -f $username, $password)))))
}

$users = @()
$res = @{
  _links = @{
    next = '/rest/api/content?expand=version.by&limit=200'
  }
}

while ($res._links.next) {
  $res = Invoke-RestMethod -Uri ($hostname + $res._links.next) -Headers $headers
  foreach ($x in $res.results) {
    $users += New-Object PSObject -Property @{
      user = $x.version.by.publicName
    }
  }
}

$users | Group-Object user | Sort-Object count -Descending | select count, name

Will give you number of posts by user

[Chase Seibert]

If you only have API access, I wrote some code here to tabulate pages and edits by user.

https://github.com/chase-seibert/confluence-stats

[Martrice Denson]

I am trying to create a page that a user can come to and see only the pages that they have created. It looks like this script is close to what I need. Can you advise me on how I would add that script to a page. I have a template that the sales team will be creating pages, if they need to edit there page I want to create a page that will only show them conten that they have created. Would what you have above suit my needs?

[Matthew J. Horn]

You can write a user macro to do this. I think the easiest way to approach the algorithm would be to iterate over each page and increment each user's count when you find a match for creator. User macros are great for this sort of one-off or highly configurable task.

If you've never written a user macro, start here:

https://confluence.atlassian.com/display/DOC/Writing+User+Macros

If we're talking 10s of thousands of pages, though, you'll want to use the SQL plugin and do a database query. Search the plugin exchange for that.

hth,

matt

[Armon Tiongco]

Hello Matt,

Thank you for always taking your time to answer my question.

Yes - i was able to create simple user macros but nothing complicated yet.

Would you happen to have samples which covers the same idea?

Thanks,

Armon

[Martin James]

That depends.

If using a database such as MySQL, you could use a tool such as MySQL Workbench to pull stats from the database. 

This method won't work if using Confluence Cloud.