Discovery--Computers-and-Total-Local-Accounts.sql

Discovery--Computers-and-Total-Local-Accounts.sql

SELECT
	[ou].[DistinguishedName] AS 'OU DN',
	[c].[ComputerName],
	COUNT([ca].[ComputerId]) AS 'Total Local Accounts',
	[CB].[Success],
	[CB].[LastErrorMessage]
FROM [tbComputer] [c] 
JOIN [tbOrganizationUnit] [ou] ON [c].[OrganizationUnitId] = [ou].[OrganizationUnitId]
LEFT JOIN (SELECT * FROM [tbComputerAccount] [ca] WHERE [ca].[ComputerId] IS NOT NULL) [ca] ON [c].[ComputerId] = [ca].[ComputerId]
CROSS APPLY
(
	SELECT TOP 1
		[csl].[Success],
		CASE 
			WHEN [csl].[Success] = 1 THEN NULL
			ELSE [c].[LastErrorMessage]
		END AS [LastErrorMessage]
	FROM [tbComputerScanLog] [csl]
	WHERE [csl].[ComputerId] = [c].[ComputerId]
	ORDER BY [csl].[ComputerScanLogId] DESC
) AS CB
GROUP BY
	[ou].[DistinguishedName],
	[c].[ComputerId],
	[c].[ComputerName],
	[CB].[LastErrorMessage],
	[CB].[Success]