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:
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.
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.