Adding the existence of a Service to a query.

My answer to the reddit post. https://www.reddit.com/r/SCCM/comments/lxslnw/sql_query_for_a_report/

First off you should always remember that you should never query the MECM tables directly.

Next this will require a sub query to add the three extra rows.

Lastly State will need to be inventoried, it is not enabled by default.

SELECT 
	vrsys.ResourceID,
	vrsys.Netbios_Name0 as 'Server Name',
	vrsys.AD_Site_Name0 as 'AD Site',
	vrsys.Client_Version0 as 'CM Client Version',
	os.LastBootupTime0 as 'Last Reboot',
	os.Caption0 as 'OS',
	COUNT(CPU.ResourceID) AS 'Sockets',
	CPU.NumberOfCores0 AS 'Cores per Socket',
	COUNT(CPU.ResourceID)*CPU.NumberOfCores0 AS 'Total Cores',
	MEM.TotalPhysicalMemory0/1024 AS 'Memory (GB)',
	case 
		when CSys.Manufacturer0 LIKE 'VMware%' then 'VM'
		ELSE 'Physical'
	END as 'Type',
	case
		when CSys.Model0 LIKE 'VMware%' then 'VMware'
		else CSys.Model0
		End as 'Model',
	case 
		when BIOS.SerialNumber0 LIKE 'VMware%' then 'VMware'
		else BIOS.SerialNumber0
	end as 'Serial',
/*	case dotnet.release00
		when '528049' then '4.8'
		when '461814' then '4.7.2'
		when '461310' then '4.7.1'
		when '460805' then '4.7'
		when '394806' then '4.6.2'
		when '394802' then '4.6.2'
		when '394271' then '4.6.1'
		when '378675' then '4.5.1'
		ELSE 'Undefined'
	end as 'DotNet Version',*/
	HWScan.LastHWScan as 'LastCheckIn',
	isnull(S.exist, '') as 'exist',
	isnull(s.ServiceType0, '') as 'type',
	isnull(S.State0, '') as 'state'
FROM 
	dbo.v_R_System vrsys
	Join dbo.v_GS_OPERATING_SYSTEM OS ON OS.ResourceID = vrsys.ResourceID
	Join dbo.v_GS_PROCESSOR CPU on cpu.ResourceID = vrsys.ResourceID
	Join dbo.v_GS_X86_PC_MEMORY MEM on MEM.ResourceID = vrsys.ResourceID
	Join dbo.v_GS_WORKSTATION_STATUS HWScan on HWScan.ResourceID = vrsys.ResourceID
	Join dbo.v_GS_COMPUTER_SYSTEM CSys on CSys.ResourceID = vrsys.ResourceID
	Join dbo.v_GS_PC_BIOS BIOS on BIOS.ResourceID = vrsys.ResourceID
--	left outer Join <insert view name here> DotNET on DotNET.MachineID = vrsys.ResourceID
	left outer join (Select S.ResourceID, 'x' as 'exist', S.ServiceType0, S.State0,s.DisplayName0 from v_GS_SERVICE S where S.DisplayName0 like 'Enhansoft Service') s on vrsys.ResourceID = s.ResourceID
group by 
	vrsys.ResourceID,
	vrsys.Netbios_Name0,
	vrsys.AD_Site_Name0,
	vrsys.Client_Version0,
	os.LastBootupTime0,
	os.Caption0,
	CPU.ResourceID,
	CPU.NumberOfCores0,
	CPU.NumberOfLogicalProcessors0,
	MEM.TotalPhysicalMemory0,
	CSys.Manufacturer0,
	CSys.Model0,
	BIOS.SerialNumber0,
--	dotnet.release00
	HWScan.LastHWScan,
	S.exist,
	s.ServiceType0,
	S.State0