nav[aria-label="Primary Navigation"] { padding: 0; & ul { list-style: none; width: 100%; display: flex; flex-direction: row; justify-content: start; align-items: start; gap: 30px; padding: 0; & li { margin: 0; } & ul li { list-style: none; } } }

lastlogin crowd to list non active users

M_22fzaj

Hi,

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

What is the field I should trust?

Regards,

Accepted answers

All comments

Steve Thornhill2

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

Caspar Krieger

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 Thornhill2

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

M_22fzaj

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

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.

M_22fzaj

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?

Neeta Dubey

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