ConfigMgr T-SQL query – clients that failed their last several deployments

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;