SCCM Configmgr 2012 SSRS Dashboard Client Health Summary Report

Another Configmgr 2012 SSRS Report that brings Client Health Summary.This time,it will be a dashboard instead normal SSRS report.Dashboard is nice way to display multiple reports into one report.Dashboard reports are very helpful if you want to monitor the information about client health (mainly the Count),client hardware/software,operating system,IE versions etc.You can also link all these individual reports to get list of computers with its hardware information.

I created Dashboard report to get client health summary into one Page instead browsing to multiple reports when required.

This report consists of several individual reports listed below:

1.Total Computers

2.Client Installed Vs Active Vs Missing

3.Client Health Based on CCMEVAL

4.Clients Assignment Per Management Point

5.Computers Discovered During last 30 Days

6.Computers Not Discovered During Last 30 Days

7.Clients Vs Non-Clients By Domain

8.Inventory (HW/SW) during last 30 days

9.Computers by Chassis Type

10.IE Versions

11.Computers by Architecture

12.Computers By Operating System

Note:Client health report is being filtered with lastlogon timestamp (<30 Days) attribute which is updated by AD system Discovery .

The Count in Crimson color indicates linked report to show list of computers which are not uploaded for now as there are many in this Dashboard.I will collate all of them during next update.

image

Download the RDL file from TechNet Gallery and uploaded to your SSRS report.

Don't forget to change the Data source after you upload it.To know more about how to upload the .RDL file and change Data source,follow http://be.enhansoft.com/post/2010/08/26/How-to-Change-the-SSRS-Datasource.aspx

