[JIRA] Finding issues that contain LucidChart diagrams

One of the coolest JIRA add-ons you could find in the market is LucidChart. LucidChart is an easy-to-use diagramming software. However, once you start using it in your JIRA, it stores diagrams in it’s own way.

Have you ever come across a requirement to find all issues in your JIRA instance that contains LucidChart diagrams? Well, I’m on JIRA 6.x and currently there’s no JQL function that I know to help with this. At least we have SQL – so JIRA administrator can look up if that’s really important.

So, here’s the fish. All you have to do is to set up a database client to access your JIRA’s database and fire up this query.

select distinct p.pkey || '-' || i.issuenum
from propertyentry pe
inner join fileattachment fa
  on pe.entity_id = fa.id
inner join jiraissue i
  on fa.issueid = i.id
inner join project p
  on i.project = p.id
where
  pe.property_key = 'lucidchart.attachment.id';

Simple as that! Then, here’s how to fish.

There was no official documentation telling you how to do this. So how do we find the relation and database tables that contain LucidChart diagram data? Following the guesses is the answer.

First, I did a query like this to find if the word ‘lucidchart’ is mentioned anywhere in the database. In my case, the database was Oracle 11g.

select 
'select ' || column_name || ', ''' || table_name || ''', ''' || column_name || ''' from ' || table_name || ' where lower(' || column_name || ') like ''%lucidchart%'' union '
from user_tab_columns
where data_type = 'VARCHAR2' order by column_name asc;

It will produce something with many rows like the following:

select ACCESS_LEVEL, 'MANAGEDCONFIGURATIONITEM', 'ACCESS_LEVEL' from MANAGEDCONFIGURATIONITEM where lower(ACCESS_LEVEL) like '%lucidchart%' union

And once you get that, you can remove unwanted texts and build up a BIG SQL query with a lot of UNION statements. You will also have to cleanup it a bit because some SELECT statements contain Oracle reserved words. Just delete those lines.

Finally, execute and there are few interesting lines that read;

com.lucidchart.jira.plugins.oauth.secret    PROPERTYENTRY    PROPERTY_KEY

That appears to be what we are looking for..!

So just query the PROPERTYENTRY table, and there you will discover the relation between the PROPERTYENTRY.ENTITY_ID and FILEATTACHMENT.ID. There you go and violah!

And that’s how I found it out without any help.

Thanks for reading!

 

Advertisements

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s