Checking Plugin Usage in Post-Functions and Custom Fields

[#1]

The Atlassian ecosystem is one of the great features of using Atlassian tools. There are thousands of apps created by third-part developers that implement solutions for an almost unlimited number of business needs. This is a huge strength for Atlassian because no single vendor can satisfy every customer need. That is where third party developers come in, filling the gaps and extending the capabilities to meet both broad and narrow needs.

For a Jira admin, though, this can create an administrative nightmare. How do you know which plugins are used and which ones are languishing unused. When it comes time to renew your licenses, how do you know which plugins should be renewed and which ones should be removed from the system?

Fortunately, for Jira Server, there is a way you can find out which plugins are used to support workflows in the form of conditions, validators and post-functions. You will need access to the database. If you have SQL access, then you are good to go. If you are in an environment where direct SQL access is restricted to the DBA group, there is a free plugin option called Home Directory and DB Browser that you should consider installing. (Note: I have no affiliation with AtlaZon who publishes this plugin). The following screen shots use this plugin.

The SQL that you will need is

select a.pluginname, count (b.id) as Count
from pluginversion a
Inner join jiraworkflows b 
on b.descriptor like concat('%', a.pluginkey, '%')
where a.pluginkey not like 'com.atlassian%'
AND a.pluginkey not like 'jira.%'
AND a.pluginkey not like 'tac.%'
AND a.pluginkey not like 'crowd*'
AND a.pluginkey not like 'org.apache%'
AND a.pluginkey not like 'io.atlassian%'
AND a.pluginkey not like 'com.springsource%'
AND a.pluginkey not like 'org.osgi%'
AND a.pluginkey not like 'com.pyxis%'
AND a.pluginkey not like 'crowd%'
AND a.pluginkey not like 'system%'
GROUP BY a.pluginname
ORDER BY a.pluginname

This SQL statement joins the workflow table to the pluginversion table. it returns a count of how many workflows each plugin is used in. If a plugin is not used in any workflow, nothing will be returned. This should allow you to see what your usage is and whether there are plugins that are workflow oriented that are candidates to be removed.

In my environment, I get the following list. Yours will differ according to what you have installed

You can do the same thing for Custom Fields. Here is that SQL:

select cfname, customfieldtypekey from customfield a
WHERE a.customfieldtypekey not like 'com.atlassian%'
AND a.customfieldtypekey not like 'jira.%'
AND a.customfieldtypekey not like 'tac.%'
AND a.customfieldtypekey not like 'crowd*'
AND a.customfieldtypekey not like 'org.apache%'
AND a.customfieldtypekey not like 'io.atlassian%'
AND a.customfieldtypekey not like 'com.springsource%'
AND a.customfieldtypekey not like 'org.osgi%'
AND a.customfieldtypekey not like 'com.pyxis%'
AND a.customfieldtypekey not like 'crowd%'
AND a.customfieldtypekey not like 'system%'
ORDER BY cfname

Using these two SQL statements should help you to identify which plugins are essential in your workflow and custom field environments.

[Matt Doar]

Very useful, thank you!

For us, JSU is the major plugin for workflows (148), followed by ScriptRunner and JMWE

For custom field types, we have 4 from custom plugins and 4 from ScriptRunner scripted fields.

The first query needed a tweak for MySQL, changing "count (b.id)" to "count(b.id)"

[Taranjeet Singh]

@C_ Derek Fields Very useful information! Thanks for sharing.

[Ryan Haney [Adaptavist]]

Definitely super useful - thanks for sharing. 

FYI: Regarding the DB access to execute the above SQL query, you can actually run it right from the ScriptRunner built in script called "Local Database Connection" w/ "local" as the Pool Name:

[Robert Banister]

Hello - I tried running this through scriptrunner as well as in the database, but I am getting the following error:

FUNCTION jiradb.count does not exist. Check the 'Function Name Parsing and Resolution' section in the Reference Manual

Jira ver 8.5.4

MySQL Ver 14.14 Distrib 5.7.25

[FuXing]

@Robert Banister Remove the space after ‘count‘.

select a.pluginname, count(b.id) as Count

[C_ Derek Fields]

Quick update - the plugin that I mentioned is no longer free. It is a paid app. 

[Radha Krishna]

I tried but I got ORA-00909 invalid number of arguments I got this error

[Rakshith A]

The pluginkey  in pluginversion table, is not the same used in the workflow. So this query wont be allways correct.

Example 

• For Scriptrunner In the pluginversion table the plugin key is com.onresolve.jira.groovy.groovyrunner but the class name in the actual workflow is com.onresolve.jira.groovy.GroovyValidator
• Similarly noticed for others like JSU, JMWE

Each script will have different class name, some info on this is here https://community.atlassian.com/t5/Adaptavist-questions/Script-Runner-Workflow-Validator-Inline-Script-which-Table-in/qaq-p/2100809

Can search partial script text with https://community.atlassian.com/t5/Adaptavist-questions/Sql-query-to-get-active-workflows-from-all-projects-in-Jira/qaq-p/590713