Determine Hosted SQL Server if running on VM or Physical Using Powershell

Problem:- 

You had hundreds of servers and for any kind of reporting or analysis, you need to know current SQL Server hosted environment if it Virtual or Physical Server, so we will use PowerShell to achieve that goal in a simple way as usual😊

Solution:-

Using below Script if you are checking machine locally

	$Virtual = Get-wmiobject win32_computersystem | Where-Object model -EQ 'Virtual Machine'
	if ($Virtual)
	{
	Write-Host "`nSQL Server Running on Virtual Server" -ForegroundColor Green
	}
	else
	{
	Write-Host "`nSQL Server Running on Physical Server" -ForegroundColor Yellow
	}

view raw DetermineHostedMachineifVMorPhysicalServerLocallyUsingPowershell.ps1 hosted with ❤ by GitHub

Using Below Script if you are dealing with hundreds of Servers Remotely

	$Servers = Read-Host "Please Add Text File Path that Contain all servers IP "
	$Target = Get-Content -Path $Servers
	$hosted = Invoke-Command -ComputerName $Target -ScriptBlock {Get-wmiobject win32_computersystem |
	Where-Object model -EQ 'Virtual Machine'} -Credential (Get-Credential)
	$result=@()
	if ($hosted)
	{
	$result+=New-Object -TypeName PSObject -Property ([ordered]@{
	'Computer IP'=$hosted.PSComputerName;
	'Hosted On'=$hosted.Model;
	})
	}
	else
	{
	$result+=New-Object -TypeName PSObject -Property ([ordered]@{
	'Computer IP'=$hosted.PSComputerName;
	'Hosted On'='Physical Server';
	})
	}
	Write-Output $result

view raw DetermineHostedMachineifVMorPhysicalServerRemotelyUsingPowershell.ps1 hosted with ❤ by GitHub

How to Run above Script Remotely: -

• You need Windows Version 8.1, Windows Server 2012R2 or above
• Run Powershell in elevated privilege (As Administrator)
• Set the Execution policy to bypass by typing (Set-ExecutionPolicy -ExecutionPolicy Bypass )
• Must enable PS Remoting.
• Save in text file all Servers IP (i.e C:\Users\Mk.elsawy\Desktop\Servers.txt)
• Save below Script as  DetermineHostedMachineifVMorPhysicalServerRemotelyUsingPowershell.ps1
• The script will ask you for a credential that will be used when connecting 
• Execute the script from Powershell as below 

Click for bigger

Ref:-

https://docs.microsoft.com/en-us/windows/win32/cimwin32prov/win32-computersystem