Is there a way to get information on when a user is added to organisation in JIRA Service desk?

Hi ,

I am looking for a way to get information on when a user is added to organisation in JIRA Service desk? Is it available in any history tables or logs?

1 answer

1 accepted

Accepted Answer
1 vote

There is a way to do this, but it's really complicated.  Fair warning.   Natively, Jira is not creating any log files that indicate this has happened.  However there are specific database tables that are getting updated as a result of this email address being added to an organization.

There is an AO table that lists the organization members, "AO_54307E_ORGANIZATION_MEMBER", however if you do a simple select on this table you will find that this does not indicate any date as to when this was added. :(

To dig into this, I turned on the SQL logging Jira provides and then added a user to an organization in order see what SQL entries where added to the database.  I found that the cwd_user_attributes table is getting updated with a complex string that appears to be some kind of invitation system.   These entries appear to directly correspond to existing user accounts in jira, so with a SQL join statement you can see each user that has such an entry with something like this:

SELECT cu.display_name, cu.email_address, cua.attribute_value
  FROM cwd_user_attributes cua join cwd_user cu ON cua.user_id = cu.id where attribute_name='local.servicedesk.outstanding.agent.invites';

When you run this it provides an output like this:

"test";"test@test";"{"value":"251ba7bd5d3559ee5a65abbb1d88e63689c40034","expiry":1510765935095,"projectIds":[10100]}"

The projectIds value corresponds to the 'id' field in the project table,  in this case 10100.  The interesting part of this query is that you do get an 'expiry' date added.  This is a Epoch Unix format of a date that appears to be exactly 4 weeks into the future from the time the user was added.   So you could input that string of '1510765935095' into a converter such as https://www.epochconverter.com/

 in turn this indicates that this expiration date is GMT: Wednesday, November 15, 2017 5:12:15.095 PM

From there you just have to back track exactly 4 weeks (28 days), and this appears to the be correct date/time this record was created.  I did note that my instance was 1 hour off, but I think this is because my timezone is planning to have a daylight savings timezone adjustment between now and this Nov 15th date.

Sorry there does not appear to be an easier way to determine when this happened currently.  I would welcome any other approaches to trying to determine this time.  It feels like it should not be this complex to determine.   Also, I am not certain if those user attributes are ever getting purged from that table, say once the expiry date is passed.  But that might be possible.

Suggest an answer

Log in or Sign up to answer
Community showcase
Published Aug 13, 2018 in Jira Service Desk

Jira Service Desk – Don’t be afraid, the journey begins with curiosity!

...be more productive while being fun to use at the same time. For some, getting started can be a bit intimidating. This is especially true if Jira Service Desk is your first exposure to Atlassian...

17,371 views 13 31
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