Counting the number of JIRA and Confluence users

Unless you have one of the “unlimited” user licenses for Confluence & JIRA it’s important to keep a track on your user growth. The Atlassian applications don’t provide historic tracking of the user base growth so this is something which you need to introduce yourself.

JIRA

Within JIRA, seeing how many licenses are in use is relatively straightforward. By accessing the License Information Admin page (this is located at http://JIRA_ROOT/secure/admin/ViewLicense!default.jspa) at gives you the following information:

license info

So you can see here that we have 785 active users out of a limit of 2000, so nice an healthy.

Now I could gather this information on a weekly basis manually but that would be a PIA so how can I get the same information automatically?

Well there’s quite a simple database query for figuring out the number of active users:

SELECT COUNT(user_name) AS num_users
FROM
      cwd_user, cwd_membership
WHERE
      cwd_user.id = cwd_membership.child_id AND
      cwd_membership.membership_type='GROUP_USER' AND
      cwd_membership.parent_name='jira-users'

Confluence

So how about the same for Confluence?

Well within Confluence (4.3.x anyway) there’s no straightforward way through the GUI to identify the number of active users. The easiest way is therefore to go direct to the database.

In our case we’re using an MS-SQL database for our Confluence data storage so the query looks like:

SELECT count(DISTINCT cwd_user.lower_user_name) AS num_users
FROM 
     cwd_user INNER JOIN cwd_membership cwd_membership ON cwd_user.id = cwd_membership.child_user_id
     INNER JOIN cwd_group cwd_group ON cwd_membership.parent_id = cwd_group.id
     INNER JOIN spacepermissions spacepermissions ON cwd_group.group_name = spacepermissions.permgroupname
WHERE
      spacepermissions.permtype = 'USECONFLUENCE' AND cwd_user.active = 'T'

 

 

 

Advertisements

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s