PowerShell Query to find all the SQL Server Instances Running on a list of Remote Windows Server Machine.

# #

# Read in a list of Server Names from a file.

# For each server, query the services to find the SQL server instance names.
# List all the SQL instances found to a log file(outputfile).
# List all the Servers where no SQL instances found to another logfile(Logerrs).
##

$servers = get-content "D:\Primary_Secondary_Check\Step1_Server_Reboot_ServerList.txt"
$outputfile = "D:\Primary_Secondary_Check\Step1_Server_Reboot_InstanceName_Output.txt"
$logerrs = "D:\Primary_Secondary_Check\Step1_Server_Reboot_InstanceName_LogFile.txt"


Get-Date -Format "dd-MMM-yyyy hh:mm:ss" >> $outputfile
Echo "Server, Instance" >> $outputfile

ForEach ($server in $servers)
{
   $instances = Get-WmiObject -ComputerName $server win32_service | where {$_.name -like "MSSQL*"}
   if (!$?)
   {
      Get-Date -Format "dd-MMM-yyyy hh:mm:ss" >> $logerrs
      Echo "$server - No SQL instance found".ToUpper() >> $logerrs
      Echo "$server - No SQL instance found".ToUpper()
   }
   Else
   {
   
      ForEach ($instance in $instances)
      {
         if ($instance.name -like "MSSQL$*")
         {
            Echo "$server$($instance.name.Replace("MSSQL$","\"))".ToString().ToUpper() >> $outputfile
            Echo "$server$($instance.name.Replace("MSSQL$","\"))".ToString().ToUpper()
         }
         if ($instance.name -eq "MSSQLSERVER")
         {
            Echo "$server".ToString().ToUpper() >> $outputfile
            Echo "$server".ToString().ToUpper()
         }
      }
   }
}