SCCM SQL Query Online device by Management Point
select srl.SiteCode, srl.ServerName, srl.InternetEnabled, srl.Shared, srl.SslState,
SUM(brs.OnlineStatus) AS OnlineClients, bs.ReportTime from SysResList srl
inner join BGB_Server bs ON srl.ServerName = bs.ServerName
inner join BGB_ResStatus brs ON bs.ServerID = brs.ServerID
where RoleName='SMS Management Point'
group by srl.SiteCode, srl.ServerName, srl.InternetEnabled, srl.Shared, srl.SslState, bs.ReportTime
SCCM SQL Query Client device count by Management Point
Select Substring(br.AccessMP,1,(PATINDEX('%.%',br.AccessMP)-1)) as Management_Point,
Count(Substring(br.AccessMP,1,(PATINDEX('%.%',br.AccessMP)-1))) as Total_Clients
from
v_R_System sd join BGB_ResStatus br on sd.ResourceID=br.ResourceID
Where PATINDEX('%.%',br.AccessMP) > 0
Group By Substring(br.AccessMP,1,(PATINDEX('%.%',br.AccessMP)-1))
Order By Count(Substring(br.AccessMP,1,(PATINDEX('%.%',br.AccessMP)-1))) Desc
