Configuration Manager in Microsoft IT

Real World Experiences from Microsoft IT

Part 3: SQL queries used for creating custom Configuration Manager Dashboard in Microsoft IT

Part 3: SQL queries used for creating custom Configuration Manager Dashboard in Microsoft IT

  • Comments 7

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.

      image

    • 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

    • 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.

        image

    • 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
      • 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

    Comments
    • Shitanshu : Thank you for the SQL codes. Do you have any doc on how to create such reports , a step by step one,

    • Any rough date when the rest of the SQL will be available for the other custom dashboards?

      Particularly looking for the client health and Asset ones if possible.

      Cheers!

    • Another quicjk Question, how do I get my charts larger?

      I tried removing the fixed width but they are still very small and hard to read

    • When is the next update on this dashboard blog series as the SQL queries posted are extremely helpful!!

    • In " Part 3: SQL queries used for creating custom Configuration Manager Dashboard in Microsoft IT

    • Hello Shitanshu. I´d like to make a question. Why still in SCCM 2012, we need to build SQL queries to build the Dashboards. There´s no another way to build them? No wizard? For example. You know that there are the tables in the SQL db, what they do, and usually what type of information are there. Why not build a wizard that allow us to simply choose the tables, input the desired values (if we need to customize) and build an output graph for the dashboard? I think sccm as a very good tool, but for every query that is not on the interface, you need to build a query for that. Best Regards, Germano.

    • Hi all,

      please could you help me. where is the information stored about the lasted download Definition file from ForeFront ? Do I need to parse the Update Table by looking for the Description from Forefront to find this kind of information ?

      thx for any kind of information.

      Klaus

    Page 1 of 1 (7 items)
    Leave a Comment
    • Please add 1 and 3 and type the answer here:
    • Post