In part 1 blog post for Configuration Manager Dashboard, we shared the sneak view for custom dashboard created in Microsoft IT using Configuration Manager Dashboard. And lot of inquires we received for sharing the SQL queries for creating these dashboard so this post is primarily for sharing SQL queries used for creating 3 custom dashboard which are Machines Counts, Update Compliance and Configuration Manager Administrator Dashboard.

1. Dashboard for Machines Counts
   • Overall Machines Count

     SELECT
         count(DISTINCT name0) AS OverallClientCount
     FROM v_R_System
         WHERE client0=1 and obsolete0=0 and active0=1

   • Machines Count By Site Code/Regions

     SELECT
     CASE sms_assigned_sites0
         WHEN 'PS1' THEN ‘Asia Region (PS1)'
         WHEN 'PS2' THEN 'Europe Region (PS2)'
         WHEN 'PS3' THEN ‘North & South America (PS3)'
     END AS "Region and SiteCode", count(distinct name0) as ClientCount
     FROM v_RA_System_SMSAssignedSites sas join v_R_System sys on sas.resourceID=sys.resourceID
     WHERE client0=1 AND obsolete0=0 AND Active0=1
     GROUP BY CASE sms_assigned_sites0

           WHEN 'PS1' THEN ‘Asia Region (PS1)'
         WHEN 'PS2' THEN 'Europe Region (PS2)'
         WHEN 'PS3' THEN ‘North & South America (PS3)'
     
   END
   ORDER by 2 desc

   Note: Change the text in blue as per your hierarchy site codes & descriptions.

   • Machines Count By Operating System

     SELECT TOP 5 
         OPSYS.Caption0 as 'Operating System',
         COUNT(*) AS 'Client Count'
     FROM v_GS_OPERATING_SYSTEM OPSYS  
     INNER JOIN v_R_System sys on OPSYS.ResourceID = sys.ResourceID 
     INNER JOIN v_RA_System_SMSAssignedSites Asys on sys.ResourceID = Asys.ResourceID
     WHERE
         sys.client0=1 AND sys.obsolete0=0 AND sys.active0=1 AND
         Asys.SMS_Assigned_Sites0 LIKE @SiteCode
     GROUP BY OPSYS.Caption0
     ORDER BY Count(*) desc

     Note: The above query uses query parameter (@Sitecode) and Dashboard can be configured as shown below for default filter & default values.

     

   • Machines Count By Domains

     SELECT TOP 10
         Resource_domain_OR_Workgr0 as ClientDomain,
         count(distinct name0) as ClientCount
     FROM v_R_System
         WHERE client0=1 AND obsolete0=0 AND Active0=1 
     GROUP BY Resource_domain_OR_Workgr0
     ORDER BY 2 desc

   • Machines Count By Server/Workstation OS Type

     SELECT
     ClientOSType =
     CASE
         WHEN Operating_System_Name_and0 LIKE '%workstation%' THEN 'Total WorkStation OS'
         WHEN Operating_System_Name_and0 LIKE '%server%' THEN 'Total Server OS'
     END,
     COUNT(*)AS ClientCount
     FROM v_r_system (NOLOCK)
     WHERE
         client0 =1 AND obsolete0 =0 AND Active0=1
         AND Operating_System_Name_and0 IS NOT NULL
     GROUP BY
     CASE
         WHEN Operating_System_Name_and0 LIKE '%workstation%' THEN 'Total WorkStation OS'
         WHEN Operating_System_Name_and0 LIKE '%server%' THEN 'Total Server OS'
     END

   • Machines Count By 32/64 bit OS Type

     SELECT
         systemtype00 as MachineType, count(distinct os.name0) as ClientCount
     FROM Computer_System_DATA pro
     JOIN v_R_System os on os.resourceid=pro.machineid
     WHERE
         os.client0=1 AND os.obsolete0=0 AND Active0=1
         AND systemtype00 in ('X86-based PC','x64-based PC')
     GROUP BY systemtype00
     ORDER BY 2 desc

   • Machines Count By Desktops/Notebooks Type

     SELECT  
     CASE ChassisTypes0
         WHEN '10' THEN 'Notebooks'
         ELSE 'Desktops'
     END AS "Workstation Type", count(distinct sys.name0) as ClientCount from
     v_GS_SYSTEM_ENCLOSURE ENC   
             INNER JOIN 
                 v_R_System SYS ON ENC.ResourceID = SYS.ResourceID   
     WHERE
         sys.client0=1 AND sys.obsolete0=0 AND active0=1
     GROUP BY
         CASE ChassisTypes0
             WHEN '10' THEN 'Notebooks'
             ELSE 'Desktops'
         END
     ORDER BY 2 desc

     Note: There are few more methods available for identifying machine types as Notebook vs. Desktops, please use appropriate method which meets your requirement as above query is used for dashboard purposes only.

   • Machines By Physical/Virtual Type 

     SELECT
     CASE Model0
         WHEN 'Virtual Machine' THEN 'Virtual Machine'
         ELSE 'Phyical Machine'
     END AS "Machine Type",
     COUNT(distinct sys.name0) as ClientCount
     FROM Computer_System_DATA ass join v_R_System sys on ass.machineID=sys.resourceID
     WHERE
         client0=1 AND obsolete0=0 AND Active0=1
     GROUP BY
         CASE Model0
             WHEN 'Virtual Machine' THEN 'Virtual Machine'
             ELSE 'Phyical Machine'
     END
     ORDER BY 2 desc
