Last active
April 27, 2018 22:36
-
-
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.
This file contains hidden or bidirectional Unicode text that may be interpreted or compiled differently than what appears below. To review, open the file in an editor that reveals hidden Unicode characters.
Learn more about bidirectional Unicode characters
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