Robert Hartskeerl changed it to his likings also, which can be found here.
Based on these 2 versions we changed the script to our needs also a bit, also we cheated a little by using quest tools.
Below the prerequirements and steps to run our version.
- Install .net framework 4.0
- Register 4.0 by adding 2 configfiles (powershell_ise.exe.config and powershell.exe.config) in the powershell folder, if not already there, usually in folder ..\WINDOWS\system32\windowspowershell\v1.0\.
Add the 4.0 reference to these config files. (check google for details if needed)
- Install the quest activeroles tooling, which can be found here.
Our script still uses a textfile with all the names from computers present in the network/domain.
- Create this textfile by running the below code, first line needs to run also if the quest tooling wasn't previously loaded.
#add-PSSnapin quest.activeroles.admanagement $FilePath = "d:\tmp\computers.csv" $Computers = Get-QADComputer -searchroot 'DC=domainname,DC=nl' -SizeLimit 0 -OSName 'Windows*' $Computers | Select-Object Name | Export-Csv $FilePath
- Open the newly created textfile and delete the first line with all the column info, also remove the "" quotes from all the computernames.
- Almost there, now run the real script which scans the network, see below.
Also his aura usually makes this stuff work faster also.
#add-PSSnapin quest.activeroles.admanagement
$Count = 0
$Count2 = 0
$instances2 = @()
$Computers = Get-Content D:\tmp\Powdershell\computers.csv
$FilePath2 = "D:\tmp\Powdershell\result3.csv"
function Pong{
Param([string]$srv,$port=135,$timeout=100,[switch]$verbose)
$ErrorActionPreference = "SilentlyContinue"
$tcpclient = new-Object system.Net.Sockets.TcpClient
$iar = $tcpclient.BeginConnect($srv,$port,$null,$null)
$wait = $iar.AsyncWaitHandle.WaitOne($timeout,$false)
if(!$wait)
{
$tcpclient.Close()
if($verbose){Write-Host "Connection Timeout"}
Return $false
}
else
{
$error.Clear()
$tcpclient.EndConnect($iar) | out-Null
if(!$?){if($verbose){write-host $error[0]};$failed = $true}
$tcpclient.Close()
}
if($failed){return $false}else{return $true}
}
function Get-SqlInstances($hostName)
{
$ErrorActionPreference = "SilentlyContinue"
$instances = @()
$reg = $null
#$reg = [Microsoft.Win32.RegistryKey]::OpenRemoteBaseKey('LocalMachine', $hostname)
$Hive = "LocalMachine"
$reg = [Microsoft.Win32.RegistryKey]::OpenRemoteBaseKey([Microsoft.Win32.RegistryHive]$Hive,$hostName,[Microsoft.Win32.RegistryView]::Registry64)
IF ($reg)
{
$regkey = $null
$regkey = $reg.OpenSubkey("SOFTWARE\\Microsoft\Microsoft SQL Server")
IF ($regkey)
{
foreach ($regInstance in $regkey.GetValue('InstalledInstances'))
{
IF ($regInstance -eq "MSSQLServer")
{
$versionKey = $reg.OpenSubKey("SOFTWARE\\Microsoft\MSSQLServer\\Setup")
$serviceName = $regInstance
}
ELSE
{
$versionKey = $reg.OpenSubkey("SOFTWARE\\Microsoft\Microsoft SQL Server\\$regInstance\\Setup")
$serviceName = "MSSQL`$$regInstance"
}
IF ($versionKey)
{
$version = $versionKey.GetValue('PatchLevel')
$edition = $versionKey.GetValue('Edition')
if($version)
{
$instanceName = $hostName
$clusterKey = $reg.OpenSubKey("SOFTWARE\\Microsoft\Microsoft SQL Server\\$regInstanceData\\Cluster")
if($clusterKey)
{
$hostName = $clusterKey.GetValue('ClusterName')
}
if($regInstance -ne 'MSSQLSERVER')
{
$instanceName = "$hostName\$regInstance"
}
$serviceStatus = Get-Service -name $serviceName
$instance = New-Object System.Object
$instance | Add-Member -type NoteProperty -name Name -value $instanceName
$instance | Add-Member -type NoteProperty -name DisplayName -value $regInstance
$instance | Add-Member -type NoteProperty -name Version -value $version
$instance | Add-Member -type NoteProperty -name Edition -value $edition
$instance | Add-Member -type NoteProperty -name InstanceName -value $instanceName
$instance | Add-Member -type NoteProperty -name InstanceID -value $regInstance
$instance | Add-Member -type NoteProperty -name ServiceName -value $serviceName
$instance | Add-Member -type NoteProperty -name Status -value $serviceStatus.Status
$instances += $instance
}
}
}
$regkey = $null
$regkey = $reg.OpenSubKey("SOFTWARE\\Microsoft\\Microsoft SQL Server\\Instance Names\\SQL")
IF ($regkey)
{
foreach ($regInstance in $regkey.GetValueNames())
{
$regInstanceData = $regKey.GetValue($regInstance)
$versionKey = $reg.OpenSubKey("SOFTWARE\\Microsoft\Microsoft SQL Server\\$regInstanceData\\Setup")
$version = $versionKey.GetValue('PatchLevel')
$edition = $versionKey.GetValue('Edition')
$instanceName = $hostName
$serviceName = $regInstance
$clusterKey = $reg.OpenSubKey("SOFTWARE\\Microsoft\Microsoft SQL Server\\$regInstanceData\\Cluster")
if($clusterKey)
{
$hostName = $clusterKey.GetValue('ClusterName')
}
if($regInstance -ne 'MSSQLSERVER')
{
$instanceName = "$hostName\$regInstance"
$serviceName = "MSSQL`$$regInstance"
}
$serviceStatus = Get-Service -name $serviceName
$instance = New-Object System.Object
$instance | Add-Member -type NoteProperty -name Name -value $instanceName
$instance | Add-Member -type NoteProperty -name DisplayName -value $regInstance
$instance | Add-Member -type NoteProperty -name Version -value $version
$instance | Add-Member -type NoteProperty -name Edition -value $edition
$instance | Add-Member -type NoteProperty -name InstanceName -value $instanceName
$instance | Add-Member -type NoteProperty -name InstanceID -value $regInstanceData
$instance | Add-Member -type NoteProperty -name ServiceName -value $serviceName
$instance | Add-Member -type NoteProperty -name Status -value $serviceStatus.Status
$instances += $instance
}
}
}
}
$instances
}
#Get-SqlInstances('sal0005')
foreach($Comp in $Computers)
{
#Write-host $Count.ToString()
#Get-SqlInstances($Comp.Name)
IF(pong($Comp))
{
$instances2 += Get-SqlInstances($Comp)
}# else {Write-host 'Offline'}
$Count = $count + 1
IF($Count -eq 999)
{
$Count2 = $Count2 + 1000
Write-host $Count2.ToString() ' found ' $instances2.Count.ToString() ' SQLServers'
$Count = 0
}
}
$instances2 | Select-Object Name, DisplayName, Version, Edition, InstanceName, InstanceID, ServiceName, Status | Export-Csv $FilePath2
$instances2 | Select-Object Name, DisplayName, Version, Edition, InstanceName, InstanceID, ServiceName, Status | ft
