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?
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:
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.
Badges are a great way to show off community activity, whether you’re a newbie or a Champion.Learn more
...+ reading Fantasy). The same is true for him at the bank he works for: Efficiency is key when time literally equals money. Read on to learn how Sergey makes most of the time he has by...
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!
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