How to import Jira User Attributes to Assets via CSV with either Atlassian Account or Customer IDs
Platform Notice: Cloud Only - This article only applies to Atlassian products on the cloud platform.
Summary
Importing User Attributes into Asset Objects does not work using Display Name or Email Address
When importing Jira User Attribute Values into Asset Objects, using a User's Display Name or their Email Address will not succeed in finding their User Account.
This is due to the Asset Importer expecting Atlassian Account IDs or Customer IDs instead.
We currently have the following Feature Request to add this functionality: JSDCLOUD-10487 - Getting issue details... STATUS
In the meantime, it is possible to export Atlassian Account IDs or Customer IDs, then fill a CSV with them for the Importer.
Environment
Jira Service Management Premium Cloud
Solution
The process to acquire Atlassian Account IDs for your Site's Users is the following, starting in your Jira Site:
- Click the Cog Wheel in the top right corner.
- Click User Management in the dropdown menu. This requires your User to be a Site Admin.
- Click Export Users on the Users page. This can be filtered, such as only Users with Site Access, or specific Roles or Product Access.
- Click Export Users on the Export users to CSV page. More filtering can be done here to only include certain User Groups, Active Users, and you can include Product Access as well as Group Membership in the exported information.
Once this process is complete, you will be emailed with a link to download a CSV containing your user information in a format similar to the following:
| User id | User status | Added to org | Org role | Last seen in Jira Service Management - <site> | Last seen in Jira - <site> | Last seen in Confluence - <site> | |
|---|---|---|---|---|---|---|---|
| <Atlassian Account ID> | <Email Address> | <Status> | <Date> | <Role> | <Date> | <Date> | <Date> |
To do this for Customers, instead navigate to the Jira Service Management page under User Management and click Export Users there, where you can download the CSV containing the Customer IDs in this format:
| username | full_name | active | last_login | |
|---|---|---|---|---|
| <Customer ID> | <Display Name, if provided> | <Email Address> | <Status> | <Date> |
With this information, there are many ways to fill the CSV to be imported to Assets with the IDs in place of the Emails or Display Names. Excel, Google Sheets, or any other spreadsheet software with functionality similar to a VLOOKUP can achieve this.
For our purposes, we will use Google Sheets with Atlassian Account IDs as an example. However, due to the arrangement of columns with User ID being first, VLOOKUP will not work as it only searches to the right, and not to the left.
Google Sheets provides a suggestion to handle this without having to move the User ID Column, though that would make VLOOKUP work fine.
Let's look at VLOOKUP first, after moving the Column to the right of the Email Address column, as we'll be using that in our example.
The same process can be used for the Customer IDs.
After loading our Exported Users CSV to Google Sheets on a sheet named Sheet1, we will create a new Sheet (named Sheet2) within the same Workbook. We will fill that with our Assets Import CSV.
We will then enter the following formula into the Jira User Attribute column for Sheet2:
=VLOOKUP(B2, 'Sheet 1'!A2:B, 2, FALSE)B2 is the Cell in Sheet2 containing the User's Email Address.
'Sheet1'!A2:B is the range VLOOKUP will search using the contents of Sheet2's B2.
2 is the Column Number for the value to be returned, in this case, User ID from the Exported Users CSV, as we have moved it to the right of the email column.
FALSE is a boolean for whether the 1st column has been sorted.
The alternative, if moving the columns is not desired, would be this formula:
=INDEX('Sheet1'!$A$1:$A, MATCH(B2, 'Sheet1'!$B$1:$B, 0))INDEX & MATCH are combined here to allow for searching to the left of a column.
'Sheet1'!$A$1:$A is the range for the value to be returned. In this case, the User ID.
B2 is the same as before. Sheet2's Email Address Cell to be searched for.
'Sheet2'!$B$1:$B is the range for the values to search. In this case, the Email Addresses.
0 is the type of search, which is an exact match search for this value.
Finally, once your formula is entered, simply fill Sheet2's User ID column with this formula by dragging down on the Fill Down square at the bottom right of the Cell containing it.
So long as the User exists in the Exported Users Sheet, the User ID should be filled.
Then, the Import to Asset should work as desired with the proper User Accounts being connected to the Objects via their Jira User Attribute being set.