SCCM SQL Query Online device by Management Point

 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