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

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.

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?

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/