How to query a Service status within a SQL query?

A user on Reddit ask How to query a Service status within a SQL query? They wanted to create a SQL Server Reporting Service (SSRS) Report with the results. They of course are using Microsoft Endpoint Manager Configuration Manage (MEMCM / MECM / SCCM) as the source for the SQL query. This trick in doing this is to create a subquery and join it to the main query. You can see the full Reddit reddit post. https://www.reddit.com/r/SCCM/comments/lxslnw/sql_query_for_a_report/ and everyone comments there.

Support MEMCM SQL Views

First off you should always remember that you should never query the MECM tables directly. Instead you should always query the supported SQL Views. The reason is simple record locking and support down the road by the CM product team. If you want to learn more, please see:

Why Is It Important to Use Supported SQL Server Views with SCCM Reporting?

What Are the Supported SQL Server Views to Use with SCCM Reporting?

Updating query a Service status

Next this will require a sub query to add the three extra columns that they are looking for. We need the subquery so that computers without the service will still show up and net be filtered out.

Lastly service State will need to be inventoried, it is not enabled by default. You will need to update the Hardware Inventory within the Client setting. Once this is update it make take a week for the results to be returned to MECM.

Updating query a Service status to add the State value.

SQL Query

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

When you run the query above you will see that X when the service exist and it is blank when it doesn’t exist. What is the Service type and state.

Don’t forget that you can subscribe to my RRS feed to stay on top of the latest trips and tricks. If you have any questions, please feel free to touch base @Garthmj.