nav[aria-label="Primary Navigation"] { padding: 0; & ul { list-style: none; width: 100%; display: flex; flex-direction: row; justify-content: start; align-items: start; gap: 30px; padding: 0; & li { margin: 0; } & ul li { list-style: none; } } }

Jira PostgreSQL Query for listing failed login attempts

unknown

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.

Accepted answers

Vikrant Yadav

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".

User Per Page.PNG

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

All comments

Vikrant Yadav

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".

User Per Page.PNG

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

unknown

@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!

Welcome!

It looks like you're new here. Sign in or register to get started.

Quick Links

Find more posts tagged with

server

jira

jira-server