Release Automation

Expand all | Collapse all

How do I Create Process Duration and Deployment Duration Parameters?

Jump to Best Answer
  • 1.  How do I Create Process Duration and Deployment Duration Parameters?

    Posted 07-08-2015 11:42 AM

    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? 



  • 2.  Re: How do I Create Process Duration and Deployment Duration Parameters?
    Best Answer

    Posted 07-09-2015 05:05 AM

    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)

    deploymentstep info.png

    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 



  • 3.  Re: How do I Create Process Duration and Deployment Duration Parameters?

    Broadcom Employee
    Posted 07-09-2015 09:10 AM

    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.



  • 4.  Re: How do I Create Process Duration and Deployment Duration Parameters?

    Posted 07-09-2015 09:50 AM

    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!



  • 5.  Re: How do I Create Process Duration and Deployment Duration Parameters?

    Posted 07-09-2015 10:24 AM

    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



  • 6.  Re: How do I Create Process Duration and Deployment Duration Parameters?

    Posted 07-09-2015 12:25 PM

    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



  • 7.  Re: How do I Create Process Duration and Deployment Duration Parameters?

    Posted 07-09-2015 10:34 AM

    This is built-in in mysql.

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



  • 8.  Re: How do I Create Process Duration and Deployment Duration Parameters?

    Posted 07-09-2015 12:25 PM

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

     

    Thank you,

     

    -Chris Farrar



  • 9.  Re: How do I Create Process Duration and Deployment Duration Parameters?

    Posted 07-16-2015 09:11 AM

    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.