SQL query to show last result for each runbook

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
WHERE PI1.TimeEnded = (SELECT MAX(PI2.TimeEnded) FROM PolicyInstances AS PI2 WHERE PI2.PolicyID = PI1.PolicyID)
) AS PI ON PI.PolicyID = Policies.UniqueID
WHERE (POLICIES.Deleted = 0) AND (POLICIES.CheckOutUser IS NULL)

a example of the result

About

Microsoft

Leave a Reply

Your email address will not be published. Required fields are marked *

This site uses Akismet to reduce spam. Learn how your comment data is processed.