How to get login count in Confluence?

[Rumceisz]

Hi All,

can you please suggest?

Thanks in advance!

Rumi

[Mizan · Accepted solution]

Try this macro

[Septa Cahyadiputra]

Amazing macro, just know this macro existed.

Thanks for sharing

[Rumceisz]

Hi,

thanks for the macro.

But I get NEver logged in for everybody!!

[Mizan]

What version of Confluence you using ?

[Mizan]

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

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

[Rumceisz]

Hi Mizan,

I use 3.5.13

[Rumceisz]

Hi Mizan,

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

[Mizan]

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]

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

[Rumceisz]

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]

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

[Andrew Frayling]

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.

[Rumceisz]

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]

have you upgraded your Confluence ?

[Rumceisz]

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]

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

[Rumceisz]

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?

[Rumceisz]

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

Can you please confirm?

[Rumceisz]

hi,

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

[Mizan]

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

[Rumceisz]

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]

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 ?

[venkat Muppidi]

hi every

where to run above query provided by septa

[venkat Muppidi]

hi every one

what is macro

where to run

plase help me

[Rumceisz]

Still works on 4.2 too: it's OK.

[Rumceisz]

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?

[Septa Cahyadiputra]

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

[Septa Cahyadiputra]

Does this helps? Let me know if you need anything

[Rumceisz]

Thank you very much!

I opt the second select. Hope that helps, le'ts see!

[Septa Cahyadiputra]

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.

[Rumceisz]

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.

[Rumceisz]

Hi Septa,

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

[Septa Cahyadiputra]

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.

[Septa Cahyadiputra]

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

[Rumceisz]

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

[Septa Cahyadiputra]

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

[Septa Cahyadiputra]

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

[Rumceisz]

Hi,

many thanks for the select.

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

[Rumceisz]

Septa,

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

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

Thanks,

Rumi