# encoding: ascii
# api: powershell
# title: Find-SQLServers
# description: This code searches your AD for servers that have active computer accounts within the last 30 days, pings them, and returns a list of Service objects for installed SQL instances.
# version: 0.1
# author: jgrote
# license: CC0
# x-poshcode-id: 5785
# x-archived: 2016-03-04T23:11:08
# x-published: 2016-03-13T18:20:00
#
# For a more readable view, change last line to:
# $sqlinstances | select machinename,status,name,displayname | sort machinename
# #Requires ActiveDirectory module
#


$servers = get-adcomputer -filter {operatingsystem -like "Windows Server*"} -properties lastlogondate | where {$_.lastlogondate -gt (get-date).adddays(-30)}

$sqlservices = foreach ($server in $servers) {
        if (test-connection $server.dnshostname -quiet -count 1 -delay 1) {
            Get-Service -verbose -computername $server.dnshostname -name "MSSQL*"
        } #if test-connection
} #foreach

$sqlinstances = $sqlservices | where {$_.servicename -like "MSSQLServer" -or $_.servicename -like "MSSQL$*"}

$sqlinstances
