This discussion has been locked.
You can no longer post new replies to this discussion. If you have a question you can start a new discussion

User Expiring report without Deprovisioned user list report

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