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

Rashmi Belur October 16, 2017

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

2 votes
Answer accepted
Andy Heinzer
Atlassian Team
Atlassian Team members are employees working across the company in a wide variety of roles.
October 18, 2017

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

Atlassian Community Events