{"id":3308,"date":"2012-10-14T15:31:35","date_gmt":"2012-10-14T13:31:35","guid":{"rendered":"http:\/\/contoso.se\/blog\/?p=3308"},"modified":"2012-10-14T15:31:35","modified_gmt":"2012-10-14T13:31:35","slug":"sql-query-to-show-last-result-for-each-runbook","status":"publish","type":"post","link":"http:\/\/contoso.se\/blog\/?p=3308","title":{"rendered":"SQL query to show last result for each runbook"},"content":{"rendered":"<p>The following SQL query will show you last result for each runbook. The query will also show you when it started and ended, and at which runbook server.<\/p>\n<blockquote><p>SELECT PI.Status, POLICIES.Name, PI.TimeEnded, PI.TimeStarted, PI.Computer<br \/>\nFROM POLICIES<br \/>\nINNER JOIN<br \/>\n(SELECT PI1.PolicyID, PI1.TimeStarted, PI1.TimeEnded, PI1.Status,<br \/>\nACTIONSERVERS.Computer<br \/>\nFROM PolicyInstances AS PI1<br \/>\nINNER JOIN ACTIONSERVERS ON PI1.ActionServer = ACTIONSERVERS.UniqueID<br \/>\nWHERE PI1.TimeEnded = (SELECT MAX(PI2.TimeEnded) FROM PolicyInstances AS PI2 WHERE PI2.PolicyID = PI1.PolicyID)<br \/>\n) AS PI ON PI.PolicyID = Policies.UniqueID<br \/>\nWHERE (POLICIES.Deleted = 0) AND (POLICIES.CheckOutUser IS NULL)<\/p><\/blockquote>\n<p>a example of the result<\/p>\n<p><a href=\"http:\/\/contoso.se\/blog\/wp-content\/uploads\/2012\/10\/20121014_blog_01.jpg\"><img decoding=\"async\" loading=\"lazy\" class=\"aligncenter size-medium wp-image-3309\" title=\"20121014_blog_01\" src=\"http:\/\/contoso.se\/blog\/wp-content\/uploads\/2012\/10\/20121014_blog_01-300x105.jpg\" alt=\"\" width=\"300\" height=\"105\" srcset=\"http:\/\/contoso.se\/blog\/wp-content\/uploads\/2012\/10\/20121014_blog_01-300x105.jpg 300w, http:\/\/contoso.se\/blog\/wp-content\/uploads\/2012\/10\/20121014_blog_01.jpg 600w\" sizes=\"(max-width: 300px) 100vw, 300px\" \/><\/a><\/p>\n","protected":false},"excerpt":{"rendered":"<p>The following SQL query will show you last result for each runbook. The query will also show you when it started and ended, and at which runbook server. SELECT PI.Status, POLICIES.Name, PI.TimeEnded, PI.TimeStarted, PI.Computer FROM POLICIES INNER JOIN (SELECT PI1.PolicyID, PI1.TimeStarted, PI1.TimeEnded, PI1.Status, ACTIONSERVERS.Computer FROM PolicyInstances AS PI1 INNER JOIN ACTIONSERVERS ON PI1.ActionServer = ACTIONSERVERS.UniqueID &hellip; <a href=\"http:\/\/contoso.se\/blog\/?p=3308\">Continue reading <span class=\"meta-nav\">&rarr;<\/span><\/a><\/p>\n","protected":false},"author":1,"featured_media":0,"comment_status":"open","ping_status":"open","sticky":false,"template":"","format":"standard","meta":{"_monsterinsights_skip_tracking":false,"_monsterinsights_sitenote_active":false,"_monsterinsights_sitenote_note":"","_monsterinsights_sitenote_category":0},"categories":[60],"tags":[],"_links":{"self":[{"href":"http:\/\/contoso.se\/blog\/index.php?rest_route=\/wp\/v2\/posts\/3308"}],"collection":[{"href":"http:\/\/contoso.se\/blog\/index.php?rest_route=\/wp\/v2\/posts"}],"about":[{"href":"http:\/\/contoso.se\/blog\/index.php?rest_route=\/wp\/v2\/types\/post"}],"author":[{"embeddable":true,"href":"http:\/\/contoso.se\/blog\/index.php?rest_route=\/wp\/v2\/users\/1"}],"replies":[{"embeddable":true,"href":"http:\/\/contoso.se\/blog\/index.php?rest_route=%2Fwp%2Fv2%2Fcomments&post=3308"}],"version-history":[{"count":2,"href":"http:\/\/contoso.se\/blog\/index.php?rest_route=\/wp\/v2\/posts\/3308\/revisions"}],"predecessor-version":[{"id":3311,"href":"http:\/\/contoso.se\/blog\/index.php?rest_route=\/wp\/v2\/posts\/3308\/revisions\/3311"}],"wp:attachment":[{"href":"http:\/\/contoso.se\/blog\/index.php?rest_route=%2Fwp%2Fv2%2Fmedia&parent=3308"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"http:\/\/contoso.se\/blog\/index.php?rest_route=%2Fwp%2Fv2%2Fcategories&post=3308"},{"taxonomy":"post_tag","embeddable":true,"href":"http:\/\/contoso.se\/blog\/index.php?rest_route=%2Fwp%2Fv2%2Ftags&post=3308"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}