Create
cancel
Showing results for 
Search instead for 
Did you mean: 
Sign up Log in
Deleted user
0 / 0 points
Next:
badges earned

Your Points Tracker
Challenges
Leaderboard
  • Global
  • Feed

Badge for your thoughts?

You're enrolled in our new beta rewards program. Join our group to get the inside scoop and share your feedback.

Join group
Recognition
Give the gift of kudos
You have 0 kudos available to give
Who do you want to recognize?
Why do you want to recognize them?
Kudos
Great job appreciating your peers!
Check back soon to give more kudos.

Past Kudos Given
No kudos given
You haven't given any kudos yet. Share the love above and you'll see it here.

It's not the same without you

Join the community to find out what other Atlassian users are discussing, debating and creating.

Atlassian Community Hero Image Collage

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
TAGS
Community showcase
Published in Marketplace Apps & Integrations

☕️ Monday coffee with Jexo: Weekly Atlassian news roundup | 21st June 2021

Hi community 👋, as every Monday we're bringing you a quick update on what happened in the Atlassian ecosystem last week. There were a few interesting events like for example the announcement of th...

68 views 0 6
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