lastlogin crowd to list non active users

M January 19, 2015

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

0 votes
M January 20, 2015

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
Caspar Krieger
Atlassian Team
Atlassian Team members are employees working across the company in a wide variety of roles.
January 19, 2015

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

Steve Thornhill
Rising Star
Rising Star
Rising Stars are recognized for providing high-quality answers to other users. Rising Stars receive a certificate of achievement and are on the path to becoming Community Leaders.
January 19, 2015

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

M January 20, 2015

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 ?

 

Caspar Krieger
Atlassian Team
Atlassian Team members are employees working across the company in a wide variety of roles.
January 20, 2015

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.

Steve Thornhill
Rising Star
Rising Star
Rising Stars are recognized for providing high-quality answers to other users. Rising Stars receive a certificate of achievement and are on the path to becoming Community Leaders.
January 20, 2015

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

0 votes
Steve Thornhill
Rising Star
Rising Star
Rising Stars are recognized for providing high-quality answers to other users. Rising Stars receive a certificate of achievement and are on the path to becoming Community Leaders.
January 19, 2015

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

Neeta Dubey March 18, 2015

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
AUG Leaders

Atlassian Community Events