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://www.reddit.com/r/SCCM/comments/3l67e8/help_with_custom_report/
15 Comments
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!
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
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)"
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.
I was able to run it in SQL Mgmt Studio fine.
i ran the report,it works good. Let me do it in other environment to see what is wrong.
Was there a resolution for the "Query execution failed for dataset 'DataSet1'. (rsErrorexecutingCommand)" error when running the report? I am having the same issue.
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
And also, CM 2012
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.
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
How to we resolve Communication 0?
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.
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.
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 ?