How to – IPU Collections

, ,

In this blogpost we will cover some of the logic for how we are handling the IPU. In order to create the “Compat Scan Passed” collection you are not able to use the “Drop down” actions to create this so you might not know how to do it. We are first going to assume that you have already extended client settings to absorb Nomad Cache job.

 

Compat Scan Passed Collection:

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_OPERATING_SYSTEM on SMS_G_System_OPERATING_SYSTEM.ResourceID = SMS_R_System.ResourceId
inner join SMS_ClassicDeploymentAssetDetails on SMS_ClassicDeploymentAssetDetails.DeviceID =
SMS_R_System.ResourceId    where SMS_G_System_OPERATING_SYSTEM.Version = “10.0.10586”
and SMS_G_System_OPERATING_SYSTEM.OSLanguage = 1033    and SMS_ClassicDeploymentAssetDetails.PackageID = ‘XXXXXXXX’
and SMS_ClassicDeploymentAssetDetails.StatusDescription = “-1047526896”

NOTES: This collection will produce a syntax error but trust me it works! My where statements basically OS = Win10 v1511, the language is English (1033), fill in your own PackgeID, and the exit code is compat scan pass!. You can use this format in your environment if needed.

 

Nomad Cached Content Collection:

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_COMPUTER_SYSTEM on SMS_G_System_COMPUTER_SYSTEM.ResourceID = SMS_R_System.ResourceId
where SMS_G_System_COMPUTER_SYSTEM.Model = “Latitude 5289”
and SMS_R_System.ResourceId in (select SMS_R_System.ResourceId from  SMS_R_System inner join SMS_G_System_1E_NomadPackages_1_0 on SMS_G_System_1E_NomadPackages_1_0.ResourceId = SMS_R_System.ResourceId where SMS_G_System_1E_NomadPackages_1_0.PackageID = “XXXXXXXX” and SMS_G_System_1E_NomadPackages_1_0.OptInfo = “Completed”)
and SMS_R_System.ResourceId in (select SMS_R_System.ResourceId from  SMS_R_System inner join SMS_G_System_1E_NomadPackages_1_0 on SMS_G_System_1E_NomadPackages_1_0.ResourceId = SMS_R_System.ResourceId where SMS_G_System_1E_NomadPackages_1_0.PackageID = “XXXXXXXX” and SMS_G_System_1E_NomadPackages_1_0.OptInfo = “Completed”)

NOTE: Also for this collection add any item that you have pre-cached by modifying the where statement. In my example I have a specified model + only 2 packages that are pre-cached. In my production collections I have 19 packages I need pre-cached so it’s just easy to show you a few items.

 

SQL Track Individual System For Required Cached Content For IPU:

SELECT DISTINCT

sys.Name0 as ‘Computer Name’

,CS.Model0

,sys.User_Name0 as ‘Last User’

,U.mail0 as ‘Email’

,CS.Manufacturer0 as ‘Manufacturer’

,CS.Model0 as ‘Model’

,OS.Caption0 as ‘Operating System’

,OS.BuildNumber0

, case

OS.BuildNumber0

WHEN ‘10586’ THEN ‘Win10 v1511’

WHEN ‘14393’ THEN ‘Win10 v1607’

WHEN ‘15063’ THEN ‘Win10 v1703’

END AS [OS Version]

,PR.AddressWidth0 as ‘OS Architecture’

,WKS.LastHWScan

,nm.AlreadyCached00

,nm.BytesFromDP00

,nm.BytesFromPeer00

,nm.OptInfo00

,pkg.Name

,nm.PackageID00

,nm.Percent00

FROM v_R_System sys

Full Outer join v_FullCollectionMembership FM ON FM.ResourceID = sys.ResourceID

Full Outer join v_GS_PC_BIOS BIOS ON BIOS.ResourceID = sys.ResourceID

Full Outer join v_GS_COMPUTER_SYSTEM CS ON CS.ResourceID = sys.ResourceID

Full Outer join v_GS_WORKSTATION_STATUS WKS ON WKS.ResourceID = sys.ResourceID

Full Outer join v_GS_OPERATING_SYSTEM OS on OS.ResourceID = sys.ResourceID

Full Outer join v_GS_PROCESSOR PR on PR.ResourceID = sys.ResourceID

Full Outer JOIN v_R_User u on u.User_Name0 = sys.User_Name0

Full Outer join v_GS_SYSTEM_ENCLOSURE se on se.ResourceID=sys.ResourceID

Full Outer join _E_NomadPackages_DATA nm on nm.MachineID=sys.ResourceID

Full Outer join v_Package PKG on pkg.PackageID = nm.PackageID00

WHERE sys.name0 = ‘HOSTNAME’

and nm.packageID00 in (‘XXX00B8A’,‘XXX00B81’,‘XXX00AB3’,‘XXX00AB2’,‘XXX00AB6’,‘XXX00AD7’)

 

NOTE: Make sure to modify your where statement with all the possible packages a system in your IPU collection will need. For my production environment once A system registers saying it has 19 packages then I know It is ready to be upgraded. I also reccoment adding a little section where it says what each package iD is I have that in my query but is not posted here.

 

note: If WordPress reformats the queries let me know so I can work on getting that fixed! I may update this blogpost to include more screenshots if anybody has any problems.

Collection Schedule Query

, , , , ,

Collection Schedule Queries.

There is no real readable output of the 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 collection evaluation problems. Also I will do another blogpost with the sql queries to identify a number of other items to include orphaned collections, 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

 

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:

 

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 collection then modify the where statement to site that specific 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.

 

 

SSRS Client Installation Troubleshooting

, , , , , , ,

This will be the first part of many in a series of reports that I will offer for free to the community. The reports will cover wide range of items from OS Migration dashboards, Bit locker reports, SCCM Infrastructure health, Client health, Collection evaluation, etc. In each report I intent on there to be plenty of visuals to provide the tech a clean overview of what is happening in the environment. There will also be several troubleshooting notes included in this report to be a 1 stop shop for troubleshooting tool. Some reports during this series will have troubleshooting guides available as well. So far I am intending to release troubleshooting guides for OSD, client install, and software updates as I have already written several of them for previous customers.

The first report to be released will have a focus on identifying problems with client installation and providing some good.  This will eventually grow into a “Client Health Assessment” where there will be multiple reports all focused on client install/health/active etc.

Below we are able to get an overview of the client install failures within the last 30 days, and keep an eye on how many clients we currently have in the environment.

I’m leveraging several CASE WHEN statements to translate the error codes to something the tech can easily troubleshoot.

Please not that if your total systems number is off from what you see in your SCCM Console then look into your maintenance tasks/discovery items. It is not uncommon for me to see customers environments that do not have AD cleaned up…so long story short make sure your AD environment is cleaned up too.

Future Revision of this report will be released as part of client health Dashboard, with a few other reports.

Link to Report:
https://gallery.technet.microsoft.com/SSRS-SCCM-Client-Install-3bd9e6e6