47 Responses to "SCCM Configmgr 2012 SSRS Dashboard Client Health Summary Report"

    1. Hi Sunil,
      Yes, this can be achieved. Please use report builder to edit the RDL file and add filter for collection.

      Regards,
      Eswar

      Reply
      1. Hi Eswar,

        Can you expand on this? I'd like to limit this to the collection - All Windows Workstation or Professional Systems.

        Jim

        Reply
        1. Hi Jim,
          I will look at it later this week to polish this and also add few more reports into client health summary based on collection.

          Regards,
          Eswar

          Reply
    1. You mean to say that, client installed are lesser than active clients? Client become active or inactive when it has sccm client installed. You need to deep look into the troubleshooting on this.

      Reply
  1. I have few inactive clients in my environment. But when I run this report, its not showing any inactive clients. where am I missing ?

    Reply
    1. Hi Shrisha,
      Did you read note in the blog post
      Note:Client health report is being filtered with lastlogon timestamp (<30 Days) attribute which is updated by AD system Discovery . Can you check what is the last logon time stamp for the inactive clients?

      Reply
      1. Hi Eshwar,

        Yes, I read. And I changed it to 90 days instead of 30 so that I can get my inactive clients in the report. still no luck.

        Reply
        1. And modified query looks like below.
          -------------------------------------------------------------------------------------------------------------------------------------------

          DataSource1
          select count(*) [Total Clients],
          count(case when sys.client0=1 then '*' end) as 'Client Installed',
          count(case when sys.Active0=1 then '*' end) as 'Active Clients',
          count(case when sys.active0=0 then '*' end) as 'Inactive Clients',
          count(case when sys.Obsolete0=1 then '*' end) as 'Obsolete Clients',
          count(case when sys.Client0 is NULL then '*' end) as 'Missing Clients',
          ROUND((CAST(count(case when sys.Active0 = 1 then '*' else NULL end) as float) /COUNT(sys.ResourceID) )*100,2) as 'Active %',
          round((CAST(count(case when sys.Client0 is NULL then '*' else NULL end) as float)/COUNT(sys.ResourceID ) )*100,2) as 'Missing %'
          from v_r_system sys
          where DATEDIFF(dd,sys.Last_Logon_Timestamp0,GetDate()) <90
          ---------------------------------------------------------------------------------------------------------------------------------------------------------------------------
          NOTE: I need

          Reply
            1. Is there any other way of editing the query, such that 'ClientActiveStatus' parameter is used to get inactive status, instead of 'Active0' parameter?

              If yes, Can you help me please

            2. Do you have field called clientactivestatus if so ,what are the values displayed for it ? if 1 active ,0 inactive something like that.

            1. Try select * from v_r_system where name='pcname', see if the inactive tab display any value, if it doesn't then it is not inactive.

            2. I tried select * from v_r_system where name0 ='pcname'

              Weird thing is the column 'active0' is listing value 1 for both active and inactive computers.

              NOTE : When I run below WQL query from sccm, im getting list of inactive clients

              select SMS_R_SYSTEM.ResourceID,SMS_R_SYSTEM.ResourceType,SMS_R_SYSTEM.Name,SMS_R_SYSTEM.SMSUniqueIdentifier,SMS_R_SYSTEM.ResourceDomainORWorkgroup,SMS_R_SYSTEM.Client from SMS_R_System inner join SMS_G_System_CH_ClientSummary on SMS_G_System_CH_ClientSummary.ResourceID = SMS_R_System.ResourceId where SMS_G_System_CH_ClientSummary.ClientActiveStatus = 0

            3. I have gone through the client settings. Its default settings
              --------------------------------------------------------------------------------------------------------
              Client policy requests during the following days - 7
              Heartbeat discovery during the following days - 7
              Hardware inventory during the following days -7
              Software inventory during the following days - 7
              Status messages during the following days -7
              Retain client status history for the following number of days - 31 Days
              ------------------------------------------------------------------------------------------------------------------
              Is there anything else to check to make the report work ?

              Im new to SCCM. Sorry for extended queries 😐

            4. try initiating the DDR on the client machine ,see if that makes any difference in the Console .I would see,it requires deep troubleshooting.

  2. Hi,

    awesome your actualisation. i have a question and i hope you can help. i would like to work with % Summerisation in the Section of Client Health Description and Count, the % would be in a new columen so i can directly see that x% from total not fine and so.

    i try out with count but i know to less about creating of queries.

    Can you help?

    greetz
    André

    Reply
  3. This is excellent report. Would like to suggest a enhancement if possible. Is it possible to provide link on the numbers to navigate to the list of servers to see what they are.

    Thanks,
    VR

    Reply
  4. Hi Eswar, you have done a very great job, now it´s running fine and looks very well.

    Thank your for the powerful help and recreating of the dashboard.

    Awesome, very awesome. 🙂

    with best regards
    André

    Reply
  5. My schedule is set on 7 days.
    in my home test environment is the SQL State not running too, i work with a default installation and is MS default setting, Discoverys are enabled.

    ???

    Reply
  6. Hello Eswar,

    i try this report now in my home environment and the result is the same, inventory during last 30 days is empty.

    @home i am working with SCCM 2012 R2 CU1 on Server 2012 R2 with SQL 2012 R2.

    I don´t know what the Problem is, i activate AD System discovery too with every 2 days.
    ???

    Reply
    1. Hi,
      I have modified the SQL Query for inventory. You can try downloading the updated one .
      Let me know if that fixes your issue.

      Reply
  7. Hi,
    is it possible that my problem comes from CAS?
    with this line
    SELECT sites.SMS_Assigned_Sites0 AS AssignedSite FROM v_RA_system_smsassignedsites sites
    i becomes over 36000 clients back.

    with this one
    SELECT sites.SMS_Assigned_Sites0 AS AssignedSite FROM v_RA_system_smsassignedsites sites
    where Sites.SMS_Assigned_Sites0 = 'XX0'
    i see only 16000 managed clients by my location.

    thanks for your help!!!
    André

    Reply
  8. Okay, the Hardware Inventory is setup every 2 days @ 12:00 with default CLASSES. Our Clients connecting over VPN when they are not in office. The client status setting is 7 days for policy, heartbeat, hardware, software and status and for retain client is 31 days.

    The are now 73 computers are very less so we have actually round about 12000 machines but i can not use the cool dashboard because there are no data. 🙁

    i must learn more about sql query´s but i need more time.

    i don´t understand why i see nothing as data.

    Reply
  9. Hi,
    with this query i see now 78 Clients with the last HW Scan from May 2014 to June 2014.

    So you think now that i have a Problem with my Clients inventory? I am in trouble, what can i do? 🙂

    thx.

    Reply
    1. what is your hardware inventory scheduled ? is that everyday or weekly once ? also it depends on the if your users are using laptops /How often do they connect to office network ?
      and Yes,you should look at all these computers why the inventory is not reported .

      Reply
  10. Hi,
    the fields are in SQL Management Studio - Query Designer empty too and the query runs fine "QUERY EXECUTED SUCCESSFULLY" but the runtime is 0.00 and 0 rows are showed.
    Only what i see is the header with AssignedSite, ActiveClients, HWSuccess and many more.

    I checked out that my AD system discovery is running and it´s look fine.

    ???

    With best regards
    André

    Reply
    1. ok,can you try this simple report,to find computers not reporting inventory during last 30 days ?
      select sys.name0 [Computer Name],chs.ClientActiveStatus,sys.Client_Version0,os.caption0 [OS],sys.User_Name0 [Last loggedon User Name],sys.Last_Logon_Timestamp0,CHS.LastMPServerName,ws.LastHWScan from v_R_System sys
      left join v_GS_COMPUTER_SYSTEM cs on cs.ResourceID=sys.ResourceID
      left join v_GS_WORKSTATION_STATUS ws on ws.ResourceID=sys.ResourceID
      left join v_GS_OPERATING_SYSTEM os on os.ResourceID=sys.ResourceID
      left join v_CH_ClientSummary CHS on CHS.ResourceID=sys.ResourceID
      where DATEDIFF(dd,ws.LastHWScan,GetDate()) >30
      and DATEDIFF(dd,sys.Last_Logon_Timestamp0,GetDate())<30
      and sys.Client0 = 1 AND sys.Obsolete0 = 0 AND sys.Active0 = 1
      order by 4 desc

      if it doesn't give any results,then you should be fine and no worry about clients inventory troubleshooting.
      Note: Please check Last_Logon_Timestamp0 attribute from v_r_system .this is been used to tell if computers are loggedinto to domain during last 30 days or not.

      Reply
  11. Hi,
    your report dashboard looks fine but i have a problem with them.
    The part of Inventory (HW/SW) during last 30 days is empty but i the AD System discovery is enabled, how can i fix it?
    The part with IE Versions is empty too.
    The other fields are filled out with date. 🙂

    We use SCCM 2012 R2 CU1 and only Windows 7 x64 Clients.

    With best regards
    André

    Reply
    1. Empty ? wow,that's great ,which means you dont have any clients which are having issues to send inventory data..seems all are reporting in date.
      For troubleshooting,here is the SQL query used to fill in the data. I think you will have to try to run by taking part of the query (either for hardware or software) and see if that reports any Data in SQL Management studio .
      Note:Replace the quotes(,") as blog converts them to fancy.

      SELECT sites.SMS_Assigned_Sites0 AS AssignedSite
      ,TotalSys.Total AS ActiveClients
      ,SuccSys.Succ AS HWSuccess
      ,SuccSys1.Succ AS HW_Not_Success
      ,SuccSW.Succ AS SWSuccess
      ,SuccSw1.Succ AS SW_Not_Success
      ,CONVERT(decimal(5,2),(SuccSys.Succ*100.00/TotalSys.Total)) AS 'HW Percentage'
      ,CONVERT(decimal(5,2),(SuccSW.Succ*100.00/TotalSys.Total)) AS 'SW Percentage'
      FROM v_RA_system_smsassignedsites sites
      INNER JOIN (
      SELECT COUNT(*) AS Succ ,
      sit.SMS_Assigned_Sites0 AS AssSite
      FROM v_R_System sis
      INNER JOIN v_gs_workstation_status sts
      ON sis.ResourceID = sts.ResourceID
      inner join v_RA_System_SMSAssignedSites sit
      on sit.resourceid=sis.resourceID
      AND sis.Client0 = 1
      AND sis.Obsolete0 = 0
      AND sis.Active0 = 1
      --AND sts.LastHWScan < @olddate and DATEDIFF(dd,sts.LastHWScan,GetDate()) <30 and DATEDIFF(dd,sis.Last_Logon_Timestamp0,GetDate()) <30 group by sit.SMS_Assigned_Sites0 ) SuccSys ON sites.SMS_Assigned_Sites0 = SuccSys.AssSite INNER JOIN ( SELECT COUNT(*) AS Succ , sit.SMS_Assigned_Sites0 AS AssSite FROM v_R_System sis INNER JOIN v_gs_workstation_status sts ON sis.ResourceID = sts.ResourceID inner join v_RA_System_SMSAssignedSites sit on sit.resourceid=sis.resourceID AND sis.Client0 = 1 AND sis.Obsolete0 = 0 AND sis.Active0 = 1 --AND sts.LastHWScan < @olddate and DATEDIFF(dd,sts.LastHWScan,GetDate()) >30
      and DATEDIFF(dd,sis.Last_Logon_Timestamp0,GetDate()) <30
      group by sit.SMS_Assigned_Sites0
      ) SuccSys1
      ON sites.SMS_Assigned_Sites0 = SuccSys.AssSite
      INNER JOIN (
      SELECT COUNT(*) AS Succ ,
      sit.SMS_Assigned_Sites0 AS AssSite
      FROM v_R_System sis
      INNER JOIN v_GS_LastSoftwareScan sts
      ON sis.ResourceID = sts.ResourceID
      inner join v_RA_System_SMSAssignedSites sit
      on sit.resourceid=sis.resourceID
      AND sis.Client0 = 1
      AND sis.Obsolete0 = 0
      AND sis.Active0 = 1
      --AND sts.LastHWScan < @olddate and DATEDIFF(dd,sts.LastScanDate,GetDate()) <30 and DATEDIFF(dd,sis.Last_Logon_Timestamp0,GetDate()) <30 group by sit.SMS_Assigned_Sites0 ) SuccSW ON SuccSW.AssSite = sites.SMS_Assigned_Sites0 INNER JOIN ( SELECT COUNT(*) AS Succ , sit.SMS_Assigned_Sites0 AS AssSite FROM v_R_System sis INNER JOIN v_GS_LastSoftwareScan sts ON sis.ResourceID = sts.ResourceID inner join v_RA_System_SMSAssignedSites sit on sit.resourceid=sis.resourceID AND sis.Client0 = 1 AND sis.Obsolete0 = 0 AND sis.Active0 = 1 --AND sts.LastHWScan < @olddate and DATEDIFF(dd,sts.LastScanDate,GetDate()) >30
      and DATEDIFF(dd,sis.Last_Logon_Timestamp0,GetDate()) <30
      group by sit.SMS_Assigned_Sites0
      ) SuccSW1
      ON SuccSW.AssSite = sites.SMS_Assigned_Sites0
      INNER JOIN (
      SELECT sit.SMS_Assigned_Sites0 AS AssSite
      ,COUNT(DISTINCT sis.Netbios_Name0) AS Total
      FROM v_RA_System_SMSAssignedSites sit
      INNER JOIN v_R_system sis
      ON sit.ResourceID = sis.ResourceID
      inner join v_FullCollectionMembership FCM
      ON FCM.ResourceID=sit.ResourceID
      -- AND FCM.CollectionID='XXXXXXX'
      AND sis.Client0 = 1
      AND sis.Obsolete0 = 0
      AND sis.Active0 = 1
      and DATEDIFF(dd,sis.Last_Logon_Timestamp0,GetDate()) <30
      GROUP BY sit.SMS_Assigned_Sites0
      ) TotalSys
      ON sites.SMS_Assigned_Sites0 = TotalSys.Asssite
      GROUP BY sites.SMS_Assigned_Sites0
      ,TotalSys.Total
      ,SuccSys.Succ
      ,SuccSys1.Succ
      ,SuccSW.Succ
      ,SuccSW1.Succ
      ORDER BY 5 DESC

      Reply

Post Comment