Skip to content

Instantly share code, notes, and snippets.

Show Gist options
  • Save R41D3NN/6f5f54e84de496021de5b78f14cdae2a to your computer and use it in GitHub Desktop.
Save R41D3NN/6f5f54e84de496021de5b78f14cdae2a to your computer and use it in GitHub Desktop.
Secret Server report that shows discovered dependencies on all machines, their services accounts, as well as if that services account is being discovered by AD Discovery.
SELECT
[ou].[DistinguishedName] AS [OU DN],
[c].[ComputerName],
[sdt].[SecretDependencyTypeName],
[cd].[DependencyName],
CONCAT([d].[FriendlyName], '\', [cd].[AccountName]) AS [AccountName],
CASE WHEN [ca].[AccountName] IS NULL
THEN 'False'
ELSE 'True'
END AS [Account In AD Discovery]
FROM [tbComputerDependency] [cd]
JOIN [tbSecretDependencyType] [sdt] ON [cd].[SecretDependencyTypeID] = [sdt].[SecretDependencyTypeId]
JOIN [tbComputer] [c] ON [cd].[ComputerID] = [c].[ComputerId]
JOIN [tbOrganizationUnit] [ou] ON [c].[OrganizationUnitId] = [ou].[OrganizationUnitId]
JOIN [tbDomain] [d] ON [cd].[AccountDomainID] = [d].[DomainId]
LEFT JOIN [tbComputerAccount] [ca] ON [cd].[AccountName] = [ca].[AccountName] AND [cd].[AccountDomainID] = [d].[DomainId] AND [ca].[ComputerId] IS NULL
ORDER BY
[OU DN],
[c].[ComputerName],
[sdt].[SecretDependencyTypeName],
[cd].[DependencyName]
Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment