is it possible to get only issue keys that were deleted

[Pradeep]

We need only issue keys that were delete (not issue details), so that we can compare those issue key and perform deletion in our database too.

[Andy Heinzer]

I found a way to figure this out using SQL.   Granted it's a bit of work to figure out, but I will try to explain my steps and I hope this helps.  I did this in postgresql, the syntax could be different for mysql, oracle, etc.

create table temptable1 (counter integer);  

first we create a temporary table

INSERT INTO temptable1
SELECT issuenum FROM jiraissue ji
join Project P on p.id = ji.Project
where p.pname='SCRUM'; 

then we insert into that new table all the issue keys currently in the project with the key SCRUM

select pcounter from project where pname='SCRUM'; 

Next we need to find out the pcounter value for this project.  This pcounter field is incremented by 1 each time an issue is created in that project.  Deleting issues does not decrement this counter.  My value was 33

select series, temptable1.counter 
from generate_series(1, 33, 1) series
left join temptable1 on series = temptable1.counter
where counter is null; 

We can use the generate_series function to make a sequence of number from 1 - 33. You would need to change that 33 value to match whatever value you got back from the pcounter query.  Using the left join we can then see which values are missing/null from our temptable.  With this information we can know which issue keys have been removed from this project.  

In turn we can then run a query like this to have SQL show us all the issue keys deleted from that project.

select 'SCRUM-' || series as IssueKey
from generate_series(1, 33, 1) series
left join temptable1 on series = temptable1.counter
where counter is null;

I wish I could have found a way to condense all this into one or two queries, but I wasn't able to do that.  Technically you can skip #4, and just run #5, but I just wanted to see the specific values when testing this out. 

Credit goes to this guide: http://www.silota.com/docs/recipes/sql-gap-analysis-missing-values-sequence.html

[Nic Brough -Adaptavist-]

Also, you might want to join it to moved_issues to check for issues that were moved out of the project, rather than deleted.

[Andy Heinzer]

oh, I'm an idiot, this is cloud.  There's no way for you to run such SQL queries against a Jira Cloud site.  nevermind...

[Joe Pitt]

If the issues are deleted they won't be in the database. 

Do not delete issues. When you delete it is GONE. Hardly a week goes by without someone wanting to restore an issue. Deleting issues will come back and bite you when it is the most inconvenient. I suggest closing with a resolution value of Deleted anything you want to delete. I implement a special transition only the project lead can execute and it requires filling in a reason field. Missing issue numbers will eventually cause a question about what it was and why was it deleted even if it was done properly. Missing data always brings in the question of people hiding something that may have looked bad.

[Nic Brough -Adaptavist-]

They've been deleted, so no.

Why are you making obsolete copies of data in "your database"?