Another Configmgr 2012 SSRS Report that brings Client Health Summary.This time,it will be a dashboard instead normal SSRS report.Dashboard is nice way to display multiple reports into one report.Dashboard reports are very helpful if you want to monitor the information about client health (mainly the Count),client hardware/software,operating system,IE versions etc.You can also link all these individual reports to get list of computers with its hardware information.
I created Dashboard report to get client health summary into one Page instead browsing to multiple reports when required.
This report consists of several individual reports listed below:
1.Total Computers
2.Client Installed Vs Active Vs Missing
3.Client Health Based on CCMEVAL
4.Clients Assignment Per Management Point
5.Computers Discovered During last 30 Days
6.Computers Not Discovered During Last 30 Days
7.Clients Vs Non-Clients By Domain
8.Inventory (HW/SW) during last 30 days
9.Computers by Chassis Type
10.IE Versions
11.Computers by Architecture
12.Computers By Operating System
Note:Client health report is being filtered with lastlogon timestamp (<30 Days) attribute which is updated by AD system Discovery .
The Count in Crimson color indicates linked report to show list of computers which are not uploaded for now as there are many in this Dashboard.I will collate all of them during next update.
Download the RDL file from TechNet Gallery and uploaded to your SSRS report.
Don't forget to change the Data source after you upload it.To know more about how to upload the .RDL file and change Data source,follow http://be.enhansoft.com/post/2010/08/26/How-to-Change-the-SSRS-Datasource.aspx
47 Comments
Pingback: SSRS - Client Health Dashboard - sccmf12twice.com
Hi Eswar,
Can this report be edited to pull the data from Particular Collection, not for complete Environment.
Thanks
Sunil
Hi Sunil,
Yes, this can be achieved. Please use report builder to edit the RDL file and add filter for collection.
Regards,
Eswar
Hi Eswar,
Can you expand on this? I'd like to limit this to the collection - All Windows Workstation or Professional Systems.
Jim
Hi Jim,
I will look at it later this week to polish this and also add few more reports into client health summary based on collection.
Regards,
Eswar
I have a question. Why do I see more clients showing as ACTIVE than clients showing as INSTALLED?
You mean to say that, client installed are lesser than active clients? Client become active or inactive when it has sccm client installed. You need to deep look into the troubleshooting on this.
I have few inactive clients in my environment. But when I run this report, its not showing any inactive clients. where am I missing ?
Hi Shrisha,
Did you read note in the blog post
Note:Client health report is being filtered with lastlogon timestamp (<30 Days) attribute which is updated by AD system Discovery . Can you check what is the last logon time stamp for the inactive clients?
Hi Eshwar,
Yes, I read. And I changed it to 90 days instead of 30 so that I can get my inactive clients in the report. still no luck.
And modified query looks like below.
-------------------------------------------------------------------------------------------------------------------------------------------
DataSource1
select count(*) [Total Clients],
count(case when sys.client0=1 then '*' end) as 'Client Installed',
count(case when sys.Active0=1 then '*' end) as 'Active Clients',
count(case when sys.active0=0 then '*' end) as 'Inactive Clients',
count(case when sys.Obsolete0=1 then '*' end) as 'Obsolete Clients',
count(case when sys.Client0 is NULL then '*' end) as 'Missing Clients',
ROUND((CAST(count(case when sys.Active0 = 1 then '*' else NULL end) as float) /COUNT(sys.ResourceID) )*100,2) as 'Active %',
round((CAST(count(case when sys.Client0 is NULL then '*' else NULL end) as float)/COUNT(sys.ResourceID ) )*100,2) as 'Missing %'
from v_r_system sys
where DATEDIFF(dd,sys.Last_Logon_Timestamp0,GetDate()) <90
---------------------------------------------------------------------------------------------------------------------------------------------------------------------------
NOTE: I need
NOTE : I need only table named "client installed vs active vs missing" Hence i used part of the RDL file.
Is there any other way of editing the query, such that 'ClientActiveStatus' parameter is used to get inactive status, instead of 'Active0' parameter?
If yes, Can you help me please
Do you have field called clientactivestatus if so ,what are the values displayed for it ? if 1 active ,0 inactive something like that.
Try part of query in sql management studio and see if the inactive clients appear in the query.
No. Not listing there aswell
Try select * from v_r_system where name='pcname', see if the inactive tab display any value, if it doesn't then it is not inactive.
I tried select * from v_r_system where name0 ='pcname'
Weird thing is the column 'active0' is listing value 1 for both active and inactive computers.
NOTE : When I run below WQL query from sccm, im getting list of inactive clients
select SMS_R_SYSTEM.ResourceID,SMS_R_SYSTEM.ResourceType,SMS_R_SYSTEM.Name,SMS_R_SYSTEM.SMSUniqueIdentifier,SMS_R_SYSTEM.ResourceDomainORWorkgroup,SMS_R_SYSTEM.Client from SMS_R_System inner join SMS_G_System_CH_ClientSummary on SMS_G_System_CH_ClientSummary.ResourceID = SMS_R_System.ResourceId where SMS_G_System_CH_ClientSummary.ClientActiveStatus = 0
You are performing 2 different queries and they both work in different way based on client inventory (ccmeval )and DDR .
Client inactive can happen for several reasons ,read the this thread https://social.technet.microsoft.com/Forums/en-US/dcf47db0-55a4-4b0f-a8e5-caae7449a89d/when-sccm-client-activity-become-and-consider-as-inactive?forum=configmanagerdeployment
I have gone through the client settings. Its default settings
--------------------------------------------------------------------------------------------------------
Client policy requests during the following days - 7
Heartbeat discovery during the following days - 7
Hardware inventory during the following days -7
Software inventory during the following days - 7
Status messages during the following days -7
Retain client status history for the following number of days - 31 Days
------------------------------------------------------------------------------------------------------------------
Is there anything else to check to make the report work ?
Im new to SCCM. Sorry for extended queries 😐
try initiating the DDR on the client machine ,see if that makes any difference in the Console .I would see,it requires deep troubleshooting.
Hi,
awesome your actualisation. i have a question and i hope you can help. i would like to work with % Summerisation in the Section of Client Health Description and Count, the % would be in a new columen so i can directly see that x% from total not fine and so.
i try out with count but i know to less about creating of queries.
Can you help?
greetz
André
did you try download the all the reports from the url http://gallery.technet.microsoft.com/SCCM-Configmgr-2012-SSRS-2863c240 and try uploading to your SSRS folder to get the % and list of failed clients ?
Thank You very much. Much Appreciated.
Thanks Waiting for it.
I have updated the reports. you can download it from technet gallery.
report is loaded properly and shows data very well but if i click to missing clients it says sub report not found.
I will be posting all the linked reports soon.
This is excellent report. Would like to suggest a enhancement if possible. Is it possible to provide link on the numbers to navigate to the list of servers to see what they are.
Thanks,
VR
i will post soon...reports are ready.
Hi Eswar, you have done a very great job, now it´s running fine and looks very well.
Thank your for the powerful help and recreating of the dashboard.
Awesome, very awesome. 🙂
with best regards
André
My pleasure.. Thanks. You made me to correct it otherwise, it would have been no use for others.
My schedule is set on 7 days.
in my home test environment is the SQL State not running too, i work with a default installation and is MS default setting, Discoverys are enabled.
???
Let me recheck the query in my lab once again if any syntax errors.
Hello Eswar,
i try this report now in my home environment and the result is the same, inventory during last 30 days is empty.
@home i am working with SCCM 2012 R2 CU1 on Server 2012 R2 with SQL 2012 R2.
I don´t know what the Problem is, i activate AD System discovery too with every 2 days.
???
Hi,
I have modified the SQL Query for inventory. You can try downloading the updated one .
Let me know if that fixes your issue.
Hi,
is it possible that my problem comes from CAS?
with this line
SELECT sites.SMS_Assigned_Sites0 AS AssignedSite FROM v_RA_system_smsassignedsites sites
i becomes over 36000 clients back.
with this one
SELECT sites.SMS_Assigned_Sites0 AS AssignedSite FROM v_RA_system_smsassignedsites sites
where Sites.SMS_Assigned_Sites0 = 'XX0'
i see only 16000 managed clients by my location.
thanks for your help!!!
André
Okay, the Hardware Inventory is setup every 2 days @ 12:00 with default CLASSES. Our Clients connecting over VPN when they are not in office. The client status setting is 7 days for policy, heartbeat, hardware, software and status and for retain client is 31 days.
The are now 73 computers are very less so we have actually round about 12000 machines but i can not use the cool dashboard because there are no data. 🙁
i must learn more about sql query´s but i need more time.
i don´t understand why i see nothing as data.
Hi,
with this query i see now 78 Clients with the last HW Scan from May 2014 to June 2014.
So you think now that i have a Problem with my Clients inventory? I am in trouble, what can i do? 🙂
thx.
what is your hardware inventory scheduled ? is that everyday or weekly once ? also it depends on the if your users are using laptops /How often do they connect to office network ?
and Yes,you should look at all these computers why the inventory is not reported .
Hi,
the fields are in SQL Management Studio - Query Designer empty too and the query runs fine "QUERY EXECUTED SUCCESSFULLY" but the runtime is 0.00 and 0 rows are showed.
Only what i see is the header with AssignedSite, ActiveClients, HWSuccess and many more.
I checked out that my AD system discovery is running and it´s look fine.
???
With best regards
André
ok,can you try this simple report,to find computers not reporting inventory during last 30 days ?
select sys.name0 [Computer Name],chs.ClientActiveStatus,sys.Client_Version0,os.caption0 [OS],sys.User_Name0 [Last loggedon User Name],sys.Last_Logon_Timestamp0,CHS.LastMPServerName,ws.LastHWScan from v_R_System sys
left join v_GS_COMPUTER_SYSTEM cs on cs.ResourceID=sys.ResourceID
left join v_GS_WORKSTATION_STATUS ws on ws.ResourceID=sys.ResourceID
left join v_GS_OPERATING_SYSTEM os on os.ResourceID=sys.ResourceID
left join v_CH_ClientSummary CHS on CHS.ResourceID=sys.ResourceID
where DATEDIFF(dd,ws.LastHWScan,GetDate()) >30
and DATEDIFF(dd,sys.Last_Logon_Timestamp0,GetDate())<30
and sys.Client0 = 1 AND sys.Obsolete0 = 0 AND sys.Active0 = 1
order by 4 desc
if it doesn't give any results,then you should be fine and no worry about clients inventory troubleshooting.
Note: Please check Last_Logon_Timestamp0 attribute from v_r_system .this is been used to tell if computers are loggedinto to domain during last 30 days or not.
Hi,
your report dashboard looks fine but i have a problem with them.
The part of Inventory (HW/SW) during last 30 days is empty but i the AD System discovery is enabled, how can i fix it?
The part with IE Versions is empty too.
The other fields are filled out with date. 🙂
We use SCCM 2012 R2 CU1 and only Windows 7 x64 Clients.
With best regards
André
Empty ? wow,that's great ,which means you dont have any clients which are having issues to send inventory data..seems all are reporting in date.
For troubleshooting,here is the SQL query used to fill in the data. I think you will have to try to run by taking part of the query (either for hardware or software) and see if that reports any Data in SQL Management studio .
Note:Replace the quotes(,") as blog converts them to fancy.
SELECT sites.SMS_Assigned_Sites0 AS AssignedSite
,TotalSys.Total AS ActiveClients
,SuccSys.Succ AS HWSuccess
,SuccSys1.Succ AS HW_Not_Success
,SuccSW.Succ AS SWSuccess
,SuccSw1.Succ AS SW_Not_Success
,CONVERT(decimal(5,2),(SuccSys.Succ*100.00/TotalSys.Total)) AS 'HW Percentage'
,CONVERT(decimal(5,2),(SuccSW.Succ*100.00/TotalSys.Total)) AS 'SW Percentage'
FROM v_RA_system_smsassignedsites sites
INNER JOIN (
SELECT COUNT(*) AS Succ ,
sit.SMS_Assigned_Sites0 AS AssSite
FROM v_R_System sis
INNER JOIN v_gs_workstation_status sts
ON sis.ResourceID = sts.ResourceID
inner join v_RA_System_SMSAssignedSites sit
on sit.resourceid=sis.resourceID
AND sis.Client0 = 1
AND sis.Obsolete0 = 0
AND sis.Active0 = 1
--AND sts.LastHWScan < @olddate and DATEDIFF(dd,sts.LastHWScan,GetDate()) <30 and DATEDIFF(dd,sis.Last_Logon_Timestamp0,GetDate()) <30 group by sit.SMS_Assigned_Sites0 ) SuccSys ON sites.SMS_Assigned_Sites0 = SuccSys.AssSite INNER JOIN ( SELECT COUNT(*) AS Succ , sit.SMS_Assigned_Sites0 AS AssSite FROM v_R_System sis INNER JOIN v_gs_workstation_status sts ON sis.ResourceID = sts.ResourceID inner join v_RA_System_SMSAssignedSites sit on sit.resourceid=sis.resourceID AND sis.Client0 = 1 AND sis.Obsolete0 = 0 AND sis.Active0 = 1 --AND sts.LastHWScan < @olddate and DATEDIFF(dd,sts.LastHWScan,GetDate()) >30
and DATEDIFF(dd,sis.Last_Logon_Timestamp0,GetDate()) <30
group by sit.SMS_Assigned_Sites0
) SuccSys1
ON sites.SMS_Assigned_Sites0 = SuccSys.AssSite
INNER JOIN (
SELECT COUNT(*) AS Succ ,
sit.SMS_Assigned_Sites0 AS AssSite
FROM v_R_System sis
INNER JOIN v_GS_LastSoftwareScan sts
ON sis.ResourceID = sts.ResourceID
inner join v_RA_System_SMSAssignedSites sit
on sit.resourceid=sis.resourceID
AND sis.Client0 = 1
AND sis.Obsolete0 = 0
AND sis.Active0 = 1
--AND sts.LastHWScan < @olddate and DATEDIFF(dd,sts.LastScanDate,GetDate()) <30 and DATEDIFF(dd,sis.Last_Logon_Timestamp0,GetDate()) <30 group by sit.SMS_Assigned_Sites0 ) SuccSW ON SuccSW.AssSite = sites.SMS_Assigned_Sites0 INNER JOIN ( SELECT COUNT(*) AS Succ , sit.SMS_Assigned_Sites0 AS AssSite FROM v_R_System sis INNER JOIN v_GS_LastSoftwareScan sts ON sis.ResourceID = sts.ResourceID inner join v_RA_System_SMSAssignedSites sit on sit.resourceid=sis.resourceID AND sis.Client0 = 1 AND sis.Obsolete0 = 0 AND sis.Active0 = 1 --AND sts.LastHWScan < @olddate and DATEDIFF(dd,sts.LastScanDate,GetDate()) >30
and DATEDIFF(dd,sis.Last_Logon_Timestamp0,GetDate()) <30
group by sit.SMS_Assigned_Sites0
) SuccSW1
ON SuccSW.AssSite = sites.SMS_Assigned_Sites0
INNER JOIN (
SELECT sit.SMS_Assigned_Sites0 AS AssSite
,COUNT(DISTINCT sis.Netbios_Name0) AS Total
FROM v_RA_System_SMSAssignedSites sit
INNER JOIN v_R_system sis
ON sit.ResourceID = sis.ResourceID
inner join v_FullCollectionMembership FCM
ON FCM.ResourceID=sit.ResourceID
-- AND FCM.CollectionID='XXXXXXX'
AND sis.Client0 = 1
AND sis.Obsolete0 = 0
AND sis.Active0 = 1
and DATEDIFF(dd,sis.Last_Logon_Timestamp0,GetDate()) <30
GROUP BY sit.SMS_Assigned_Sites0
) TotalSys
ON sites.SMS_Assigned_Sites0 = TotalSys.Asssite
GROUP BY sites.SMS_Assigned_Sites0
,TotalSys.Total
,SuccSys.Succ
,SuccSys1.Succ
,SuccSW.Succ
,SuccSW1.Succ
ORDER BY 5 DESC
Works fine in SP1 CU3.
does the report require the sccm installation to be at a specific version? R2 only or something else?
it should work for all (Configmgr 2012) but have not tested otherthan R2 .Try and let me know if any issues.