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
Recent Comments