Skip to content

Instantly share code, notes, and snippets.

@R41D3NN

R41D3NN/Secret-Server--Discovery-AD-Accounts--Users-And-Groups.sql

Last active April 27, 2018 17:07
Show Gist options

  • Select an option

    Learn more about clone URLs
  • Save R41D3NN/19ba4b2bc438a872d12de5832f38ff61 to your computer and use it in GitHub Desktop.

Select an option

Learn more about clone URLs
Save R41D3NN/19ba4b2bc438a872d12de5832f38ff61 to your computer and use it in GitHub Desktop.
Download ZIP
Secret Server report to show discovered AD Users and each group they are in. Each group is in it's own row.
Raw
Secret-Server--Discovery-AD-Accounts--Users-And-Groups.sql
SELECT
A.[OU DN],
A.[AccountName],
Split.a.value('.', 'VARCHAR(1000)') AS [Group]
FROM (
SELECT
[ou].[DistinguishedName] AS 'OU DN',
[ca].[AccountName],
CAST ('<M>' + REPLACE(REPLACE(REPLACE(REPLACE([ca].[SearchGroups], '&', '\u0026'), '<', '\u003C'), '>', '\u003E'), CHAR(59), '</M><M>') + '</M>' AS XML) AS [Group]
FROM [tbComputerAccount] [ca]
JOIN [tbOrganizationUnit] [ou] ON [ca].[OrganizationUnitId] = [ou].[OrganizationUnitId]
) AS A
CROSS APPLY [Group].nodes ('/M') AS Split(a)
ORDER BY
[Group],
A.[OU DN],
A.[AccountName]
Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment