Close Menu
    Facebook X (Twitter) Instagram
    Sunday, July 20
    X (Twitter) LinkedIn
    All about Endpoint Management
    • Home
    All about Endpoint Management
    Home»CM2012»Investigating SCCM Client Policy Request and Communication Status with a Collection Specific Report

    Investigating SCCM Client Policy Request and Communication Status with a Collection Specific Report

    Eswar KonetiBy Eswar KonetiOctober 15, 11:22 pm3 Mins Read CM2012 15,779 Views
    Share
    Facebook Twitter LinkedIn Reddit

    Introduction:

    As a SCCM Configmgr administrator, one of your key responsibilities is to ensure the health of SCCM clients for tasks like application deployment, software updates, and inventory management.

    Oftentimes, you may encounter situations where you need to identify computers that haven't contacted the server in a specific number of days or determine the clients with outdated policy requests or communication gaps.

    This blog post aims to guide you on creating an SSRS report to present this information, allowing for easier troubleshooting and maintenance of SCCM clients.

    Retrieving Client Activity Information:

    In the Configuration Manager (ConfigMgr) Console, you have the ability to view and monitor various client activities. These activities include policy requests, heartbeat DDR (Discovery Data Record), hardware scans, and management point as shown in below picture.

    image

    To begin, we need to retrieve the client activity information from the SQL database. The data is stored in a view called vWorkstationStatus, which can be joined with collection views to obtain the desired results. Below is an SQL query that retrieves clients whose LastPolicyRequest is more than 25 days old, filtered by a specific collection.

    Below is SQL query ,if you want to create powershell grid view. (This query is filtered to get clients if LastPolicyRequest request is more than 25 days and collection).

    SELECT ws.Name, ws.UserName,ws.LastMPServerName,
    CONVERT ( varchar (26) , ws.CreationDate , 100) CreationDate,
    CONVERT ( varchar (26) , ws.LastDDR , 100) LastDDR,
    CONVERT ( varchar (26) , ws.LastPolicyRequest,100) LastPolicyRequest,
    CONVERT ( varchar (26) , ws.LastHardwareScan,100) LastHardwareScan,
    ws.SystemRole,
    DATEDIFF ( DAY , ws.LastPolicyRequest , getdate() ) 'Days Since Last Communication'
    FROM  vWorkstationStatus ws
    JOIN dbo.v_FullCollectionMembership vfcm ON vfcm.ResourceID = ws.ResourceID
    JOIN dbo.v_Collection vc ON vc.CollectionID = vfcm.CollectionID
    WHERE vc.Name='Your collection Name'
    GROUP BY ws.Name, ws.UserName,ws.CreationDate,ws.LastMPServerName,ws.LastDDR,
    ws.LastPolicyRequest,ws.LastHardwareScan,ws.SystemRole
    HAVING (datediff(dd,max(ws.LastPolicyRequest),getdate()-25) >0)

    In SSRS Report, I have made 2 parameters as prompts 1) Collection Name and 2) Number of days .

    Below is SSRS report how it looks like?

    image

    To access the complete SSRS report, download the RDL file from the provided TechNet here. Once downloaded, upload the report to your SSRS Reports folder. Don't forget to update the data source to connect to your SQL database. After making the necessary configurations, run the report to generate the desired results.

    Conclusion:

    By utilizing SSRS reports in SCCM, you can easily monitor and troubleshoot client communication issues. This blog post has provided you with an SQL query to retrieve client activity information and guided you through the process of creating an SSRS report. With this report, you can efficiently identify clients with outdated policy requests or communication gaps, enabling you to take appropriate actions to maintain the health of your SCCM environment.

    Questions appeared on https://social.technet.microsoft.com/Forums/en-US/b9940352-7382-462d-8ef9-8c7a1483a29f/sccm-client-last-communication-report?forum=configmanagerdeployment

    https://social.technet.microsoft.com/Forums/office/en-US/5b21299a-d91a-43bd-bae7-dc02c09a8694/how-to-create-a-collection-based-on-days-since-last-communications?forum=configmanagerapps

    https://www.reddit.com/r/SCCM/comments/3l67e8/help_with_custom_report/

    Client policy request configmgr Days Since Last Communication Hardware scan Last communication date Last DDR report SCCM 2012 SCCM Report client last communication SQL Report SSRS Report
    Share. Twitter LinkedIn Email Facebook Reddit

    Related Posts

    SCCM SQL Report – Compare Installed Apps on TWO Different Computers

    July 13, 10:35 am

    Optimize Your Intune Workflow with a Powerful Browser Extension

    March 22, 10:39 am

    Migrate Microsoft 365 Updates from SCCM/MECM to Intune for Co-Managed Devices

    February 11, 9:50 pm

    15 Comments

    1. David on July 12, 2023 1:25 AM

      Hey Eswar, thanks for the great post.

      I too had the permissions problems, but think I found a SQL view that does the same job, without the the errors:

      dbo.v_CH_ClientSummary

      This is in Config Manager 2303.

      Thanks!

      Reply
    2. Nickcx on June 13, 2017 11:29 PM

      Hi Eswar,
      I am trying to enhance a standard report Compliance 7 - Computers in a specific compliance state for an update group (secondary) and I want to add your timestamp report! I've done it ok for other extra data (e.g. Division, from an extended HINV) but I'd like to add the timestamp info. However I get this error:

      "An error has occurred during report processing. (rsProcessingAborted)
      Query execution failed for dataset 'DataSet0'. (rsErrorExecutingCommand)
      The SELECT permission was denied on the object 'vWorkstationStatus', database 'CM_I01', schema 'dbo'"

      I am full admin and have no problems running your original query or other reports. Is there something I've done wrong?
      Thank you for any help!
      ===

      declare @CI_ID int; select @CI_ID=CI_ID from fn_rbac_ConfigurationItems(@UserSIDs) where CIType_ID=9 and CI_UniqueID=@AuthListID

      declare @StateID int
      select @StateID=StateID from fn_rbac_StateNames(@UserSIDs) sn where sn.StateName=@StateName and TopicType=300

      select
      ccm.ResourceID,
      rs.Name0+isnull('.'+rs.Resource_Domain_or_Workgr0, '') as MachineName,
      rs.User_Domain0+'\'+User_Name0 as LastLoggedOnUser,
      asite.SMS_Assigned_Sites0 as AssignedSite,
      rs.Client_Version0 as ClientVersion,
      div0.Current_Division0 as CurrentDivision,

      CONVERT ( varchar (26) , ws.CreationDate , 100) CreationDate,
      CONVERT ( varchar (26) , ws.LastDDR , 100) LastDDR,
      CONVERT ( varchar (26) , ws.LastPolicyRequest,100) LastPolicyRequest,
      CONVERT ( varchar (26) , ws.LastHardwareScan,100) LastHardwareScan,
      ws.SystemRole,
      DATEDIFF ( DAY , ws.LastPolicyRequest , getdate() ) 'DaysSince'

      from fn_rbac_ClientCollectionMembers(@UserSIDs) ccm
      join fn_rbac_Update_ComplianceStatusAll(@UserSIDs) cs on cs.CI_ID=@CI_ID and cs.ResourceID=ccm.ResourceID
      and (@StateID=0 and cs.Status=0 or @StateID=1 and cs.Status in (1,3) or @StateID=2 and cs.Status=2)
      join fn_rbac_R_System(@UserSIDs) rs on rs.ResourceID = ccm.ResourceID
      join vWorkstationStatus ws on ccm.ResourceID=ws.ResourceID
      join v_FullCollectionMembership vfcm ON vfcm.ResourceID = ws.ResourceID
      join v_Collection vc ON vc.CollectionID = vfcm.CollectionID
      left join fn_rbac_RA_System_SMSAssignedSites(@UserSIDs) asite on asite.ResourceID = ccm.ResourceID
      left join v_GS_DIVISION0 div0 on ccm.ResourceID=div0.ResourceID

      where ccm.CollectionID=@CollID
      order by MachineName

      Reply
    3. Mike on December 3, 2015 2:18 AM

      Hi Eswar, I tried using this report. I added a new data source and updated DataSet1 to reflect the new source, but I'm getting an error - "Query execution failed for dataset 'DataSet1'. (rsErrorexecutingCommand)"

      Reply
      • Eswar Koneti on December 6, 2015 10:28 PM

        it looks like ,some data not available in your SCCM 2012 DB. are you running this report in CM12 or CM07 ? you can try to execute the query posted in the blog from SQL management studio to check if it works there or not.

        Reply
        • Mike on December 8, 2015 5:44 AM

          I was able to run it in SQL Mgmt Studio fine.

          Reply
          • Eswar Koneti on December 13, 2015 11:34 PM

            i ran the report,it works good. Let me do it in other environment to see what is wrong.

            Reply
            • James McDonald on October 30, 2018 4:09 AM

              Was there a resolution for the "Query execution failed for dataset 'DataSet1'. (rsErrorexecutingCommand)" error when running the report? I am having the same issue.

              Reply
              • Eswar Koneti on November 2, 2018 2:24 PM

                it looks like the sql view used in dataset1 either missing in your configmgr database or something went wrong.
                can you edit the RDL file using report builder or other tools and see if the SQL used in dataset1 has data that exist in your database ? Run the SQL code in management studio to know what data is missing.

                Thanks,
                Eswar

        • Mike on December 8, 2015 5:45 AM

          And also, CM 2012

          Reply
        • Ian North on October 22, 2019 7:25 PM

          I also had this issue. I ran it on the SSRS web console and it returned the following error:

          An error has occurred during report processing. (rsProcessingAborted)
          Query execution failed for dataset 'DataSet1'. (rsErrorExecutingCommand)
          The SELECT permission was denied on the object 'vWorkstationStatus', database 'CM_HAF', schema 'dbo'.

          To fix it, I gave the account defined as the ConfigMgr Reporting Services Point account the db_datareader role on the ConfigMgr database. The query then ran.

          Reply
          • Eswar Koneti on October 22, 2019 10:09 PM

            Hi Ian,
            Thanks for the info and yes ,vworkstation is not SQL view and need explicit permission on the database for the user who run the report or you can use the datasource that is configured to run the SSRS or other account that has enough permissions to run the report.

            Thanks,
            Eswar

            Reply
    4. Tuan on November 20, 2015 4:21 AM

      How to we resolve Communication 0?

      Reply
      • Eswar Koneti on November 23, 2015 11:00 AM

        You don't have to .Days since last communication 0 are good clients. If the last communication days are higher ,then something wrong with client and requires troubleshooting. Troubleshooting may involve both on client side and server side depends on how you troubleshoot and health of sccm client.

        Reply
    5. Syed on September 29, 2015 10:52 PM

      Hello Eswar,

      Thanks for the above post. i have one question on views and tables. Will view and table names are depends on the environment or will it be same in all the SCCM environment?? because i don't see Vworkstationstatus view in my SQL DB.i could V_Gs_workstation_status in my SQL DB.

      Reply
      • Eswar Koneti on October 2, 2015 8:08 AM

        Vworkstationstatus view exisit by default when Configmgr 2012 installed database successfully ,it is not custom view.What error do you see when running the query ?

        Reply

    Leave a ReplyCancel reply

    This site uses Akismet to reduce spam. Learn how your comment data is processed.

    Sign Up

    Get email notifications for new posts.

    Author

    I’m Eswar Koneti ,a tech enthusiast, security advocate, and your guide to Microsoft Intune and Modern Device Management. My goal? To turn complex tech into actionable insights for a streamlined management experience. Let’s navigate this journey together!

    Support

    Awards

    Archives

    © Copyright 2009-2024 Eswar Koneti, All rights reserved.

    Type above and press Enter to search. Press Esc to cancel.