Confluence Query

[Mike]

Curious if anyone has been able to get the following information from Confluence:

• Query list of users that have "subscribe to all blog posts" enabled
• Query list for users based on number of failed logins / login attempts

I am working to improve the user experience and wanting to proactively reach out to users who may be receiving too many notifications or unable to sign in.

Running 6.13.0 w/ mssql 

[Diego · Accepted solution]

Hello again Mike!

Recently we took care of the Subscribe to all blog posts part. Here is the link to our thread:

Query list of user who have "Subscribe to all blog posts" checked

Now, we need to look into the failed login attempts. Again, having direct access to the database is a must. Also, always be careful when dealing with your database data!

For us to obtain the desired data, we will need to use a SELECT statement, like this:

1. Go to you database management tool
2. Run the following query:

SELECT * FROM logininfo
JOIN user_mapping ON user_mapping.user_key = logininfo.username
ORDER BY totalfailed DESC

Since this is a SELECT statement, there should be no need to shutdown Confluence. Remember that changing data in the database requires backup and scheduled downtime of the instance.

Here, we have new entries being created under the logininfo table. Each failed attempt, adds 1 to the totalfailed column and also the curfailed one. When the users succeeds the login procedure, the curfailed column resets to 0.

Also, if we take a look at the faileddate column, we can see when the last failure happened.

The ORDER BY ... DESC organizes the results in a descending fashion. Meaning that the biggest will be on top, quite possibly being a user who has a lot of problems to login.

The user_mapping table that we are joining into contains user information such as username. Matching the user_key column from the user_mapping table and the username column from logininfo allows us to now who we are looking into.

Let us know if this helps you out!