An end user reported an issue with their own machine not getting any software deployments. A deeper dive led us to find that the machine had failed on every deployment for some time, and needed some intervention. I wrote a T-SQL script to use in a report, so that our local techs could quickly determine clients that need investigation and intervention because of repeated failed deployments.
The following T-SQL script will check the last five deployments for every machine that has deployment status, and returns the ones that failed on all five of them (as well as their latest deployment status time, in Eastern Standard Time).
declare @tblFailedResourceIDs TABLE (ResourceID int, LastStatusTime datetime); SET NOCOUNT ON; declare @ResourceID int, @LastResourceID int, @iFailureCounter int, @LastStateName nvarchar(255), @iCounter int, @LastStatusTime datetime, @LastRecordedStatusTime datetime; set @LastResourceID = 0; DECLARE advert_cursor CURSOR READ_ONLY FOR SELECT sys.ResourceID, LastStateName, stat.LastStatusTime FROM v_advertisement adv JOIN v_ClientAdvertisementStatus stat ON stat.AdvertisementID = adv.AdvertisementID JOIN v_R_System sys ON stat.ResourceID=sys.ResourceID WHERE (LastStateName != 'Accepted - No Further Status' and LastStateName != 'No Status') order by sys.ResourceID, LastStatusTime desc; OPEN advert_cursor; FETCH NEXT FROM advert_cursor INTO @ResourceID, @LastStateName, @LastStatusTime; WHILE @@FETCH_STATUS = 0 BEGIN IF @ResourceID != @LastResourceID BEGIN SET @iFailureCounter = 0; SET @iCounter = 0; END SET @iCounter = @iCounter + 1 If @iCounter <= 5 BEGIN If @iCounter = 1 SET @LastRecordedStatusTime = @LastStatusTime If @LastStateName = 'Failed' SET @iFailureCounter = @iFailureCounter + 1; If @iFailureCounter = 5 INSERT INTO @tblFailedResourceIDs (ResourceID, LastStatusTime) VALUES (@ResourceID, @LastRecordedStatusTime) END SET @LastResourceID = @ResourceID FETCH NEXT FROM advert_cursor INTO @ResourceID, @LastStateName, @LastStatusTime; END; CLOSE advert_cursor; DEALLOCATE advert_cursor; SET NOCOUNT OFF; SELECT Name0 AS Computer, DATEADD(HH,-5,F.LastStatusTime) AS Last_Status_Time_EST from v_r_system SYS INNER JOIN @tblFailedResourceIDs F on F.ResourceID = SYS.ResourceID ORDER BY Name0;