On newer versions of SQL (SQL2012+) use this SQL...

SELECT @@servername AS [Server],

instance = case @@servicename

when 'MSSQLSERVER' then 'DEFAULT'

else @@servicename

end,

(SELECT DISTINCT local_tcp_port

FROM sys.dm_exec_connections

WHERE local_tcp_port IS NOT NULL) AS [Port],

cpu_count AS [Logical CPU Count],

hyperthread_ratio,

cpu_count/hyperthread_ratio as [Physical_CPU_Count],

physical_memory_kb/1024 AS [Physical Memory (MB)],

VM_type = case virtual_machine_type

when 0 then 'None'

when 1 then 'Hypervisor'

when 2 then 'Other'

else 'Error'

end,

SERVERPROPERTY('edition') AS [Edition],

SERVERPROPERTY('productversion') AS [Version],

(SELECT MAX(COALESCE(last_user_seek, last_user_scan, last_user_lookup,'1/1/1970')) AS [Last User Activity]

FROM sys.dm_db_index_usage_stats

WHERE database_id > 4) AS [Last User Activity],

(SELECT COUNT(*)

FROM sys.databases) AS [Database Count],

(SELECT os_version = case windows_release

when '10.0' then 'Windows 10/2016/2019'

when '6.3' then 'Windows 8.1/2012R2'

when '6.2' then 'Windows 8/2012'

when '6.1' then 'Windows 7/2008R2'

when '6.0' then 'Windows Vista/2008'

else 'Uknown version'

end

FROM sys.dm_os_windows_info) As [OS_Version]

FROM sys.dm_os_sys_info;

GO

On SQL2008R2 use this slight variation...

SELECT @@servername AS [Server],

instance = case @@servicename

when 'MSSQLSERVER' then 'DEFAULT'

else @@servicename

end,

(SELECT DISTINCT local_tcp_port

FROM sys.dm_exec_connections

WHERE local_tcp_port IS NOT NULL) AS [Port],

cpu_count AS [Logical CPU],

hyperthread_ratio as [Hyperthread Ratio],

cpu_count/hyperthread_ratio as [Physical_CPU],

physical_memory_in_bytes/1024/1024 AS [Physical Memory (MB)],

VM_type = case virtual_machine_type

when 0 then 'None'

when 1 then 'Hypervisor'

when 2 then 'Other'

else 'Error'

end,

SERVERPROPERTY('edition') AS [Edition],

SERVERPROPERTY('productversion') AS [Version],

(SELECT MAX(COALESCE(last_user_seek, last_user_scan, last_user_lookup,'1/1/1970')) AS [Last User Activity]

FROM sys.dm_db_index_usage_stats

WHERE database_id > 4) AS [Last User Activity],

(SELECT COUNT(*)

FROM sys.databases) AS [Database Count],

(SELECT os_version = case windows_release

when '10.0' then 'Windows 10/2016/2019'

when '6.3' then 'Windows 8.1/2012R2'

when '6.2' then 'Windows 8/2012'

when '6.1' then 'Windows 7/2008R2'

when '6.0' then 'Windows Vista/2008'

else 'Uknown version'

end

FROM sys.dm_os_windows_info) As [OS_Version]

FROM sys.dm_os_sys_info;

GO

On older versions of SQL (SQL2005, SQL2008) use this cut down alternate version...

SELECT @@servername AS [Server],

instance = case @@servicename

when 'MSSQLSERVER' then 'DEFAULT'

else @@servicename

end,

(SELECT DISTINCT local_tcp_port

FROM sys.dm_exec_connections

WHERE local_tcp_port IS NOT NULL) AS [Port],

cpu_count AS [Logical CPU Count],

hyperthread_ratio as [Hyperthread Ratio],

cpu_count/hyperthread_ratio as [Physical_CPU],

physical_memory_in_bytes/1024/1024 AS [Physical Memory (MB)],

SERVERPROPERTY('edition') AS [Edition],

SERVERPROPERTY('productversion') AS [Version],

(SELECT MAX(COALESCE(last_user_seek, last_user_scan, last_user_lookup,'1/1/1970')) AS [Last User Activity]

FROM sys.dm_db_index_usage_stats

WHERE database_id > 4) AS [Last User Activity],

(SELECT COUNT(*)

FROM sys.databases) AS [Database Count]

FROM sys.dm_os_sys_info;

GO