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
Thank you for your reply.
1. Both AD and computer collects to discover computers in OUs and services at computers = This already done
2. What I am looking for run the query against an OU( which has lots of servers) , not against a specific Computer .
