JQL for getting reporters with different domain email

[adelgadox-kvk]

Hello!

We have detected that some users have been able to create tickets using emails other than the company's and I would like to be able to get all the tickets that were created with a reporter other than the domain (let's use @zzzzzz.com as an example)

I thought about using "not contains" (!~) but it only applies to free text fields.

Do you know any other way to obtain this data?

Thanks in advance!

[Giovanni Melai · Accepted solution]

Hi @adelgadox-kvk,

I can't think of an easy way to do this directly with JQL, also because in Jira Cloud the reference to the users is not with the username/email but with the user ID. 

What I can suggest is to get the users IDs from the admin console. You can go to admin.atlassian.com and select Directory. 

Now, if you have only a few users you can just open each of those and check the URL, the user ID is the last part of the URL: ...members/<User ID>

If you have many users you can export the full list in CSV and import it in gsheet/excel so that it's easier to filter the users. 

Once you have all the IDs you can use the following JQL:

reporter in (<UserId1>,<UserId2>,...)

 I hope this helps

[adelgadox-kvk]

Hi @Giovanni Melai ,

Yes, I thought the export to CSV could be a solution.

We have almost 8000 users but I think it will be easier to create a solution using Google Sheets from the CSV data.

Thanks Giovanni for this alternative of solution.