Purpose

Sometimes, Jira admins would like to know which SLAs and their conditions are being associated with JSM projects. This guide provides SQL queries to identify those SLAs, conditions and associated projects.

Solution

•  Run the following SQL queries that will list SLA conditions (PAUSE, START, STOP), SLA names and associated project name:

  SELECT mc."FACTORY_KEY", mc."TYPE_NAME", tm."NAME", p.pname  FROM "AO_54307E_METRICCONDITION" mc
  join "AO_54307E_TIMEMETRIC" tm on mc."TIME_METRIC_ID" = tm."ID"
  join "AO_54307E_SERVICEDESK" sd on tm."SERVICE_DESK_ID" = sd."ID"
  join project p on sd."PROJECT_ID" = p.id

  This SQL query was written to PostgreSQL and it may differ for other database types.

• Example result (for the sake of simplicity, only one project is listed here):

  |FACTORY_KEY                        |TYPE_NAME|NAME                          |pname          |
  |-----------------------------------|---------|------------------------------|---------------|
  |issue-created-sla-condition-factory|Start    |Time to resolution            |IT Service Desk|
  |resolution-sla-condition-factory   |Start    |Time to resolution            |IT Service Desk|
  |resolution-sla-condition-factory   |Stop     |Time to resolution            |IT Service Desk|
  |issue-created-sla-condition-factory|Start    |Time to first response        |IT Service Desk|
  |resolution-sla-condition-factory   |Stop     |Time to first response        |IT Service Desk|
  |comment-sla-condition-factory      |Stop     |Time to first response        |IT Service Desk|
  |status-sla-condition-factory       |Stop     |Time to first response        |IT Service Desk|
  |resolution-sla-condition-factory   |Start    |Time to close after resolution|IT Service Desk|
  |resolution-sla-condition-factory   |Stop     |Time to close after resolution|IT Service Desk|
  |status-sla-condition-factory       |Stop     |Time to close after resolution|IT Service Desk|
  |status-sla-condition-factory       |Start    |Time to approve normal change |IT Service Desk|
  |status-sla-condition-factory       |Start    |Time to approve normal change |IT Service Desk|
  |status-sla-condition-factory       |Stop     |Time to approve normal change |IT Service Desk|
  |status-sla-condition-factory       |Stop     |Time to approve normal change |IT Service Desk|
  |status-sla-condition-factory       |Stop     |Time to approve normal change |IT Service Desk|
  |status-sla-condition-factory       |Stop     |Time to approve normal change |IT Service Desk|
  |status-sla-condition-factory       |Stop     |Time to approve normal change |IT Service Desk|
  |status-sla-condition-factory       |Stop     |Time to approve normal change |IT Service Desk|
  |status-sla-condition-factory       |Stop     |Time to approve normal change |IT Service Desk|
  |status-sla-condition-factory       |Stop     |Time to approve normal change |IT Service Desk|
  |status-sla-condition-factory       |Pause    |New sla                       |IT Service Desk|
  |assignee-sla-condition-factory     |Pause    |New sla                       |IT Service Desk|
  |status-sla-condition-factory       |Stop     |New sla                       |IT Service Desk|
  |issue-created-sla-condition-factory|Start    |New sla                       |IT Service Desk|
  |status-sla-condition-factory       |Start    |New sla                       |IT Service Desk|