Looking for query Active Directory to find Computers with "MSSQLServer" or "sqlservr.exe" , Service Running in Specific OU.
At first you have to run both AD and computer collects to discover computers in OUs and services at computers.
Then you can use the following SQL query to select computers and services.
declare @domainOUCanonicalNamePattern nvarchar(max)
declare @serviceExecutablePattern nvarchar(max)
--Here set the OU canonical name
set @domainOUCanonicalNamePattern = null
--here set the service executable
set @serviceExecutablePattern = '%sqlservr.exe%'
select C.ComputerName, SRV.BinaryPathName, SRV.ServiceName, *
from dbo.tblDomain D(nolock)
join dbo.tblDomainOU OU(nolock) on OU.DomainID = D.DomainID and isnull(OU.LDM_TOMBSTONED,0) = 0
join dbo.tblDomainComputer DC(nolock) on DC.DomainID = D.DomainID and DC.DomainOUID = OU.DomainOUID
and ISNULL(DC.LDM_TOMBSTONED,0) = 0
join dbo.tblComputer C(nolock) on C.ComputerName = DC.ComputerName and C.DomainName = D.DomainName
and isnull(C.LDM_TOMBSTONED,0) = 0
join dbo.tblComputerPhysical CP(nolock) on CP.ComputerID = C.ComputerID and isnull(CP.LDM_TOMBSTONED,0) = 0
join dbo.tblComputerService SRV(nolock) on SRV.PhysicalComputerID = CP.PhysicalComputerID and isnull(SRV.LDM_TOMBSTONED,0) = 0
where ISNULL(D.LDM_TOMBSTONED,0) = 0
and (@domainOUCanonicalNamePattern is null or OU.CanonicalName like @domainOUCanonicalNamePattern)
and (@serviceExecutablePattern is null or SRV.BinaryPathName like @serviceExecutablePattern)
You can wrap the following query in a custom query report to create a nice looking output
In reply to jjahandarifar:
Do you need a custom report that will show you all the servers in OU with SQL Server installed?
If you need a custom report, could you please request for it at the designated custom report forum: https://www.quest.com/community/products/enterprise-reporter/f/custom-reports
In reply to IvanK: