How to – IPU Collections

, ,

IPU Collections

In this blogpost we will cover some of the logic for how we are handling the IPU Collections. 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”

IPU Collections

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

ALSO CHECK: How To – In Place Upgrade

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