<#
	AUTHOR:		Derik Hammer
	DATE:		20150507
	LINK:		www.sqlhammer.com/blog/multiple-server-management-finding-the-database-server
#>

Function Search-CMS 
{
    param
    (
        [Parameter(Mandatory=$false,
                   ValueFromPipeline=$True,
                   ValueFromPipelineByPropertyName=$True)]
        [string[]]$Keywords = $null, 

        [Parameter(Mandatory=$false,
                   ValueFromPipeline=$True,
                   ValueFromPipelineByPropertyName=$True)]
        [switch]$SearchDatabaseName, 

        [Parameter(Mandatory=$false,
                   ValueFromPipeline=$True,
                   ValueFromPipelineByPropertyName=$True)]
        [string]$cmsServer = $cmsServerName, 

        [Parameter(Mandatory=$false,
                   ValueFromPipeline=$True,
                   ValueFromPipelineByPropertyName=$True)]
        [string]$rootServerGroup = $cmsRootServerGroup,
        
        [Parameter(Mandatory=$false,
                   ValueFromPipeline=$True,
                   ValueFromPipelineByPropertyName=$True)]
        [int]$connTimeout = 30
    )

    process
    {
        Trap {
          $err = $_.Exception
          while ( $err.InnerException )
            {
            $err = $err.InnerException
            write-output $err.Message
            };
            continue
          }

        if(-not $Keywords)
        {
            Write-Warning "No keywords were provided. Dumping the complete server list and descriptions.";
            $SearchDatabaseName = $false;
        }
      
        $tempResults = @();
        $Keywords = ($Keywords | % { [regex]::escape($_) } ) -join "|"

        $serverList = Get-CMSServerList -cmsName $cmsServer -serverGroup $rootServerGroup -recurse;

        if($SearchDatabaseName)
        {
            $jobs = @();
            foreach($server in $serverList)
            {
                $args = @();
                $args += $connTimeout;
                $args += $server.Name;
                $args += $Keywords;
                
                $jobs += Start-Job -ScriptBlock {   [System.Reflection.Assembly]::LoadWithPartialName("Microsoft.SqlServer.Smo") | Out-Null;
                                                    $server = New-Object ("Microsoft.SqlServer.Management.Smo.Server") $args[1]
                                                    $server.ConnectionContext.ConnectTimeout = $args[0];
                                                    if($server.Version.Major -eq $null) 
                                                    { 
                                                        Write-Host "Could not establish connection to $($args[1])." 
                                                    } 
                                                    else 
                                                    { 
                                                        $servers = @();
                                                        foreach($database in ($server.Databases).Name)
                                                        {
                                                            if($database -match $($args[2]))
                                                            {
                                                                $obj = New-Object psobject;
                                                                Add-Member -InputObject $obj -MemberType NoteProperty -Name Result -Value 'True';
                                                                Add-Member -InputObject $obj -MemberType NoteProperty -Name ServerName -Value $($server.Name);
                                                                $servers += $obj;
                                                            }
                                                        }
                                                        return $servers;
                                                    } 
                                                } -ArgumentList $args;
            }
          
            $joboutput = ($jobs | Wait-Job | Receive-Job | Where-Object { $_.Result -eq 'True' } | Select-Object ServerName)
            $jobs | Remove-Job;

            foreach($server in $joboutput)
            {
                $tempResults += $server;
            }

            $results = ($tempResults | Select-Object ServerName -Unique);
        }
        else
        {
            $serverListOutput = $serverList | Where-Object {$_.Description -match $Keywords} | Select-Object ServerName, Description;

            foreach($result in $serverListOutput)
            {
                $tempResults += $result;
            }
            
            $results = ($tempResults | Select-Object ServerName, Description -Unique);
        }
    
        return $results;
    }
}