Hi All,
can you please suggest?
Thanks in advance!
Rumi
Try this macro
Hi Rumi,
Judging from your question I assume you would like to know how many failed login attemp recorded. You could do this by two method, from database
SELECT u.user_name, a.attribute_value as FailedLogin FROM cwd_user_attribute a join cwd_user u ON a.user_id=u.id WHERE attribute_name='invalidPasswordAttempts';
This will return the username and how many failed login attempt registered on the database.
It also possible to get it using REST end point as describe here, you might want to double check the parameter but I think it should be the same with Confluence.
Hope it helps.
Cheers,Septa Cahyadiputra
Hi Septa,
no, in our case we would like to remove those users that never had logged into Confluence. So we need the list those users whose login count=zero.
Can you suggest?
Many thanks,
Amazing macro, just know this macro existed.
Thanks for sharing
Hi,
thanks for the macro.
But I get NEver logged in for everybody!!
Even though you already got your answer as I believe using the macro is much easier, just want to add couple of SQL that might help. Here is to get the list of users that never authenticate agains Confluence:
SELECT u.user_name, u.first_name, u.last_name, u.created_date FROM cwd_user u WHERE NOT EXISTS (SELECT a.username FROM logininfo a WHERE u.user_name = a.username);
Here is the SQL query to get the list of user that never successfully login after a specific date The
SELECT u.user_name, u.first_name, u.last_name, u.created_date FROM cwd_user u JOIN logininfo l ON u.user_name=l.username WHERE l.successdate > '2011-12-31';
above query will get you the list of users that last successful login is 31st of December 2011.
What version of Confluence you using ?
Ah, that means you are not on Confluence 4.x yet. May I know which version of Confluence you are on?
Yes you can !
Here is the query for users that login after specific date. Please noted that the date is using epoch. You can convert it here
SELECT u.user_name, u.first_name, u.last_name, d.directory_name, u.created_date FROM cwd_user u JOIN cwd_user_attribute l ON u.id=l.user_id JOIN cwd_directory d ON u.directory_id=d.id WHERE l.attribute_value > '1338422400' AND l.attribute_name='lastAuthenticated';
Here is the query for user that never login into Confluence
SELECT u.user_name, u.first_name, u.last_name, d.directory_name, u.created_date FROM cwd_user u JOIN cwd_directory d ON u.directory_id=d.id WHERE NOT EXISTS (SELECT a.user_id FROM cwd_user_attribute a WHERE u.id = a.user_id);
Does this helps? Let me know if you need anything
Thank you very much!
I opt the second select. Hope that helps, le'ts see!
Just want to inform you that I add directory_name value to avoid confusion as there is a possiblity that the user that never try to log-in are registered under a different directory.
Hi Mizan, hi Septa,
the macro works great, only backlash that it's a bit slow due to the 2000 users.
I would like to ask you what about the Last login macro/feature on 4.2 Confluence version? We upgrade the Confluence this weekend (I just learned the info), so from sunday we will use 4.2.
Our developer colleague said that the macro won't work on the upgraded version. But you suggested a feature first which was for 4+ version?
Still works on 4.2 too: it's OK.
hi every one
what is macro
where to run
plase help me
It looks like you're new here. Sign in or register to get started.