2. Dashboard for Update Compliance
   • Software Update Compliance by Update List/IDs

     SELECT
         vc.Name,at.title,
         CollectionID='SMS00001',
         Status=sn.StateName,
         NumberOfComputers=count(*),
         PComputers=convert(numeric(5,2), (isnull(count(*), 0)* 100.00 / isnull(nullif((SELECT count(*) FROM v_ClientCollectionMembers WHERE CollectionID='SMS00001') , 0), 1)))
     FROM v_ClientCollectionMembers cm
     JOIN v_Collection vc on vc.collectionID = cm.collectionid
     JOIN v_UpdateListStatus_Live cs on cs.CI_ID in (SELECT CI_ID FROM v_authlistinfo WHERE Title = @title)  AND cs.ResourceID=cm.ResourceID
     LEFT JOIN v_StateNames sn on sn.TopicType=300 AND sn.StateID=isnull(cs.Status, 0)
     JOIN v_authlistinfo at on cs.CI_ID=at.CI_ID
     WHERE
         cm.CollectionID='SMS00001'
     GROUP BY
         sn.StateName,vc.Name, at.title
     ORDER BY
         count(*) desc

     Note: The query uses update IDs or update list as query parameter (@title) for the compliance, so please configure the query parameter as shown below and set the default as appropriate. Also change the text in blue - collection ID to respective compliance collection as per your hierarchy.

     

3. Configuration Manager Administrator Dashboard
   • Site Server Count by Role Assigned

     SELECT
         r.rolename as RoleName,count (*) as ServerCount from v_systemresourcelist r
     WHERE
         rolename in ('SMS Management Point','SMS Site Server','SMS SQL Server','AI Update Service Point','SMS Distribution Point','SMS Software Update Point','SMS Fallback Status Point','SMS System Health Validator','SMS Reporting Point','SMS Server Locator Point')
     GROUP BY r.rolename
     ORDER BY 2 desc

   • Site Server Update Compliance
     • Note: Use above SQL query used for Update compliance and change the collection ID to site server collection ID
4. • Last WSUS Server Sync with Windows Update

     SELECT
         SiteCode, ContentVersion, SyncTime
     FROM
         update_syncstatus
     ORDER BY
         SyncTime

   • Last Successful Site Server Backup Date & Time

     SELECT
         stat.MachineName AS ServerName,stat.SiteCode AS SiteCode,MAX(stat.Time) AS LastBackUpTime
     FROM
         (SELECT a.* FROM v_StatusMessage a join v_StatusMessage b
         on a.sitecode = b.sitecode
         AND a.modulename = b.modulename
         AND a.recordid > b.recordid
     WHERE
         a.component = 'SMS_SITE_BACKUP'
         AND b.Component = 'SMS_SITE_BACKUP'
         AND a.MessageID in (501)
         AND b.messageID = 500
         AND not exists
     (SELECT * FROM v_StatusMessage where component = 'SMS_SITE_BACKUP' and sitecode = a.sitecode and messageid = 4610 and recordid > b.recordid and recordid < a.recordid)
     and not exists
     (SELECT * FROM v_StatusMessage where component = 'SMS_SITE_BACKUP' and sitecode = a.sitecode and messageid = 500 and recordid > b.recordid and recordid < a.recordid)
     and not exists
     (SELECT * FROM v_StatusMessage where component = 'SMS_SITE_BACKUP' and sitecode = a.sitecode and messageid in (501) and recordid > b.recordid and recordid < a.recordid)
     ) stat INNER JOIN v_Site sites
                     on sites.ServerName = stat.MachineName
     WHERE
         sites.Type = 2
     GROUP BY
         stat.SiteCode, stat.MachineName
     ORDER BY 3

   • Site Servers Not Communicated in last 24 hrs.

     SELECT DISTINCT
         SiteCode, Role
         , (SELECT TOP 1 TimeReported) AS LastReportedTime
         , getutcdate() AS CurrentTime
     FROM Summarizer_SiteSystem NOLOCK
     WHERE
         TimeReported < DATEADD(HOUR,-24, getutcdate())

   • Last 24 hrs. Error/Warning Status Messages Count by Site

     SELECT
         MessageID
         ,sm.Severity
         ,MachineName
         ,COUNT(*) as 'Count'
         ,MAX(Time) as 'LastOccurred' 
         ,Component         
     FROM v_StatusMessage sm WITH (NOLOCK)
     WHERE
         ModuleName = 'SMS Server'
         AND Sm.Severity != 1073741824
         AND Time > DATEADD(hour, -24, GetutcDate())
         AND SiteCode in ('[SiteCode1]','[SiteCode2]','[SiteCode3]')           
     GROUP BY
         MessageID, MachineName, Component, sm.Severity
     ORDER BY 4 desc

     Note: Change the text in blue for site code as appropriate

What to expect in next update on this dashboard blog series?

SQL queries used for following dashboard as referred in part 1 blog post

• Configuration Manager Client Health Dashboard
• Client Health Problem Management Dashboard
• App-V Dashboard
• LCD/Plasma Screen Dashboard
• Asset Management Dashboard

Disclaimer: The information on this site is provided "AS IS" with no warranties, confers no rights, and is not supported by the authors or Microsoft Corporation. Use of included script samples are subject to the terms specified in the Terms of Use