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

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"

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

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

Suggest an answer

Log in or Sign up to answer
Community showcase
Published Feb 27, 2018 in Crowd

The Crowd team is looking for feedback on Server & Data Center customers' identity strategies!

Do you own more than one Server or Data Center product? Do you have challenges provisioning users across your Atlassian products? Are you spending a lot of time integrating each Atlassian product wit...

1,572 views 6 14
Read article

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