SCCM Configmgr 2012 SSRS Report client policy request or Days Since Last Communication older for specific collection

 

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.

image

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?

image

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://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/

9 Responses to "SCCM Configmgr 2012 SSRS Report client policy request or Days Since Last Communication older for specific collection"

  1. 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
    1. 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
    1. 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
  2. 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
    1. 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 Reply