Come for the products,
stay for the community

The Atlassian Community can help you and your team get more value out of Atlassian products and practices.

Atlassian Community about banner
4,298,303
Community Members
 
Community Events
165
Community Groups

lastlogin crowd to list non active users

Hi,

I'm searching for the last authentification on Crowd database (We use JIRA + Confluence)

What is the field I should trust?

Regards,

 

3 answers

The only field which sound interesting is lastAuthenticated, unfortunately when I'm doing a Query on it, I get weird values (My own user shows that my last log three month ago)

Please take sometimes to answer to theses t<wo questions

1) Is this filed populated officialy (I mean Crowd recommand to use it)

2) What is the exact syntaxe on Oracle to extract the value?

 

SELECT cwd_user.user_name, to_date(cwd_user_attribute.attribute_value,'YYYY-MON-DD HH24:MI:SS')
FROM cwd_user, cwd_user_attribute
WHERE cwd_user_attribute.user_id = cwd_user.id
AND cwd_user_attribute.attribute_name = 'lastAuthenticated';

0 votes

Steve's answer is pretty close.

If you have Crowd 2.8 or later and are using SSO (https://jira.atlassian.com/browse/CWD-3914), this should work for postgres:

SELECT cwd_user.user_name, to_timestamp((cwd_user_attribute.attribute_value)::bigint / 1000)
FROM cwd_user, cwd_user_attribute
WHERE cwd_user_attribute.user_id = cwd_user.id
AND cwd_user_attribute.attribute_name = 'lastActive';

If you have Crowd < 2.8, I think you might be able to derive this information from the cwd_token tables, but your best bet is probably looking at (or scraping) the User Sessions screen several times.

(And for completeness, if you're using Atlassian Cloud JIRA/Confluence, this information is displayed in the user listing.)

Thank, always handy to have a copy of the Crowd one to save working it out.

We are using Oracle + crowd + JIRA +confluence and 'lastActive' doesn't exists and to_timstamp is not accepted, I've tried to_date() as below but nothing really works.

 

SELECT cwd_user.user_name, to_date(cwd_user_attribute.attribute_value,'YYYY-MON-DD HH24:MI:SS')
FROM cwd_user, cwd_user_attribute
WHERE cwd_user_attribute.user_id = cwd_user.id
AND cwd_user_attribute.attribute_name = 'lastAuthenticated';

 

Do you have an Oracle syntaxe working ?

 

No, I don't have an Oracle database handy to test with. Since you don't have any lastActive attribute on the user in Crowd, I suggest you look at the User Sessions page I linked above instead.

A select * from cwd_user_attribute will show you any useful fields you can use in the search

This is what I use for JIRA,

Should be able to swap the "jira" parts to crowd and get similar results

 

SELECT jira.cwd_user.user_name, from_unixtime(round(jira.cwd_user_attributes.attribute_value/1000))
FROM jira.cwd_user, jira.cwd_user_attributes
WHERE jira.cwd_user_attributes.user_id = cwd_user.id
AND jira.cwd_user_attributes.attribute_name = 'lastAuthenticated';

I'm using this query however it gives error " round function requires 2 to 3 arguments"

Suggest an answer

Log in or Sign up to answer
TAGS
Community showcase
Published in Confluence

An update on Confluence Cloud customer feedback – June 2022

Hi everyone, We’re always looking at how to improve Confluence and customer feedback plays an important role in making sure we're investing in the areas that will bring the most value to the most c...

173 views 1 3
Read article

Community Events

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

Find an event

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

Unfortunately there are no Community Events near you at the moment.

Host an event

You're one step closer to meeting fellow Atlassian users at your local event. Learn more about Community Events

Events near you