Being as SCCM Configmgr administrator, your primary aspect is to maintain health of your sccm clients for application deployment,software updates,inventory etc. Questions often come up in forums ,email list asking for ,list of computers not contacted the server since X days (25) or how do I know the clients who’s policy request is old or days since last communication is more than X days older.
From the Configmgr Console,we can see the client activity like policy request,heartbeat DDR,hardware scan ,Management point etc as shown in below picture.
How do I present this information into SSRS report for further troubleshooting ? Creating report ,exporting results and working /troubleshooting clients who’s communication is older than x days would be easier.
The client activity information is stored in view called vWorkstationStatus in SQL DB.So I use this view to join with collection views to get desired results.
I have SQL Query to retrieve this information but not in SSRS Report. I usually run the SQL Query in SQL Management studio but I thought of creating SSRS report to get this information against 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?
Download the RDL File from TechNet here ,upload report to your SSRS Reports folder ,change the Data source and runt the report.
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/
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 ?