How to assess App or Plugin usage in Jira
Platform Notice: Data Center Only - This article only applies to Atlassian products on the Data Center platform.
Note that this KB was created for the Data Center version of the product. Data Center KBs for non-Data-Center-specific features may also work for Server versions of the product, however they have not been tested. Support for Server* products ended on February 15th 2024. If you are running a Server product, you can visit the Atlassian Server end of support announcement to review your migration options.
*Except Fisheye and Crucible
Summary
Admins often need to know how much a certain App (aka Plugin) is being used in the instance and there isn't a single way to assess that, given the many ways an App can work in Jira.
This article provides some additional checks Admins can perform to broaden the search scope, but still the advises offered here are not the source of truth.
Atlassian also provides this free App through the Marketplace that can be used through Jira's admin UI: App Usage for Jira
Environment
Jira Software or Jira Service Management Data Center
Solution
Here are some of the most common places an App can be used in Jira:
Custom Fields
Workflows
Dashboards
Issue Types
Filters
Automation Rules
Learning the Plugin Key
To look for places where the Apps are used, we first need to learn their "Plugin Key".
This can be found on each Support Zip's application-properties/application.xml on the <plugins> section or through the DB query:
1
2
3
4
5
select
pluginname,
pluginkey
from pluginversion
order by pluginname asc;Some plugins, though, may not use the Plugin Key exactly as described here, but a variation of it. We may have to truncate the pluginkey to the domain only sometimes, like "com.pluginvendor" instead of "com.pluginvendor.plugin-identifier".
Custom Fields
We can learn which Field's provided by which App in Jira by following through this KB article:
Or running this ad-hoc query that scans for specific Plugin Keys you're after:
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
select
id as "Custom Field Id",
cfname as "Custom Field Name",
customfieldtypekey as "Custom Field Key"
from customfield
where
(
customfieldtypekey like 'com.atlassian.jira.ext.charting%'
OR
customfieldtypekey like 'com.atlassian.jira.ext.calendar%'
OR
customfieldtypekey like 'com.atlassian.jira.toolkit%'
OR
customfieldtypekey like 'nl.tweeenveertig%'
OR
customfieldtypekey like 'com.googlecode%'
)
order by cfname asc;We'd keep adding OR clauses to the Select for each Plugin Key we want to assess.
With the Custom Fields in hand you can assess their usage through Jira's UI or check in which screens and projects they're present through DB queries:
And also through advanced searches in Jira's UI like this, to list all Issues created in the past 6 months that the field is not empty, for example:
1
created >= startOfMonth("-6") and "Custom Field Name" is not emptyWorkflows
Apps can also provide Conditions, Validations and Post-Functions to Workflows. This DB query should bring the Workflows that contain the Plugin Key in it's descriptor (the XML representation of the Workflow):
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
select
id as "Workflow Id",
workflowname as "Workflow Name"
from jiraworkflows
where
(
descriptor like '%>com.atlassian.jira.ext.charting%'
OR
descriptor like '%>com.atlassian.jira.ext.calendar%'
OR
descriptor like '%>com.atlassian.jira.toolkit%'
OR
descriptor like '%>nl.tweeenveertig%'
OR
descriptor like '%>com.googlecode%'
)
order by workflowname asc;Notice the like comparison matches anything on either end and starts with a closing tag: '%>plugin.key%'
For some Apps, you may need to truncate the Plugin Key. For example JSU — it's Plugin Key is com.googlecode.jira-suite-utilities but on several Workflow elements it's recorded as com.googlecode.jsu.
Dashboards
Plugins can also provide custom Gadgets to be used in Jira Dashboards. The below DB query should list them:
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
select distinct
p.id as "Dashboard Id",
p.pagename as "Dashboard Name",
c.gadget_xml as "Gadget XML"
from portalpage p
join portletconfiguration c on p.id = c.portalpage
where
(
c.gadget_xml like '%/com.atlassian.jira.ext.charting%'
OR
c.gadget_xml like '%/com.atlassian.jira.ext.calendar%'
OR
c.gadget_xml like '%/com.atlassian.jira.toolkit%'
OR
c.gadget_xml like '%/nl.tweeenveertig%'
OR
c.gadget_xml like '%/com.googlecode%'
)
order by p.pagename asc;Notice how each match to the Plugin Key is performed with wildcards on both ends and a starting slash: '$/plugin.key%'
The Gadget XML column is just to help identify the Plugin Key in use.
Issue Types
It's not too common, but some Apps may provide Issue Types. This should be well known by Admins and their usage could be assessed by JQL searches through Jira's UI, like:
1
type = TYPE_PROVIDED_BY_THE_APP and created >= startOfMonth("-6")Through the DB, a query on the issuetype table should help identify the Types provided by third party Apps:
1
2
3
4
5
6
select
id,
pname,
description,
iconurl
from issuetype;The Description and IconURL should hint to which App the Type's from. There's no assertive link to the Plugin Key like in the other topics.
Filters
Filters are the basis for Dashboard Gadgets (native to Jira and from third party Apps), Agile Boards (Kanban and Scrum), Subscriptions and more.
They're also the trickiest to assess the App usage, because Custom Field names can be referenced by name or Id and depending on the name we can have a lot of false-positives.
This query will list several filters and "subfilters" that the fields can be used (Filters and Boards "subfilters"):
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
with field as (
select
lower('%Custom Field Name%') as "cfname",
lower('%cf[99999]%') as "cfid"
),
filters as (
select
'Filter' as "type",
f.id as "id",
f.filtername as "name",
'' as "board",
f.reqcontent as "jql"
from searchrequest f
left join "AO_60DB71_RAPIDVIEW" b on b."SAVED_FILTER_ID" = f.id
join field on lower(f.reqcontent) like field.cfname or lower(f.reqcontent) like field.cfid
),
quickfilters as (
select
'Board' as "type",
b."ID" as "id",
q."NAME" as "name",
b."NAME" as "board",
'' as "jql"
from "AO_60DB71_QUICKFILTER" q
join "AO_60DB71_RAPIDVIEW" b on b."ID" = q."RAPID_VIEW_ID"
join field on lower(q."LONG_QUERY") like field.cfname or lower(q."LONG_QUERY") like field.cfid
),
subqueries as (
select
'Subquery' as "type",
b."ID" as "id",
'' as "name",
b."NAME" as "board",
sq."LONG_QUERY" as "jql"
from "AO_60DB71_SUBQUERY" sq
join "AO_60DB71_RAPIDVIEW" b on b."ID" = sq."RAPID_VIEW_ID"
join field on lower(sq."LONG_QUERY") like field.cfname or lower(sq."LONG_QUERY") like field.cfid
),
cards as (
select
'Card Color' as "type",
b."ID" as "id",
cc."COLOR" as "name",
b."NAME" as "board",
cc."VAL" as "jql"
from "AO_60DB71_CARDCOLOR" cc
join "AO_60DB71_RAPIDVIEW" b on b."ID" = cc."RAPID_VIEW_ID"
join field on lower(cc."VAL") like field.cfname OR lower(cc."VAL") like field.cfid
),
swimlanes as (
select
'Swimlane' as "type",
b."ID" as "id",
s."NAME" as "name",
b."NAME" as "board",
s."LONG_QUERY" as "jql"
from "AO_60DB71_SWIMLANE" s
join "AO_60DB71_RAPIDVIEW" b on b."ID" = s."RAPID_VIEW_ID"
join field on lower(s."LONG_QUERY") like field.cfname or lower(s."LONG_QUERY") like field.cfid
)
select * from filters
union
select * from quickfilters
union
select * from subqueries
union
select * from cards
union
select * from swimlanes
order by 1, 3;Replace the Custom Field Name and 99999 at the top of the query by the Name and Id of a Custom Field provided by the query on the Custom Fields section above.
Use the resulting jql column to validate if the search is indeed making use of said fields or if it's a false positive.
Automation Rules
Automation Rules can also make use of Custom Fields provided by third party apps. This query helps list the Rules that mentions fields in JQL search components:
1
2
3
4
5
select r."ID" as "Rule Id", r."NAME" as "Rule name", c."COMPONENT" as "Component name", c."TYPE" as "Component Type", c."VALUE" as "Component value"
from "AO_589059_RULE_CFG_COMPONENT" c
join "AO_589059_RULE_CONFIG" r on r."ID" = c."RULE_CONFIG_ID"
where (c."TYPE" like '%condition' or c."TYPE" like '%jql%')
and lower(c."VALUE") like lower('%Custom Field Name%') or lower(c."VALUE") like lower('%cf[99999]%');Was this helpful?