JIRA: Lets download JIRA data N Report

JIRA is a great product, in numerous ways. But at times the pain of PM’s (Project Managers) is, they are unable to get the holistic view across multiple projects. This becomes more import when the resources are shared across numerous projects.

So as problem defined above the solution that struck me was

Just download the JIRA data and build custom reports on them

And so the solution goes:

1. Get a JIRA account, and navigate to the following URL to obtain a API login token.
   API Token
2. Once the token is obtain proceed to start creating the following SQL tables:
   • [JIRA].[Issues]
   • [JIRA].[Projects]
   • [JIRA].[Users]

   
   CREATE TABLE [JIRA].[Issues](
   	[JiraIdentifier] [int] NOT NULL,
   	[IssueKey] [varchar](50) NULL,
   	[ParentIssueKey] [varchar](50) NULL,
   	[Priority] [varchar](20) NULL,
   	[Project] [varchar](20) NULL,
   	[Assignee] [varchar](20) NULL,
   	[Reporter] [varchar](50) NULL,
   	[Resolution] [varchar](50) NULL,
   	[Description] [varchar](max) NULL,
   	[Status] [varchar](20) NULL,
   	[Summary] [varchar](max) NULL,
   	[Type] [varchar](20) NULL,
   	[CreatedDate] [datetime] NULL,
   	[UpdatedDate] [datetime] NULL,
   	[DueDate] [datetime] NULL,
   	[ResolutionDate] [datetime] NULL,
   	[StartDate] [datetime] NULL,
   	[LastUpdated] [datetime] NULL,
    CONSTRAINT [PK_JiraIssue] PRIMARY KEY CLUSTERED 
   (
   	[JiraIdentifier] ASC
   )WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY],
   UNIQUE NONCLUSTERED 
   (
   	[JiraIdentifier] ASC
   )WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
   ) ON [PRIMARY] TEXTIMAGE_ON [PRIMARY]
   GO
   
   CREATE TABLE [JIRA].[Projects](
   	[ProjectID] [int] NOT NULL,
   	[ProjectKey] [varchar](20) NULL,
   	[Lead] [varchar](50) NULL,
   	[Name] [varchar](400) NULL,
   	[LastUpdated] [datetime] NULL,
    CONSTRAINT [PK_JiraProjects] PRIMARY KEY CLUSTERED 
   (
   	[ProjectID] ASC
   )WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY],
   UNIQUE NONCLUSTERED 
   (
   	[ProjectID] ASC
   )WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
   ) ON [PRIMARY]
   GO
   
   CREATE TABLE [JIRA].[Users](
   	[UserName] [varchar](50) NOT NULL,
   	[DisplayName] [varchar](100) NULL,
   	[IsActive] [bit] NULL,
   	[Email] [varchar](50) NULL,
   	[LastUpdated] [datetime] NULL,
    CONSTRAINT [PK_JiraUsers] PRIMARY KEY CLUSTERED 
   (
   	[UserName] ASC
   )WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY],
   UNIQUE NONCLUSTERED 
   (
   	[UserName] ASC
   )WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
   ) ON [PRIMARY]
   GO
   
   

3. Post completion of the SQL work now we need to write out a C# application which will import the data from JIRA and persist it to the above created SQL tables, and continually keep them updated with the changes in the portal

   JiraDataDownloaderCSharp (GitHub)

   Note: Remember the step 1, where we created the API token on the JIRA portal in the C# code above it acts as the password field, not your account actual password for JIRA login.

4. Once the above steps are completed successfully you must be able to see data getting populated in the tables

   

5. Now as the data is available to us, we can go ahead create any fancy report in any of the reporting applications like SSRS/Qlikview/QlikSense/Power BI and more.

   

Hope the above helps.