Archiv der Kategorie: Atlassian JIRA

Atlassian JIRA mit MS SQL : Select the text, date or string value from a custom field of an issue

Problem

A string, date or a text-value of an custom field shall be retrieved from an issue

Solution

SELECT	pk.PROJECT_KEY+'-'+CAST(ji.issuenum AS varchar(max)) as issue, 
		cfv.STRINGVALUE, 
		cfv.TEXTVALUE,
		cfv.DATEVALUE
FROM jiraissue ji 
INNER JOIN customfieldvalue cfv ON ji.id=cfv.issue 
INNER JOIN project_key pk ON ji.PROJECT=pk.PROJECT_ID 
WHERE pk.PROJECT_KEY+'-'+CAST(ji.issuenum AS varchar(max))='AT-15' 
AND cfv.CUSTOMFIELD=(SELECT TOP 1 id FROM customfield WHERE cfname='Requested document')

Atlassian JIRA MS SQL Server: Get Affects version or fixed version from the Database by query

Problem

To fetch the values „Affects version“ or „Fixed Version“ from the JIRA Database via SQL, the use of different relations/tables is necessary

Solution – Lösung

You can use the ASSOCIATION_TYPE=’IssueVersion‘ for Affects version and ASSOCIATION_TYPE=’IssueFixVersion‘ to get the Fixed version

The next example shows how to retrieve Affects Version for Issue-ID AT-15:

SELECT projectversion.id, vname, project_key.PROJECT_KEY+'-'+CAST(jiraissue.issuenum AS varchar(max)) as pkey, ASSOCIATION_TYPE
FROM   projectversion, nodeassociation, jiraissue, project_key
WHERE  SINK_NODE_ID = projectversion.id
AND    SOURCE_NODE_ID = jiraissue.id
AND    project_key.PROJECT_ID=jiraissue.PROJECT
AND    ASSOCIATION_TYPE='IssueVersion' 
AND    project_key.PROJECT_KEY+'-'+CAST(jiraissue.issuenum AS varchar(max))='AT-15'

Atlassian JIRA+MS SQL Server Get values from multiple version picker custom field in SQL / Mulitple Version Picker Werte über SQL Query erhalten

Problem

The version strings of custom field, that can select multiple versions, shall be selected

Approach – Ansatz

The table projectversion can be used to connect to CustomFieldValue.NUMBERVALUE

Solution – Lösung

SELECT pk.PROJECT_KEY+'-'+CAST(a.issuenum AS varchar(max)) as issue, pv.vname
FROM project_key pk
INNER JOIN jiraissue a ON pk.PROJECT_ID=a.PROJECT 
INNER JOIN CustomFieldValue b ON a.ID=b.ISSUE
INNER JOIN customfield c ON b.CUSTOMFIELD=c.id 
INNER JOIN projectversion pv ON pv.ID=b.NUMBERVALUE
WHERE c.CFName = 'LUA Version(s)' 
--AND pk.PROJECT_KEY+'-'+CAST(a.issuenum AS varchar(max)) ='NDS-4352'
ORDER BY 1

JIRA SQL: Get Issue count of Issue Types and Issue status of alle JIRA projects / Anzahl der Issues pro Issue Type / Status pro Projekt

Problem

Für alle JIRA Projekte soll die Anzahl der Issues pro Issue Type und Issue Status ermittelt werden.

Ansatz – Approach

SQL Database Query

Lösung – Solution

SELECT project.pname 'Project', issuetype.pname 'Issue Type', issuestatus.pname 'Status', COUNT(project_key.PROJECT_KEY+'-'+CAST(issuenum AS VARCHAR(5))) 'Count'
FROM jiraissue
INNER JOIN project_key ON jiraissue.PROJECT=project_key.PROJECT_ID
INNER JOIN project ON CAST(project.ID as nvarchar)=project_key.PROJECT_ID 
INNER JOIN issuestatus ON issuestatus.ID=jiraissue.issuestatus
INNER JOIN issuetype ON issuetype.ID=jiraissue.issuetype  
GROUP BY project.pname, issuetype.pname, issuestatus.pname 

Atlassian JIRA+MS SQL Server: Get selected custom field value for Issue from JIRA Database in SQL

Problem

A selected custom field value, that is in a MS SQL Server JIRA Database should be retrieved.

Ansatz – Approach

The tables project_key, jiraissue, CustomFieldValue, customfield and customfieldoption have to be joined

Lösung – Solution

SELECT pk.PROJECT_KEY+'-'+CAST(a.issuenum AS varchar(max)) as issue, c.cfname as field, d.customvalue 
FROM project_key pk
INNER JOIN jiraissue a ON pk.PROJECT_ID=a.PROJECT 
INNER JOIN CustomFieldValue b ON a.ID=b.ISSUE
INNER JOIN customfield c ON b.CUSTOMFIELD=c.id 
INNER JOIN customfieldoption d ON c.id=d.CUSTOMFIELD 
WHERE c.CFName = 'Requirements/Specifications affected?' 
AND   b.STRINGVALUE=CAST(d.id as varchar(max))

JIRA 6.x SQL Statement: Get actual value / last change for custom field

Problem

The actual value of a custom field from an Issue should be selected

Solution

select CAST(pk.PROJECT_KEY as VARCHAR(10))+'-'+CAST(issue.issuenum AS varchar(10)), cfo.customvalue FROM 
CustomFieldValue cfv 
INNER JOIN CustomField CF on CF.Id = CFV.CustomField
INNER JOIN CustomFieldOption CFO on CF.Id  = CFO.CustomField
INNER JOIN jiraissue issue on issue.id = cfv.issue 
INNER JOIN project_key pk on PROJECT_ID=issue.PROJECT
WHERE cf.CFName = 'Classification' 
And CAST(CFO.Id AS VARCHAR(10)) =CFV.StringValue;

JIRA 6.1. and above: Database field PKEY is empty

Problem

In prior versions than JIRA 6.1., the table „jiraissue“ in the database has stored the Issue IDs and has made it possible to make a selection on Issue IDs. Unfortunalety this field is empty now. It is not possible to select values because of the Issue ID.

Approach

Analysing the table shows a new column name „issuenum“ which can be used in combination with the colum „project“ to concatenate the Issue Key.

Solution

SELECT project_key.PROJECT_KEY+‘-‚+CAST(issuenum AS VARCHAR(5)) as pkey, jiraissue.*
FROM jiraissue
INNER JOIN project_key
ON jiraissue.PROJECT=project_key.PROJECT_ID
WHERE project_key.PROJECT_KEY+‘-‚+CAST(issuenum AS VARCHAR(5))=’VVTT-65‘