Hello Team,
We have a report which fetch the information about the user account expiring details for (n)number of days
But it fetch the information includes Deprovisioned users but we dont need the deprovision users list in same expiring users details.
We need 1. report without deprovision users list and 2. Deprovisioned users list as a separate report.
Current Report Query is
SELECT [base].[DomainName] as [Domain Name (Domain)], [tblDomainOU].[CanonicalName] as [Canonical Name (Domain OU)], CASE WHEN LEN([tblAccount].[SAMAccountDomain]) = 0 THEN [tblAccount].[SAMAccountName] ELSE [tblAccount].[SAMAccountDomain] + '\' + [tblAccount].[SAMAccountName] END as [Account Name (Account)], [tblDomainUser].[DisplayName] as [Display Name (Domain User)], [tblAccount].[SAMAccountName] as [SAM Account Name (Account)], [tblDomainUser].[FirstName] as [First Name (Domain User)], [tblDomainUser_S0].[LastName] as [Last Name (Domain User)], [tblDomainUser_S0].[Office] as [Office (Domain User)], [tblDomainUser].[Department] as [Department (Domain User)], DATEADD(minute, 0, [tblDomainUser].[AccountExpirationDate])as [Expiration Date (Domain User)]
FROM
[tblDomain] base
LEFT OUTER JOIN ([tblDomainUser] tblDomainUser FULL OUTER JOIN [tblDomainUserPart3] tblDomainUser_S0
ON ([tblDomainUser].[DomainUserID] = [tblDomainUser_S0].[DomainUserID])
FULL OUTER JOIN [tblDomainUserPart2] tblDomainUser_S1
ON ([tblDomainUser].[DomainUserID] = [tblDomainUser_S1].[DomainUserID])
FULL OUTER JOIN [tblDomainUserExchange] tblDomainUser_S2
ON ([tblDomainUser].[DomainUserID] = [tblDomainUser_S2].[DomainUserID])
)
ON ([base].[DomainID] = [tblDomainUser].[DomainID] AND ([tblDomainUser].[LDM_TOMBSTONED] = 0 OR [tblDomainUser].[LDM_TOMBSTONED] IS NULL))
LEFT OUTER JOIN [tblAccount] tblAccount
ON ([tblDomainUser].[AccountID] = [tblAccount].[AccountID] AND ([tblAccount].[LDM_TOMBSTONED] = 0 OR [tblAccount].[LDM_TOMBSTONED] IS NULL))
LEFT OUTER JOIN [tblDomainOU] tblDomainOU
ON ([tblDomainUser].[DomainOUID] = [tblDomainOU].[DomainOUID] AND ([tblDomainOU].[LDM_TOMBSTONED] = 0 OR [tblDomainOU].[LDM_TOMBSTONED] IS NULL))
WHERE (([base].[DomainName] IN (N'uk.esure.com', N'esres.local') OR [base].[DomainName] LIKE N'%') AND DATEADD(minute, 0, [tblDomainUser].[AccountExpirationDate]) BETWEEN GETDATE() AND GETDATE() + 21 AND (base.LDM_TOMBSTONED = 0 OR base.LDM_TOMBSTONED IS NULL))
ORDER BY [base].[DomainName] ASC, [tblDomainOU].[CanonicalName] ASC, [Account Name (Account)] ASC, [tblDomainUser].[DisplayName] ASC