This discussion has been locked.

You can no longer post new replies to this discussion. If you have a question you can start a new discussion

Computers with "MSSQLServer" Service Running in Specific OU

Looking for query Active Directory to find Computers with "MSSQLServer" or "sqlservr.exe" , Service Running in Specific OU.

Parents

0 IvanK over 6 years ago

Hello,

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

Regards, Ivan

Reply