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.

  • 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

  • Ivan,

    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 .

    What you provided will accomplish that ?
  • 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

     

    Regards, Ivan