Jira PostgreSQL Query for listing failed login attempts

[Deleted user]

Hello,

As an applicationmanager one of my monthy tasks is to create a list of failed login attempts in Jira. I am not an database administrator and new to PostgreSQL.

I hope anyone can help me with a query to create this list. We have postgres72 with DB version 10.14.

We use Jira version 8.15

For now I randomly look in User Management but this is not a great solution.

[Vikrant Yadav · Accepted solution]

Hi @[deleted]  I suggest you not to touch JIRA PROD Database, if you are not expert in SQL. In order to get the list users getting error "Reset Failed Login Count". For this you can do one thing :-

Select "All" in User Per Page >> Click Filter> Export Data in Excel >> Apply Filter on Username Column . You get all users having error "Reset Failed Login Count".

https://confluence.atlassian.com/jirakb/reset-failed-user-login-count-442270086.html

Qurey is avaialbe here, if you want to do it with Databse  :- https://confluence.atlassian.com/jirakb/reset-a-user-s-login-count-from-the-database-in-jira-server-329352344.html

Thanks,

V.Y

[Deleted user]

@Vikrant Yadav Thank you for your answer. I am almost there.

I created an cvs file from the export function but there is no way I find "Reset Failed Login Count"as text or column. 

The first query for example I tried from the DB options you gave me returned the value of the invalidPasswordAttempts attribute.

Two additional questions:

For the csv: I followed the steps you describes, but where can I find the invalidPasswordAttempts in the file

For the query: Any idea for to edit the query so that only invalidPasswordAttempts is used for all users in the database. So I get a list with the columns username and invalidPasswordAttempts.

[Vikrant Yadav]

@[deleted]  Try below Query  :- 

SELECT user_id, display_name, updated_date last_login, attribute_value login_count FROM cwd_user a, cwd_user_attributes b where attribute_name = 'login.count'
and a.id = b.user_id

[Vikrant Yadav]

Working Query :- 

SELECT user_id, display_name, updated_date last_login, attribute_value login_count FROM cwd_user a, cwd_user_attributes b where attribute_name = 'login.totalFailedCount'
and a.id = b.user_id

Hope it works for you!

[Deleted user]

@Vikrant Yadav This is really great stuff and what I am looking for.

Maybe you have a bonus tip for me? Is it easy to show al login.acount > 3 for example?

I want to thank you for the time and your support!!

[Vikrant Yadav]

@[deleted]  Great :) ...Glad to hear it works for you!

For login counts you can use below query and filter out those users having count greater then three :- 

SELECT user_id, display_name, updated_date last_login, attribute_value login_count FROM cwd_user a, cwd_user_attributes b where attribute_name = 'login.count'
and a.id = b.user_id

Thanks

V.Y