Locating Custom Field data

[Michael J. Schneider]

I connected to JIRA via SQL and created a query.  I created a Custom Field in my tickets named REPORTED BY, and I can find the numeric value, but I cannot find the Alpha data which would be a persons name.  Any idea where I could find that data within the DB?

[Taranjeet Singh]

@Michael J. Schneider See if this documentation provides you the required user information from JIRA database: https://confluence.atlassian.com/jirakb/how-to-extract-user-data-from-external-database-table-699957597.html

[Taranjeet Singh]

@Michael J. Schneider Did my answer helped resolving your query?

[Michael J. Schneider]

We are working on it. I should know by tomorrow if it worked.

[Michael J. Schneider]

Taranjeet

My SQL admin was able to find the "Custom Field_Reported By" field that I set up, but it only brings back numeric values.  He checked the JIRA Schema pdf and was not able to find it. That is the last field we need for what we are doing.

let
    Source = Sql.Databases("JCSQL55"),
    JiraDB = Source{[Name="JiraDB"]}[Data],
    dbo_vw_JiraIssue = JiraDB{[Schema="dbo",Item="vw_JiraIssue"]}[Data]
in
    dbo_vw_JiraIssue

The question is related to the portion where we determine the ReportedBy custom field. 

SELECT PR.pkey + '-' + convert(varchar, i.issuenum) AS IssueKey
              , PR.pkey as ProjectKey
              ,i.SUMMARY
              --,CG.ID as IssueId
              ,it.pname AS IssueType
              ,ISs.pname AS IssueStatus
              ,P.Pname AS Priority
              ,i.CREATOR as Creator
              ,i.DESCRIPTION
              ,(
                     SELECT STRINGVALUE
                     FROM customfieldvalue
                     WHERE CUSTOMFIELD = 10108  -- HysolateTicket
                           AND ISSUE = i.ID
                     ) AS CustomField_HysolateTicket
              ,i.CREATED
              ,i.UPDATED
              ,(
                           select DATEVALUE
                           from customfieldvalue
                           where CUSTOMFIELD = 10601  -- Closed Date
                           and ISSUE = i.ID     
                           ) AS Closed_Date
              , (
                     SELECT top 1 STRINGVALUE   -- Pending match to the personName
                     FROM customfieldvalue
                     WHERE CUSTOMFIELD = 10204  -- ReporteBY
                           AND ISSUE = i.ID
                     )      AS CustomField_Reportedby
       FROM jiraissue I WITH (NOLOCK)
       INNER JOIN [issuetype] IT ON I.issuetype = it.id
       INNER JOIN issuestatus ISs ON i.issuestatus = ISs.id
       INNER JOIN priority P ON P.id = I.PRIORITY
       INNER JOIN PROJECT PR WITH (NOLOCK)      ON PR.ID = I.PROJECT

[Michael J. Schneider]

Any idea where the information in this Custom Field "Reported By" would be located?  I created it because the CREATOR field isn't always the person who reported it.

[Taranjeet Singh]

@Michael J. Schneider You can extract that information from app_user table in the JIRA database, I think. You may need to use JOINS with app_user table to extract this user information for your issues. See the link for the documentation I provided for more information on what information you can get from app_user table.