Report Title: Group Membership by Display Name of Domain Account
Report Description: Shows all groups to which the selected domain accounts belong.
Report Category: Active Directory
Version: 2.0.1
Customization Details: The current "Group Membership by Domain Account" report doesn't have the ability to show the display name of the domain account instead of the account name. This is custom query report will allow you to achieve this. If the display name is empty or null, then the account name will be shown. The report is using the same query from the "Group Membership by Domain Account" report but also has the following added to it:
LEFT OUTER JOIN [tblDomainGroup] DomGroupInfo
ON ([vwDomainGroupMember].[AccountID] = [DomGroupInfo].[AccountID])
LEFT OUTER JOIN [tblDomainUser] DomUserInfo
ON ([vwDomainGroupMember].[AccountID] = [DomUserInfo].[AccountID])
To create the new report:
1. Launch the Report Manager
2. Right click on “My Reports” and select “Create Custom Query Report…”
3. Type in the report name, author and description
4. Click on the “Query” tab and paste the following:
DECLARE @domainName NVARCHAR(255)
SELECT [base].[DomainName] as [Domain Name (Domain)], DomUserInfo.[DisplayName] as [Display Name (Domain User)], DomGroupInfo.[DisplayName] as [Display Name (Domain Group)], [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)], CASE WHEN LEN([tblAccount_1].[SAMAccountDomain]) = 0 THEN [tblAccount_1].[SAMAccountName] ELSE [tblAccount_1].[SAMAccountDomain] + '\' + [tblAccount_1].[SAMAccountName] END as [Account Name (Group)], [Account Scope (Group)] =
CASE [tblAccount_1].[AccountScope]
WHEN N'B' THEN N'Builtin'
WHEN N'D' THEN N'Domain'
WHEN N'DL' THEN N'Domain local'
WHEN N'L' THEN N'Local'
WHEN N'U' THEN N'Universal'
WHEN N'SQL' THEN N'SQL Login'
WHEN N'W' THEN N'Well Known'
WHEN N'X' THEN N'Unknown'
ELSE [tblAccount_1].[AccountScope]
END, DATEADD(minute, -240, [base].[CollectedTime])as [Last Collected Time (Domain)]
FROM
[tblDomain] base
LEFT OUTER JOIN [vwDomainGroupMember] vwDomainGroupMember
ON ([base].[DomainID] = [vwDomainGroupMember].[DomainID])
LEFT OUTER JOIN [tblDomainOU] tblDomainOU
ON ([vwDomainGroupMember].[DomainOUID] = [tblDomainOU].[DomainOUID] AND ([tblDomainOU].[LDM_TOMBSTONED] = 0 OR [tblDomainOU].[LDM_TOMBSTONED] IS NULL))
LEFT OUTER JOIN [tblGroup] tblGroup
ON ([vwDomainGroupMember].[GroupID] = [tblGroup].[GroupID] AND ([tblGroup].[LDM_TOMBSTONED] = 0 OR [tblGroup].[LDM_TOMBSTONED] IS NULL))
LEFT OUTER JOIN [tblDomainGroup] DomGroupInfo
ON ([vwDomainGroupMember].[AccountID] = [DomGroupInfo].[AccountID])
LEFT OUTER JOIN [tblDomainUser] DomUserInfo
ON ([vwDomainGroupMember].[AccountID] = [DomUserInfo].[AccountID])
LEFT OUTER JOIN [tblAccount] tblAccount
ON ([vwDomainGroupMember].[AccountID] = [tblAccount].[AccountID] AND ([tblAccount].[LDM_TOMBSTONED] = 0 OR [tblAccount].[LDM_TOMBSTONED] IS NULL))
LEFT OUTER JOIN [tblAccount] tblAccount_1
ON ([tblGroup].[GroupAccountID] = [tblAccount_1].[AccountID] AND ([tblAccount_1].[LDM_TOMBSTONED] = 0 OR [tblAccount_1].[LDM_TOMBSTONED] IS NULL))
WHERE (([base].[DomainName] LIKE @domainName) AND ((CASE WHEN LEN([tblAccount].[SAMAccountDomain]) = 0 THEN [tblAccount].[SAMAccountName] ELSE [tblAccount].[SAMAccountDomain] + '\' + [tblAccount].[SAMAccountName] END) LIKE N'%') AND (base.LDM_TOMBSTONED = 0 OR base.LDM_TOMBSTONED IS NULL))
ORDER BY [base].[DomainName] ASC, [tblDomainOU].[CanonicalName] ASC, [Account Name (Account)] ASC, [Account Name (Group)] ASC
5. Click on "Validate Query" button
6. Click on the “Parameter” tab and add the following parameter:
Prompt: Specify the domain:
Parameter: @domainName
Operator: Equals
7. Click on the “Layout” tab and create the layout using the layout designer (This will be similar to the existing "Group Membership by Domain Account" report layout").
NOTE: Originally Created on: Mar 28, 2014 10:55 AM by SAMER ELKHATIB - Last Modified: Mar 28, 2014 11:01 AM by SAMER ELKHATIB
