These queries were created for during a conversion period when systems were being changed out regularly and we needed an accurate count of systems per site.
This first one eturns all clients (including servers) at a site:
SELECT DISTINCT
SITE.SiteCode AS 'Site Code',
SITE.SiteName AS 'Site Name',
COUNT(Instl.SMS_Installed_Sites0) AS 'Number of Clients Installed'
FROM
v_Site SITE
INNER JOIN
v_RA_System_SMSInstalledSites Instl
ON SITE.SiteCode = Instl.SMS_Installed_Sites0
INNER JOIN
v_AgentDiscoveries AGENT
ON Instl.ResourceID = AGENT.ResourceID
WHERE (7 >= DATEDIFF(DAY, AGENT.AgentTime, GETDATE()))
AND (AGENT.AgentName <> 'SMS_WINNT_SERVER_DISCOVERY_AGENT')
GROUP BY SITE.SiteCode, SITE.ReportingSiteCode, SITE.SiteName
ORDER BY SITE.SiteCode
This second one returns only the workstations (because the logon installation never runs on our servers)
SELECT DISTINCT
SITE.SiteCode AS 'Site Code',
SITE.SiteName AS 'Site Name',
COUNT(Instl.SMS_Installed_Sites0) AS 'Number of Clients Installed'
FROM
v_Site SITE
INNER JOIN
v_RA_System_SMSInstalledSites Instl
ON SITE.SiteCode = Instl.SMS_Installed_Sites0
INNER JOIN
v_AgentDiscoveries AGENT
ON Instl.ResourceID = AGENT.ResourceID
WHERE (7 >= DATEDIFF(DAY, AGENT.AgentTime, GETDATE()))
AND (AGENT.AgentName = 'NT Logon Discovery')
GROUP BY SITE.SiteCode, SITE.ReportingSiteCode, SITE.SiteName
ORDER BY SITE.SiteCode
Finally here is the XML statement I used for the query that allows you to drill through to the systems at that site.
<Node ID="N7023" Desc="List of Client Counts by Site Code" A="1" CatID="3" F="0" SP="usp_SMSR7023" HS="0" SD="none" HDT="1" DTQ="3016" FP="none" Type="0" G="0" V="1" />
If you notice the drill through uses 3016 which is the same stored procedure used by the "Clients by site" dashboard view as a drill through.
