How to get the Inactive users count from the Database

[Kanaka Ponnuru Prasad]

Hi Team,

Recently our LDAP got migrated and during this activity many users were lost group access and some are showing inactive which are supposed to be active.

Can someone help us how to get the count for inactive users from database side.

Note: We already pulled the data from DB side and while we are filtering with particular data(day which this LDAP activity is happened) we are not getting the appropriate data. With the filter we are able to get a count of around 10-15 users but when we cross check these users in "User Management" side these accounts are already inactive long back. So this seems that this filter is not working.

Regards,

Kanaka.

[Fabio Racobaldo _Herzum_]

Hi @Kanaka Ponnuru Prasad ,

welcome to the Atlassian community!

Inactive users, on JIRA DB side, can be retrieved using field "active" in the table "cwd_user".

So, in order to have a list of inactive users please use the following query :

SELECT *
FROM cwd_user cu
WHERE cu.active='F';

In order to retrieve the total number of inactive users uses :

SELECT count(*)
FROM cwd_user cu
WHERE cu.active='F';

Hope this helps,

Fabio

[Kanaka Ponnuru Prasad]

Hi @Fabio Racobaldo _Herzum_ ,

Thank you for the prompt response.

when i use the below query I am getting "SQL query not properly ended". can you please guide what went wrong and what is cu here do we need to put anything here?

Will this query runs on a particular date? I mean we are facing this issue since 25th October between 8AM CET to 10AM CET. can we get the results with that particular date?

SELECT *
FROM cwd_user cu
WHERE cu.active='F';

Regards,

Kanaka

[Fabio Racobaldo _Herzum_]

HI @Kanaka Ponnuru Prasad ,

query is well-formed and there are no missing elements.

Moreover, on JIRA side, you have information about user status (active or inactive) created and updated date of the user but you have not information when user has been deactivated. Those information are on AD side.

Fabio 

[Kanaka Ponnuru Prasad]

@Fabio Racobaldo _Herzum_ ,

So You mean to say there is nothing we get from DB side with the above queries?

We need to check from LDAP side right?

Regards,

Kanaka