How to interpret the MTTR raw data (json) in the database?

Kilian July 31, 2018

Hello,

i want to do calculations on the MTTR (mean time to resolve) which cant be done by JQL so i searched the value in the DB and saw that it is stored as JSON (see below). Is there an explanation for this? Or are there examples on how to use it in an SQL?

{
   "timeline":{
      "events":[
         {
            "date":1508920800676,
            "types":[
               "START"
            ]
         },
         {
            "date":1508920959597,
            "types":[
               "PAUSE"
            ]
         }
      ]
   },
   "ongoingSLAData":{
      "goalId":86,
      "startTime":1508920800676,
      "paused":true,
      "thresholdData":{
         "calculatedAt":1508920959597,
         "remainingTime":25041079,
         "thresholdsConfigChangeDate":1507531971180,
         "thresholdsConfigChangeMsEpoch":1507531971180
      }
   },
   "completeSLAData":[

   ],
   "metricId":19,
   "definitionChangeDate":1507798977743,
   "definitionChangeMsEpoch":1507798977743,
   "goalsChangeDate":1508411143600,
   "goalsChangeMsEpoch":1508411143600,
   "goalTimeUpdatedDate":1507790885527,
   "goalTimeUpdatedMsEpoch":1507790885527,
   "metricCreatedDate":1507531971180,
   "updatedDate":1508920959862
}

 

2 answers

0 votes
jonas.bloeink June 9, 2020

Hi vmishovski,

you can use this query and then in the column "Textvlaue" is the JSON string:

select Top 10 
jiraissue.issuenum, customfield.cfname, customfieldvalue.TEXTVALUE
from jiraissue
inner join customfieldvalue on customfieldvalue.ISSUE = jiraissue.id
inner join customfield on customfield.id = customfieldvalue.CUSTOMFIELD
where cfname like 'MTTR'
;
0 votes
vmishovski June 5, 2020

Hello @Kilian would you share how did you extracted the above?

Suggest an answer

Log in or Sign up to answer
TAGS
AUG Leaders

Atlassian Community Events