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,
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,
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.