How to identify Workflow Diagram in Jira database

Still need help?

The Atlassian Community is here for you.

Ask the community

When a workflow is created:

  • its XML data are stored in descriptor column of table jiraworkflows, together with the workflow name (workflowname column) and id
  • its Diagram is stored in table propertytext, referred to by column property_key of table propertyentry
  • column property_key of table propertyentry however doesn't signify which workflow it is in an easy way

This article discusses how to identify which Diagram belongs to which workflow

tip/resting Created with Sketch.

JIRA uses md5 to encode the workflow names to be used in column property_key of table propertyentry. We can use md5 Hash Generator to get the md5 hashcode of a workflow.

Step-by-step guide

  1. Run the following SQL query to get the md5 hashcode of a workflow based on the workflow name:

    select md5(workflowname) from jiraworkflows 
    	where workflowname = 'JIRA MD5 Workflow';

    Please replace the 'JIRA MD5 Workflow' with the actual name of your workflow. The md5 hashcode returned by the query looks like "a5f6ec9e0aaa7ed764a8f1a58d4b95ab" (actual value would vary based on the workflow name supplied).

  2. Run the following query to get the Diagram (replace the hashcode accordingly):

    select 
      pe.id, 
      pe.property_key, 
      pt.propertyvalue 
    from 
      propertyentry pe 
      inner join propertytext pt on pe.id = pt.id 
    where 
      entity_name like '%workflow%' 
      and property_key like '%<md5 value>';

    Replace <md5 value> with the actual value returned by the previous query. Please make sure to put the '%' sign before the md5 hashcode value in the query, since the value at property_key has similar to the following format "jira.workflow.layout:a5f6ec9e0aaa7ed764a8f1a58d4b95ab"

Last modified on Nov 14, 2022

Was this helpful?

Yes
No
Provide feedback about this article
Powered by Confluence and Scroll Viewport.