How to get login count in Confluence?

Hi All,

can you please suggest?

Thanks in advance!

Rumi

6 answers

1 accepted

This widget could not be displayed.
Mizan Community Champion Jun 13, 2012

Try this macro

Amazing macro, just know this macro existed.

Thanks for sharing

Hi,

thanks for the macro.

But I get NEver logged in for everybody!!

Mizan Community Champion Jun 13, 2012

What version of Confluence you using ?

Mizan Community Champion Jun 13, 2012

can you try to replace the 30th line of the above macro with the below line

#if (!$loginManager.getLoginInfo($user.name).lastSuccessfulLoginDate)

Hi Mizan,

I use 3.5.13

Hi Mizan,

I replaced the line, but the 'Last successful login' is still empty.

Mizan Community Champion Jun 13, 2012

Does the macro work after doing the above changes , most probably it will work because for jira 3.5 getLoginInfo() requires string as a argument .

Mizan Community Champion Jun 13, 2012

is there any difference in the output after editing the macro ? or its the same ?

even replace $user with $user.name in the else clause

Great work, Mizan!

Now it displays all the last login info.

The results are very strange for us: from 1995 users 1120 never logged in. Can this macro fail? I mean for example an upgrade happened in the meantime and does it register the data before upgrade or server restart?

Thanks again! Nice macro!

Rumi

Mizan Community Champion Jun 13, 2012

The macro is correct as it has helped others , its written by Andrew Frayling . I dont think the upgrade has affected your output .

Glad people are finding the macro useful, credit to Remo Siegwart though for his answer to this question.

I doubt an upgrade or restart would affect the data, but it might be affected if the user repository changes. It uses standard API calls and I can see how the data may potentially be reset if you did something like remove a user repository and configure Confluence to use a different one as the old data may not exist, or the API call may not look for it. I've never tested it against that set of circumstances though.

Andrew.

Hi,

Now it doesn't work! The page where we put the macro loading for 4-5 minutes and then timeout. What could happen?

Mizan Community Champion Jun 17, 2012

have you upgraded your Confluence ?

Yes, we already upgraded on friday to version 4.2.

The macro worked on friday, however it was very long time to load. Today it gets timepot everytime we refresh: after 4-5 minutes of loading.

Mizan Community Champion Jun 17, 2012

Thats a strange behavior , you should try the macro provided in this blog instead of the one which we modified for ver 3.5 .

I replaced the code, but it's still veryslow and timeout. And I fear that whenever I run this macro, the whole Confluence is very slow. Can it be? Many of my customers complained today that Confluence is very slow.

Can it be this slowly loading because of the 2000 users?

I fear that this macro eats the memory of the whole Confluence instance!!!

Can you please confirm?

hi,

I can confirm that this macro cause the problem: we checked the log!!

Mizan Community Champion Jun 17, 2012

This might be because of the number of users and a nested if-else inside the macro , you can try the query provided by Septa

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';

UNfortunately the SQl didn't work last time, this is why we would like to use the macro. Can these nested if-else be workarounded?

Mizan Community Champion Jun 17, 2012

No they are needed , going with the sql will be a better option since you have 2000 users (and performance issues when you use this macro) , did you try the sql after upgrading to 4.2 ?

hi every

where to run above query provided by septa

This widget could not be displayed.

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,

Rumi

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.

Hope it helps.

Cheers,
Septa Cahyadiputra

Ah, that means you are not on Confluence 4.x yet. May I know which version of Confluence you are on?

Hi,

many thanks for the select.

The only problem in our side that we don't have a table logininfo.

Septa,

sorry! I didn't mention: we have 3.5.13 version.

Could we hope? Is there a different table for logininfo?

Thanks,

Rumi

This widget could not be displayed.

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);

Hope it helps.

Cheers,
Septa Cahyadiputra

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.

Maybe this is why I got all the users as result?

We use common user management in Jira and Confluence. We create the user account in Jira which synchronize in every hour to Confluence.

Hi Septa,

the second select gives all the users as result! 2331 users. Some condition missing maybe?

It seems to be the case. I have confirmed again to ensure that the given query is valid. The latest query that provided the directory name would give you a much better idea on the origin of the users.

This widget could not be displayed.

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?

This widget could not be displayed.

Still works on 4.2 too: it's OK.

This widget could not be displayed.

hi every one

what is macro

where to run

 

plase help me

Suggest an answer

Log in or Sign up to answer
Atlassian Summit 2018

Meet the community IRL

Atlassian Summit is an excellent opportunity for in-person support, training, and networking.

Learn more
Community showcase
Posted Wednesday in Teamwork

What teamwork quotes inspire you?

Hey everyone! My name is Natalie and I'm an editor of the Atlassian Blog and I've got a question for you: What's your favorite quote about teamwork?  We've compiled a list here, along with...

190 views 18 7
Join discussion

Atlassian User Groups

Connect with like-minded Atlassian users at free events near you!

Find a group

Connect with like-minded Atlassian users at free events near you!

Find my local user group

Unfortunately there are no AUG chapters near you at the moment.

Start an AUG

You're one step closer to meeting fellow Atlassian users at your local meet up. Learn more about AUGs

Groups near you