Modify the 'Microsoft Active Directory Import Details' report to display all statuses

Products

• Client Management Suite

Issue/Introduction

Can the 'Microsoft Active Directory Import Details' report be expanded to include all statuses?

Environment

Release : ITMS 8.x

Resolution

The following query can be used as a guide and modified as needed:

--ADImport Rules – All Statuses
SELECT       --tm.[StartDateTime] AS [Start Date/Time],
             --DATEDIFF( s, tm.[StartDateTime], tm.[EndDateTime] ) AS [Seconds],
             ISNULL(dbo.fnStr_GetLocalizedItemName( itm.[Guid], N'%_culture%'), itm.Name) AS [Import Rule],
                     tm.State AS [Status],
                     tm.[StartDateTime] AS [Start Date],
             DATEDIFF( s, tm.[StartDateTime], tm.[EndDateTime] ) AS [Seconds]
    FROM     TaskManager tm
    JOIN     vNonPartitionedItem itm ON UPPER(CAST(itm.Guid AS NVARCHAR(40))) = UPPER(SUBSTRING(tm.TaskID,2,36))                             
       WHERE      itm.ClassGuid = 'B2378265-2779-49E6-998D-8BE620B3D9D9'
       --AND      tm.State = '%C%' --Options: Completed (C), Stopped (S), Failed (F)
       AND      tm.[StartDateTime] >= DATEADD( dd, -7, GETDATE() )
    ORDER BY tm.[StartDateTime] DESC