Slack - Add to Jira Issue does nothing

[Chris Hudson]

I have the Jira Cloud app in Slack and it works perfectly so far for everything except the "Add to Issue" message action. the prompt pops up and I can select the issue I want to add the selected message to, it then tells me it was successfully attached to the issue, but there is nothing in the issue relating to the message. 
I cannot find any errors or anything online to assist in trouble shooting this. Can you help me out please?

[Nic Brough -Adaptavist- · Accepted solution]

The OS_ tables are workflow historym and you can extract it from there.

The Jira "what has changed" tables are a bit more nice to work with though (I think it's because you can see a simple relationship between the tables and what a user sees on the issue "history" tab)

Look for ChangeItem and ChangeGroup.

The group is a header for the change, it holds the issue, the person who made the change and the date/time. So a "select * from changegroup where issue = jiraissue.id" will give you a list of all the groups of changes.

Once you've got a group, you can use "select * from changeitem where group = changegroup.id" to get a list of all the fields that were changed during that change. You'll be able to see "status" in there, with it's old and new values.

(Note - I'm not a DBA, I only know the basics of SQL, so I'll leave the best way to build your query up to you based on the relationships I've just scribbled)

[Erwin Manuel]

Thanks Nic! this is exactly what we need

[Ramiro P]

Hi @Erwin how you managed to make the query? Could you please share it? :)

[Erwin Manuel]

Hi Ramiro,

You can use something similar to

select 
	pkey 'Issue ID'
	,computeReopen.[Count Reopened]
from jiraissue ji
inner join (
	select
		cg.issueid
		,COUNT(ci.ID) as 'Count Reopened'
	from changeitem ci
	left join changegroup cg on cg.ID = ci.groupid
	where ci.FIELD = 'status'
	and cast(ci.NEWSTRING as varchar(1000))like 'Reopened'
	group by cg.issueid
	) computeReopen on ji.ID = computeReopen.issueid
where ji.PROJECT = (select ID from project where pkey = 'AAA')
order by pkey

Hope this helps.

[Ramiro P]

Thanks Erwin, I'll give it a try :D