How do I Create Process Duration and Deployment Duration Parameters?

[Christopher Farrar]

We currently send out an automatically generated email with deployment start time and deployment finish time as part of our regular deployments.  I want to include the duration for each process and the total deployment duration in the "deployment complete" email.  I can access all of this information manually in the "Offline Process Report" from the dashboard (5.5.1) but I cannot figure out where these values are stored so that I can get them set to parameters to include in my email.  Does anyone do anything like this that could share how they are currently making it work? 

[Jacky_Mahadab]

Hi

The following query will provide start/stop/duration time for each step in your deployment

 

use nolio_db55;

select rcbm.name as step_name,oej.start_time as start_time ,oej.end_time as end_time ,unix_timestamp(oej.end_time)-unix_timestamp(oej.start_time)as duration_time_in_sec from rc_stages rcs

  inner join rc_releases rcr on rcr.id=rcs.release_id

  inner join rc_modules rcm on rcm.stage_id=rcs.id

  inner join rc_basic_modules rcbm on rcbm.id=rcm.id

  inner join offline_execution_jobs oej on oej.id=rcm.job_id

where rcr.id = DEPLOYMENT_ID (You can use the release_id built in parameter)

 

Output from my system (MySQL DB)

One thing to keep in mind is that the offline execution jobs table will be updated only after the job finished (Before that it will be reside in the execution job table) so if you running the query while your deployment is running you need to:

a. check also in execution job table

b. wait until all steps move to offline execution job table

 

Thanks

Jacky 

[Gregg Stewart]

Very nice, Jacky. I spoke with CJ regarding this and pointed him towards the Execution_Jobs and Offline_Execution_Jobs tables. But didn't have a specific query. Your query cuts right to the chase.

[Christopher Farrar]

Jacky,

 

Thank you for the reply, when I run the query that you suggested, I receive an error stating the following:

 

Msg 195, Level 15, State 10, Line 1

'unix_timestamp' is not a recognized built-in function name.

 

Is this some sort of custom function that you have installed?  Working with the Unix timestamp is the current obstacle that I am facing anyway so I'm curious how you convert it to "seconds".

 

Thank you!

[subra07]

CJ, Try this and let me know.

 

SELECT RCBM.NAME AS STEP_NAME, OEJ.START_TIME AS START_TIME, OEJ.END_TIME AS END_TIME,

  TO_NUMBER(TO_CHAR(OEJ.END_TIME, 'YYYYMMDDHH24MISSFF')) - TO_NUMBER(TO_CHAR(OEJ.START_TIME, 'YYYYMMDDHH24MISSFF')) AS DURATION_TIME_IN_SEC

  FROM RC_STAGES RCS

  INNER JOIN RC_RELEASES RCR ON RCR.ID=RCS.RELEASE_ID

  INNER JOIN RC_MODULES RCM ON RCM.STAGE_ID=RCS.ID

  INNER JOIN RC_BASIC_MODULES RCBM ON RCBM.ID=RCM.ID

  INNER JOIN OFFLINE_EXECUTION_JOBS OEJ ON OEJ.ID=RCM.JOB_ID

[Christopher Farrar]

Subra,

 

I get the same error but with ‘TO_CHAR’:

 

Msg 195, Level 15, State 10, Line 2

'TO_CHAR' is not a recognized built-in function name.

 

I am using SQLServer 2012 as my DB.

 

Thank you,

 

-Chris Farrar

[Jacky_Mahadab]

This is built-in in mysql.

If you use other DB type check how to convert date to INT.

[Christopher Farrar]

I will look into that, thank you for the suggestion.

 

Thank you,

 

-Chris Farrar

[Christopher Farrar]

I found a solution to this issue and I just want to follow up with the initial post so that it is documented.  First off, thank you to Subra and Greg, I did not use your exact solution or query but it pointed me in the right direction to come up with a suitable solution for our environment.  By running the following query, I was able to gather the required fields from the "execution_jobs" table and the "offline_execution_jobs table" and then diff the start time and end time to come up with a duration field. 

 

SELECT 'offline_execution_jobs' as names,

       process_name as 'Process Name'

       ,start_time as 'Start Time'

       ,end_time as 'End Time'

       ,DATEDIFF (ss, start_time, end_time) Seconds

       ,RIGHT('0' + CAST(DATEDIFF (ss, start_time, end_time) / 3600 AS VARCHAR),2) + ':' +

              RIGHT('0' + CAST((DATEDIFF (ss, start_time, end_time) / 60) % 60 AS VARCHAR),2)  + ':' +

              RIGHT('0' + CAST(DATEDIFF (ss, start_time, end_time) % 60 AS VARCHAR),2) Duration

       FROM [PPReleaseDB].[dbo].[offline_execution_jobs] (NOLOCK)

          WHERE minor= @minor--   Finished'

          AND release_name= @releasename

 

 

UNION ALL

 

 

SELECT 'execution_jobs' as names,

       process_name as 'Process Name'

       ,start_time as 'Start Time'

       ,end_time as 'End Time'

       ,DATEDIFF (ss, start_time, end_time) Seconds

       ,RIGHT('0' + CAST(DATEDIFF (ss, start_time, end_time) / 3600 AS VARCHAR),2) + ':' +

              RIGHT('0' + CAST((DATEDIFF (ss, start_time, end_time) / 60) % 60 AS VARCHAR),2)  + ':' +

              RIGHT('0' + CAST(DATEDIFF (ss, start_time, end_time) % 60 AS VARCHAR),2) Duration

       FROM [PPReleaseDB].[dbo].[execution_jobs] (NOLOCK)

          WHERE minor             = @minor

          AND release_name = @releasename

ORDER BY start_time ASC;

 

For version 1.0 of our plan, this will suffice.  We are currently utilizing the "Execute SQL Query using Microsoft SQL Server" action to run this query and output the results to a CSV file.  Following this is a simple action to send an email with this attachment.  For now, this provides the base functionality to report on the elements that we need.  For version 2.0, we plan on outputting the results to a result set and then parsing the results set for the parameters that we need to inject them into an HTML email.  This will be a much more aesthetic and elegant solution.  I will try to update this post once we actually do this to include the solution for that as well.  Thanks again everyone.