Reduce OSD Time

, , ,

SSRS – Client Health Dashboard

, , , , ,
Client Health Dashboard
In previous posts I was speaking about soon to be releasing a series of SSRS reports based on troubleshooting. The first in the series I am trying to have is a focus on Client Health Overview. This dashboard will later be include drill down functionality to multiple other reports as soon as I can finish making and validating them. This dashboard is something that I put together for my current customer to get a brief overview of client health. There will be a few more items added to the home page at a later time which will be focused on count vulnerable/unpatched systems
Currently we can see in this report Active clients, MP information, Client version counts, OS version counts. There are even a few top level items you should keep an eye on like duplicate systems, mac addresses, systems running out of space, or why clients failed to install/re-evaluate. These will all be further expaneded on when the full client health troubleshooting series wraps up over the next few weeks.
SSRS Client Health Dashboard

Link to the Report: https://gallery.technet.microsoft.com/SSRS-Client-Health-6bfb794f

Some code used from: Greg Ramsey and Eswor Koneti

Future Dashboards w/ drilldowns coming soon.

– Client Health (will be finished soon)

  • This will also include drill downs into several other reports

– Software Deployment (For Packages / For Apps)

  • This will also include how to troubleshoot just like this other report
    SSRS Client Health Dashboard
– Infrastructure health (Primary/DP/SQL/DP etc health focused)
  • This will also include how to troubleshoot just like this other report
  • This will also include drill downs into several other reports
– Windows Migration Summary
SSRS Client Health Dashboard

 

– Collection evaluation

 

Collection Schedule Query

, , , , ,

SCCM Collection Schedule Queries.

There is no real readable output of the SCCM Collection schedule for us to understand. What is produced  is 16 character string. If you want to really find out what this translated to then you have to look into the SDK for translation. Also, there really are not any good existing reports/queries out there to cover this information via SQL. This is the query I came up with for my customers environment when trying to evaluate their SCCM Collection evaluation problems. Also I will do another blogpost with the sql queries to identify a number of other items to include orphaned collections, SCCM Collection  update types, etc… There is a screenshot in the bottom of this post to give a preview of the information.

Let’s show an example.

There schedule produced “29B66B4000100200” is not readable. You have to look up this information in the SDK to start to get an understanding.

The First “7” digits translate to the effective schedule date for example: Effective 6/22/2015 1:10pm
The last “9” digits translate to the actual schedule that is performed for example: Every 2 hours

 

SCCM Collection

The code:

Select

CG.CollectionName,

CG.SITEID AS [Collection ID],

CASE VC.CollectionType

WHEN 0 THEN ‘Other’

WHEN 1 THEN ‘User’

WHEN 2 THEN ‘Device’

ELSE ‘Unknown’ END AS CollectionType,

CG.schedule, case

WHEN CG.Schedule like ‘%000102000’ THEN ‘Every 1 minute’

WHEN CG.Schedule like ‘%00010A000’ THEN ‘Every 5 mins’

WHEN CG.Schedule like ‘%000114000’ THEN ‘Every 10 mins’

WHEN CG.Schedule like ‘%00011E000’ THEN ‘Every 15 mins’

WHEN CG.Schedule like ‘%000128000’ THEN ‘Every 20 mins’

WHEN CG.Schedule like ‘%000132000’ THEN ‘Every 25 mins’

WHEN CG.Schedule like ‘%00013C000’ THEN ‘Every 30 mins’

WHEN CG.Schedule like ‘%000150000’ THEN ‘Every 40 mins’

WHEN CG.Schedule like ‘%00015A000’ THEN ‘Every 45 mins’

WHEN CG.Schedule like ‘%000100100’ THEN ‘Every 1 hour’

WHEN CG.Schedule like ‘%000100200’ THEN ‘Every 2 hours’

WHEN CG.Schedule like ‘%000100300’ THEN ‘Every 3 hours’

WHEN CG.Schedule like ‘%000100400’ THEN ‘Every 4 hours’

WHEN CG.Schedule like ‘%000100500’ THEN ‘Every 5 hours’

WHEN CG.Schedule like ‘%000100600’ THEN ‘Every 6 hours’

WHEN CG.Schedule like ‘%000100700’ THEN ‘Every 7 hours’

WHEN CG.Schedule like ‘%000100B00’ THEN ‘Every 11 Hours’

WHEN CG.Schedule like ‘%000100C00’ THEN ‘Every 12 Hours’

WHEN CG.Schedule like ‘%000101000’ THEN ‘Every 16 Hours’

WHEN CG.Schedule like ‘%000100008’ THEN ‘Every 1 days’

WHEN CG.Schedule like ‘%000100010’ THEN ‘Every 2 days’

WHEN CG.Schedule like ‘%000100028’ THEN ‘Every 5 days’

WHEN CG.Schedule like ‘%000100038’ THEN ‘Every 7 Days’

WHEN CG.Schedule like ‘%000192000’ THEN ‘1 week’

WHEN CG.Schedule like ‘%000080000’ THEN ‘Update Once’

WHEN CG.SChedule = THEN ‘Manual’

END AS [Update Schedule],

Case VC.RefreshType

when 1 then ‘Manual’

when 2 then ‘Scheduled’

when 4 then ‘Incremental’

when 6 then ‘Scheduled and Incremental’

else ‘Unknown’

end as RefreshType,

VC.MemberCount

from

dbo.collections_g CG

left join v_collections VC on VC.SiteID = CG.SiteID

–Where CG.CollectionName like ‘%minutes’

order by

CG.Schedule DESC

The results of the query:

 

SCCM Collection

NOTE: I did not put a case when statement for every possible outcome, only the ones that exist in my customers environment. If you want to look up what schedule is used for a specific SCCM Collection then modify the where statement to site that specific SCCM Collection name.

Here is the SSRS report: https://sccmf12twice.com/portfolio-item/troubleshooting-collection-evaluation/

I plan on in the near future releasing a revised version where you can see update times over the last 7 days included.

 

SCCM Collection