{"id":607,"date":"2016-10-04T04:32:02","date_gmt":"2016-10-04T04:32:02","guid":{"rendered":"http:\/\/oranotes.tuktaroff.com\/?page_id=607"},"modified":"2016-10-04T04:34:49","modified_gmt":"2016-10-04T04:34:49","slug":"job-history","status":"publish","type":"page","link":"http:\/\/oranotes.tuktaroff.com\/?page_id=607","title":{"rendered":"Job history"},"content":{"rendered":"<p><code><br \/>\n<script type=\"text\/javascript\"> \nfunction copy(what) {\n    window.clipboardData.setData('Text',what.value);\n}\nfunction paste(what) {\n    what.value = window.clipboardData.getData('Text');\n}\n<\/script><\/p>\n<h2>\nJob history<br \/>\n<\/h2>\n<pre class=\"qcode\">\r\nselect job_name, run_datetime, run_duration\r\nfrom\r\n(\r\n    select job_name, run_datetime,\r\n        SUBSTRING(run_duration, 1, 2) + ':' + SUBSTRING(run_duration, 3, 2) + ':' +\r\n        SUBSTRING(run_duration, 5, 2) AS run_duration\r\n    from\r\n    (\r\n        select DISTINCT\r\n            j.name as job_name, \r\n            run_datetime = CONVERT(DATETIME, RTRIM(run_date)) +  \r\n                (run_time * 9 + run_time % 10000 * 6 + run_time % 100 * 10) \/ 216e4,\r\n            run_duration = RIGHT('000000' + CONVERT(varchar(6), run_duration), 6)\r\n        from msdb.dbo.sysjobhistory h\r\n        inner join msdb.dbo.sysjobs j\r\n        on h.job_id = j.job_id\r\n    ) t\r\n) t\r\norder by run_datetime desc;\r\n<\/pre>\n<form action=\"\"><textarea name=\"data\" cols=\"50\" rows=\"10\" style=\"display:none;\"><br \/>\nselect job_name, run_datetime, run_duration<br \/>\nfrom<br \/>\n(<br \/>\n    select job_name, run_datetime,<br \/>\n        SUBSTRING(run_duration, 1, 2) + ':' + SUBSTRING(run_duration, 3, 2) + ':' +<br \/>\n        SUBSTRING(run_duration, 5, 2) AS run_duration<br \/>\n    from<br \/>\n    (<br \/>\n        select DISTINCT<br \/>\n            j.name as job_name,<br \/>\n            run_datetime = CONVERT(DATETIME, RTRIM(run_date)) +<br \/>\n                (run_time * 9 + run_time % 10000 * 6 + run_time % 100 * 10) \/ 216e4,<br \/>\n            run_duration = RIGHT('000000' + CONVERT(varchar(6), run_duration), 6)<br \/>\n        from msdb.dbo.sysjobhistory h<br \/>\n        inner join msdb.dbo.sysjobs j<br \/>\n        on h.job_id = j.job_id<br \/>\n    ) t<br \/>\n) t<br \/>\norder by run_datetime desc;<br \/>\n<\/textarea><input type=\"button\" value=\"Clipboard\" onclick=\"copy(this.form.data)\"><\/form>\n<h2>\nJob history (detailed)<br \/>\n<\/h2>\n<pre class=\"qcode\">\r\nSELECT --sysjobhistory.server,\r\n             sysjobs.name\r\n             AS\r\n             job_name,\r\n             CASE sysjobhistory.run_status\r\n               WHEN 0 THEN 'Failed'\r\n               WHEN 1 THEN 'Succeeded'\r\n               ELSE '???'\r\n             END\r\n             AS\r\n             run_status,\r\n             CAST(\r\n             Isnull(Substring(CONVERT(VARCHAR(8), run_date), 1, 4) + '-' +\r\n                           Substring(CONVERT(VARCHAR\r\n                                     (8), run_date), 5, 2) + '-' +\r\n                    Substring(CONVERT(VARCHAR(\r\n                              8), run_date), 7, 2), '') AS DATETIME)\r\n             AS\r\n             [Run DATE],\r\n\r\n             Isnull(Substring(CONVERT(VARCHAR(7), run_time+1000000), 2, 2) + ':'\r\n                     +\r\n                           Substring(CONVERT(VARCHAR(7), run_time+1000000), 4, 2\r\n                            )\r\n                    +\r\n                    ':' +\r\n                    Substring(CONVERT(VARCHAR(7), run_time+1000000), 6, 2), '') \r\n             AS\r\n             [Run TIME],\r\n             Isnull(Substring(CONVERT(VARCHAR(7), run_duration+1000000), 2, 2) +\r\n                     ':' +\r\n                           Substring(CONVERT(VARCHAR(7), run_duration+1000000),\r\n                           4,\r\n                           2)\r\n                    + ':' +\r\n                    Substring(CONVERT(VARCHAR(7), run_duration+1000000), 6, 2),\r\n             ''\r\n             ) AS\r\n             [Duration],\r\n             Isnull(Substring(CONVERT(VARCHAR(7), run_time+run_duration+1000000), 2, 2) + ':'\r\n                     +\r\n                           Substring(CONVERT(VARCHAR(7), run_time+run_duration+1000000), 4, 2\r\n                            )\r\n                    +\r\n                    ':' +\r\n                    Substring(CONVERT(VARCHAR(7), run_time+run_duration+1000000), 6, 2), '')\r\n             AS\r\n             [Total TIME],             \r\n             sysjobhistory.step_id,\r\n             sysjobhistory.step_name,\r\n             sysjobhistory.MESSAGE AS Msg\r\n      FROM   msdb.dbo.sysjobhistory\r\n             INNER JOIN msdb.dbo.sysjobs\r\n               ON msdb.dbo.sysjobhistory.job_id = msdb.dbo.sysjobs.job_id\r\n\t\t\t   order by run_date desc;\r\n<\/pre>\n<form action=\"\"><textarea name=\"data\" cols=\"50\" rows=\"10\" style=\"display:none;\"><br \/>\nSELECT --sysjobhistory.server,<br \/>\n             sysjobs.name<br \/>\n             AS<br \/>\n             job_name,<br \/>\n             CASE sysjobhistory.run_status<br \/>\n               WHEN 0 THEN 'Failed'<br \/>\n               WHEN 1 THEN 'Succeeded'<br \/>\n               ELSE '???'<br \/>\n             END<br \/>\n             AS<br \/>\n             run_status,<br \/>\n             CAST(<br \/>\n             Isnull(Substring(CONVERT(VARCHAR(8), run_date), 1, 4) + '-' +<br \/>\n                           Substring(CONVERT(VARCHAR<br \/>\n                                     (8), run_date), 5, 2) + '-' +<br \/>\n                    Substring(CONVERT(VARCHAR(<br \/>\n                              8), run_date), 7, 2), '') AS DATETIME)<br \/>\n             AS<br \/>\n             [Run DATE],<\/p>\n<p>             Isnull(Substring(CONVERT(VARCHAR(7), run_time+1000000), 2, 2) + ':'<br \/>\n                     +<br \/>\n                           Substring(CONVERT(VARCHAR(7), run_time+1000000), 4, 2<br \/>\n                            )<br \/>\n                    +<br \/>\n                    ':' +<br \/>\n                    Substring(CONVERT(VARCHAR(7), run_time+1000000), 6, 2), '')<br \/>\n             AS<br \/>\n             [Run TIME],<br \/>\n             Isnull(Substring(CONVERT(VARCHAR(7), run_duration+1000000), 2, 2) +<br \/>\n                     ':' +<br \/>\n                           Substring(CONVERT(VARCHAR(7), run_duration+1000000),<br \/>\n                           4,<br \/>\n                           2)<br \/>\n                    + ':' +<br \/>\n                    Substring(CONVERT(VARCHAR(7), run_duration+1000000), 6, 2),<br \/>\n             ''<br \/>\n             ) AS<br \/>\n             [Duration],<br \/>\n             Isnull(Substring(CONVERT(VARCHAR(7), run_time+run_duration+1000000), 2, 2) + ':'<br \/>\n                     +<br \/>\n                           Substring(CONVERT(VARCHAR(7), run_time+run_duration+1000000), 4, 2<br \/>\n                            )<br \/>\n                    +<br \/>\n                    ':' +<br \/>\n                    Substring(CONVERT(VARCHAR(7), run_time+run_duration+1000000), 6, 2), '')<br \/>\n             AS<br \/>\n             [Total TIME],<br \/>\n             sysjobhistory.step_id,<br \/>\n             sysjobhistory.step_name,<br \/>\n             sysjobhistory.MESSAGE AS Msg<br \/>\n      FROM   msdb.dbo.sysjobhistory<br \/>\n             INNER JOIN msdb.dbo.sysjobs<br \/>\n               ON msdb.dbo.sysjobhistory.job_id = msdb.dbo.sysjobs.job_id<br \/>\n\t\t\t   order by run_date desc;<br \/>\n<\/textarea><input type=\"button\" value=\"Clipboard\" onclick=\"copy(this.form.data)\"><\/form>\n<p><\/code><\/p>\n","protected":false},"excerpt":{"rendered":"<p>Job history select job_name, run_datetime, run_duration from ( select job_name, run_datetime, SUBSTRING(run_duration, 1, 2) + &#8216;:&#8217; + SUBSTRING(run_duration, 3, 2) + &#8216;:&#8217; + SUBSTRING(run_duration, 5, 2) AS run_duration from ( select DISTINCT j.name as job_name, run_datetime = CONVERT(DATETIME, RTRIM(run_date)) + (run_time * 9 + run_time % 10000 * 6 + run_time % 100 * 10) [&hellip;]<\/p>\n","protected":false},"author":1,"featured_media":0,"parent":605,"menu_order":0,"comment_status":"closed","ping_status":"closed","template":"","meta":{"footnotes":""},"class_list":["post-607","page","type-page","status-publish","hentry"],"jetpack_sharing_enabled":true,"jetpack_shortlink":"https:\/\/wp.me\/P2NVny-9N","_links":{"self":[{"href":"http:\/\/oranotes.tuktaroff.com\/index.php?rest_route=\/wp\/v2\/pages\/607","targetHints":{"allow":["GET"]}}],"collection":[{"href":"http:\/\/oranotes.tuktaroff.com\/index.php?rest_route=\/wp\/v2\/pages"}],"about":[{"href":"http:\/\/oranotes.tuktaroff.com\/index.php?rest_route=\/wp\/v2\/types\/page"}],"author":[{"embeddable":true,"href":"http:\/\/oranotes.tuktaroff.com\/index.php?rest_route=\/wp\/v2\/users\/1"}],"replies":[{"embeddable":true,"href":"http:\/\/oranotes.tuktaroff.com\/index.php?rest_route=%2Fwp%2Fv2%2Fcomments&post=607"}],"version-history":[{"count":3,"href":"http:\/\/oranotes.tuktaroff.com\/index.php?rest_route=\/wp\/v2\/pages\/607\/revisions"}],"predecessor-version":[{"id":610,"href":"http:\/\/oranotes.tuktaroff.com\/index.php?rest_route=\/wp\/v2\/pages\/607\/revisions\/610"}],"up":[{"embeddable":true,"href":"http:\/\/oranotes.tuktaroff.com\/index.php?rest_route=\/wp\/v2\/pages\/605"}],"wp:attachment":[{"href":"http:\/\/oranotes.tuktaroff.com\/index.php?rest_route=%2Fwp%2Fv2%2Fmedia&parent=607"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}