Home Analytics

Incident backlog trend

Stephane_BouillonStephane_Bouillon Customer Adept IT Monkey ✭✭

Does anybody have a query that shows the trend of open incidents for the last 60 days ? As backlog I consider the total number of incidents that were not in a closed state on any given day.

It is very interesting from a team performance standpoint to know if over time the backlog is decreasing, increasing, or remaining more or les stable.

If something is available out-of-the-box that's even better.

Stephane

Answers

  • Stephane_BouillonStephane_Bouillon Customer Adept IT Monkey ✭✭

    The same would be nice to have for service requests as well.

  • James_JohnsonJames_Johnson Customer Adept IT Monkey ✭✭

    If you're looking for a graphical representation you could take a look at the code I posted in https://community.cireson.com/discussion/5518/sample-code-for-dashboards-300-blog#latest


    Otherwise here's the sql query we use for a line graph for past 3 months of incidents of priority greater than 3.

    WITH Dates AS (
        SELECT
         EventDate = DATEADD(MONTH,-3,CONVERT(DATE,GETDATE()))
        UNION ALL SELECT
         EventDate = DATEADD(DAY, 1, EventDate)
        FROM
         Dates
        WHERE
         EventDate < CONVERT(DATE,GETDATE())
    ),
    PriorityWI AS (
    SELECT ResolvedDate,Created FROM workitem WHERE ClassId = 'A604B942-4C7B-2FB2-28DC-61DC6F465C68' AND PriorityId > 3
    )
    SELECT EventDate [Date], 
    (
    	SELECT COUNT(PriorityWI.Created) -  
    	(
    		SELECT COUNT(PriorityWI.Created) FROM PriorityWI WHERE CONVERT(DATE,PriorityWI.ResolvedDate) <= EventDate
    	) FROM PriorityWI WHERE CONVERT(DATE,PriorityWI.created) <= EventDate
    ) AS [Priority_Backlog]
    FROM Dates
    group by EventDate
    
  • Gerhard_GoossensGerhard_Goossens Customer Advanced IT Monkey ✭✭✭

    Today is your lucky day. I created this from borrowed code that I found somewhere... :-)

    This is for IRs and SRs, you can split it by removing the "UNION ALL"

    There is also a token that I pass to the dashboard that you can remove or change as needed


    WITH Dates AS (
        SELECT
            EventDate = DATEADD(MONTH, -1, CONVERT(DATE, GETDATE()))
        UNION ALL
        SELECT
            EventDate = DATEADD(DAY, 1, EventDate)
        FROM
            Dates
        WHERE
            EventDate < CONVERT(DATE, GETDATE())
    )
    
    
    SELECT  com.[Date],
    		sum(com.Backlog) as [Backlog]
    
    
    		from
    (
    SELECT
        EventDate [Date],
        (
            SELECT
                COUNT(wi.id) - (
                    SELECT
                        COUNT(wi.id)
                    FROM
                        workitem wi
    					
        LEFT JOIN [Enumeration] enum ON enum.EnumerationID = wi.TierId 
        LEFT JOIN [DisplayString] dssupport ON dssupport.ElementID=enum.EnumerationID
                    WHERE
                        CONVERT(DATE, wi.CompletedDate) <= EventDate
                        AND wi.ClassId = '04B69835-6343-4DE2-4B19-6BE08C612989'
    					AND StatusId IN ('72B55E17-1C7D-B34C-53AE-F61F8732E425', 'A52FBC7D-0EE3-C630-F820-37EAE24D6E9B', '59393F48-D85F-FA6D-2EBE-DCFF395D7ED1', '05306BF5-A6B9-B5AD-326B-BA4E9724BF37') --Active, Pending
    					AND dssupport.DisplayString in ('{{SuppGroup}}')
                )
            FROM
                workitem wi
    			
        LEFT JOIN [Enumeration] enum ON enum.EnumerationID = wi.TierId 
        LEFT JOIN [DisplayString] dssupport ON dssupport.ElementID=enum.EnumerationID
            WHERE
                CONVERT(DATE, wi.created) <= EventDate
                        AND wi.ClassId = '04B69835-6343-4DE2-4B19-6BE08C612989'
    					AND StatusId IN ('72B55E17-1C7D-B34C-53AE-F61F8732E425', 'A52FBC7D-0EE3-C630-F820-37EAE24D6E9B', '59393F48-D85F-FA6D-2EBE-DCFF395D7ED1', '05306BF5-A6B9-B5AD-326B-BA4E9724BF37') --Active, Pending
    					AND dssupport.DisplayString in ('{{SuppGroup}}')
        ) as [Backlog]
    FROM
        Dates
    group by
        EventDate
    ---------------------------------------------
    	UNION ALL
    ---------------------------------------------
    	
    SELECT
        EventDate [Date],
        (
            SELECT
                COUNT(wi.id) - (
                    SELECT
                        COUNT(wi.id)
                    FROM
                        workitem wi
    					
        LEFT JOIN [Enumeration] enum ON enum.EnumerationID = wi.TierId 
        LEFT JOIN [DisplayString] dssupport ON dssupport.ElementID=enum.EnumerationID
                    WHERE
                        CONVERT(DATE, wi.resolvedDate) <= EventDate
                        AND wi.ClassId = 'A604B942-4C7B-2FB2-28DC-61DC6F465C68'
    					AND StatusId IN ('5E2D3932-CA6D-1515-7310-6F58584DF73E', 'B6679968-E84E-96FA-1FEC-8CD4AB39C3DE') --Active, Pending
    					AND dssupport.DisplayString in ('{{SuppGroup}}')
                )
            FROM
                workitem wi
    			
        LEFT JOIN [Enumeration] enum ON enum.EnumerationID = wi.TierId 
        LEFT JOIN [DisplayString] dssupport ON dssupport.ElementID=enum.EnumerationID
            WHERE
                CONVERT(DATE, wi.created) <= EventDate
                AND wi.ClassId = 'A604B942-4C7B-2FB2-28DC-61DC6F465C68'
    			AND StatusId IN ('5E2D3932-CA6D-1515-7310-6F58584DF73E', 'B6679968-E84E-96FA-1FEC-8CD4AB39C3DE') --Active, Pending
    			AND dssupport.DisplayString in ('{{SuppGroup}}')
        ) as [Backlog]
    
    
    
    
    	FROM
        Dates
    group by
        Dates.EventDate) Com
    
    
    Group by com.Date
    order by com.Date
    
  • Adam_DzyackyAdam_Dzyacky Product Owner Contributor Monkey ✭✭✭✭✭

    Nice one @Gerhard_Goossens !

Sign In or Register to comment.