Why total active users between sql base data and license different?

License view 500 (314 currently active)

SQL BD query 179

Users access group enter enter in  JIRA  = 362 of them unactive = 48

2 answers

0 vote

My guess is that the SQL query is not correct.

First, look in Global Permissions in JIRA - what group or groupS are named for "JIRA users" permission?  (Also note the two administrator lines, although the users in there are probably in the user groups already)

Then tell us what the SQL is you're running.

I take the sql from official site https://confluence.atlassian.com/jirakb/how-to-get-a-list-of-active-users-counting-towards-the-jira-application-license-278695452.html

SELECT DISTINCT u.lower_user_name
FROM   cwd_user u
       JOIN cwd_membership m
         ON u.id = m.child_id
            AND u.directory_id = m.directory_id
       JOIN globalpermissionentry gp
         ON Lower(m.parent_name) = Lower(gp.group_id)
       JOIN cwd_directory d
         ON m.directory_id = d.id
       AND d.active = '1'
       AND u.active = '1';


Ok, that looks good to me, assuming you are on JIRA 6.2.1, 6.3 or 6.4, so you need to check why the UI is returning a different answer.

How many people have you got in each of the "can log in" and "admin" groups?


Version     6.4.6

I go to User management and set filter user on include the group

- jira-users = 361

- jira-administrators = 10

- jira-developers = 55

When I run SQL query only like "WHERE  gp.permission ='USE';" and delete "AND d.active = '1' AND u.active = '1';"

I recieve result 224 users

What wrong a count users?

I don't think the count is wrong, I think the SQL does not match the question.  But I do not know what SQL you're actually effectively running.  I'm not a DBA and I only really get the basics of searching - could you show me what you're running without obscuring it with "as" statements ? (I know they simplify it for DBAs, but they make reading what the query does really hard)


Suggest an answer

Log in or Sign up to answer
Atlassian Community Anniversary

Happy Anniversary, Atlassian Community!

This community is celebrating its one-year anniversary and Atlassian co-founder Mike Cannon-Brookes has all the feels.

Read more
Community showcase
Fadoua M. Boualem
Published yesterday in Trello

Using Trello to manage events

As a Jira power user, I was at first doubtful that Trello could benefit my workflow. Jira already uses boards (ones you can customize!), so why would I even need to use Trello?! In this post you will...

429 views 5 6
Read article

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