sql to identify users that count against the license count

http://confluence.atlassian.com/display/CONF35/How+Do+I+Identify+Inactive+Users+in+Confluence

"If you want to disable inactive users and prevent them from being counted by Confluence license count it is possible to find out by running queries against your database."

Is there a column that defines whether a user is in the "disable" state? I see the active=T/F, but this doc seems to indicate that there has to be a state=disable in order to not count against the license. Thanks for any pointers as I'd like to know the sql query to help discern the difference in the user state.

1 answer

1 accepted

This widget could not be displayed.

Update: There is also a good way in getting the same information through Confluence User Macro as discussed here: https://answers.atlassian.com/questions/36898/how-to-query-active-users-in-confluence

I just answered this question at https://answers.atlassian.com/questions/40858/sql-query-to-show-all-users-with-use-confluence-permission . I'll just repost it here. Since you seem to be running on Confluence 3.5.x or higher, here is the query you are after:

SELECT DISTINCT
    (users.lower_user_name)
FROM
    cwd_user users
JOIN
    cwd_directory userdir
ON
    users.directory_id=userdir.id
WHERE
    users.lower_user_name IN
                          (
                          SELECT DISTINCT
                              lower(permusername)
                          FROM
                              SPACEPERMISSIONS
                          WHERE
                              permtype='USECONFLUENCE')
OR  users.lower_user_name IN
    (
        SELECT
            u.lower_user_name
        FROM
            cwd_user u
        JOIN
            cwd_directory d
        ON
            u.directory_id = d.id
        JOIN
            cwd_membership m
        ON
            u.id = m.child_user_id
        JOIN
            cwd_group g
        ON
            g.id = parent_id
        JOIN
            SPACEPERMISSIONS sp
        ON
            g.lower_group_name =lower(sp.permgroupname)
        WHERE
            permtype='USECONFLUENCE' )
AND users.active='T'
AND userdir.active='T'
ORDER BY
    1;

thanks, but that fails:

wiki-# ORDER BY
wiki-# 1;
ERROR: syntax error at or near "."
LINE 2: (user.lower_user_name)
^

looks like the table alias can't be "user." I changed it to users:

SELECT DISTINCT
(users.lower_user_name)
FROM
cwd_user users
JOIN
cwd_directory userdir
ON
users.directory_id=userdir.id
WHERE
users.lower_user_name IN
(
SELECT DISTINCT
lower(permusername)
FROM
SPACEPERMISSIONS
WHERE
permtype='USECONFLUENCE')
OR users.lower_user_name IN
(
SELECT
u.lower_user_name
FROM
cwd_user u
JOIN
cwd_directory d
ON
u.directory_id = d.id
JOIN
cwd_membership m
ON
u.id = m.child_user_id
JOIN
cwd_group g
ON
g.id = parent_id
JOIN
SPACEPERMISSIONS sp
ON
g.lower_group_name =lower(sp.permgroupname)
WHERE
permtype='USECONFLUENCE' )
AND users.active='T'
AND userdir.active='T'
ORDER BY
1;


Good catch! Seems 'users' is a SQL reserved word. Which apprently went just fine in my quick test in MySQL :/. I have fixed the query as per your suggestion. Thanks :)

Suggest an answer

Log in or Sign up to answer
Community showcase
Posted Monday in Confluence

Why start from scratch? Introducing four new templates for Confluence Cloud

Hi my Community friends!  For those who don't know me, I'm a product marketer on the Confluence Cloud team - nice to meet you! For those of you who do, you know that I've been all up in your Co...

409 views 4 6
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