sql to identify users that count against the license count

[Ray Stell]

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.

[HuseinA · Accepted solution]

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;

[Ray Stell]

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;

[HuseinA]

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 :)