Automation For Jira - Identifying the number rule execution over the past 30 days
Platform Notice: Data Center - This article applies to Atlassian products on the Data Center platform.
Note that this knowledge base article was created for the Data Center version of the product. Data Center knowledge base articles 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
Automation For Jira comes with the performance insight feature which provides an overview of your rules' performance by allowing you to view the execution count and duration. The problem with this feature is that it only shows the execution count for the top 20 automation rules.
The purpose of this to provide an SQL query that will return the number of automation rules executed over the last 30 days in Automation for Jira, and split them depending on their execution status (SUCCESS vs NO ACTIONS PERFORMED).
Environment
Any version of Jira Data Center, Jira Software or Jira Service Management, with Automation for Jira bundled or installed as a user installed app.
Solution
You may run the SQL query below in the Jira Database, which was only written and tested for PostgreSQL:
select
AI."OBJECT_ITEM_NAME" as Rule_Name
,sum(CASE WHEN AI."CATEGORY" = 'SUCCESS' THEN 1 ELSE 0 END) AS Successful_Executions
,sum(CASE WHEN AI."CATEGORY" = 'NO_ACTIONS_PERFORMED' THEN 1 ELSE 0 END) AS No_Actions_Performed
from "AO_589059_AUDIT_ITEM" AI
join "AO_589059_AUDIT_ITEM_ASC_ITEM" AIAI on AI."ID" = AIAI."AUDIT_ITEM_ID"
where 1=1
AND AI."CATEGORY" != 'CONFIG_CHANGE'
AND AI."CREATED" >= NOW() - INTERVAL '30 day'
group by AI."OBJECT_ITEM_NAME";