Friday, March 10, 2017

1E NomadBranch Collections

I'm working on updating our 1E NomadBranch install to the latest version and thought having some collections for it would be useful. We already have many for ConfigMgr itself.


So for Nomad Activity I set up these collections as I thought they would be useful for the upgrade but also ongoing.


To start I have a parent collection called 'All Systems with 1E NomadBranch' I used this for the query.

 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_ADD_REMOVE_PROGRAMS on SMS_G_System_ADD_REMOVE_PROGRAMS.ResourceID = SMS_R_System.ResourceId inner join SMS_G_System_ADD_REMOVE_PROGRAMS_64 on SMS_G_System_ADD_REMOVE_PROGRAMS_64.ResourceID = SMS_R_System.ResourceId where SMS_G_System_ADD_REMOVE_PROGRAMS.DisplayName like "1E Nomad%" OR SMS_G_System_ADD_REMOVE_PROGRAMS_64.DisplayName like "1E Nomad%"  

I ended up using the % wildcard as version 6 is called 'NomadBranch' and version 5 is called 'Nomad Branch' (has space). This was easier then doing separate queries or other ways. Additionally Nomad has both 32-Bit and 64-Bit so we pull from SMS_G_System_ADD_REMOVE_PROGRAMS_64.DisplayName and SMS_G_System_ADD_REMOVE_PROGRAMS.DisplayName as well.

Now we break down the major versions. I'm a fan of parent/child collections so these are limited to the parent collection 'All Systems with 1E NomadBranch' above. We obtained NomadBranch at version 5.2 so I didn't do anything earlier then that. For these I ended up just looking for the version of NomadBranch.EXE.

All Systems with 1E NomadBranch 6

 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_SoftwareFile on SMS_G_System_SoftwareFile.ResourceID = SMS_R_System.ResourceId where SMS_G_System_SoftwareFile.FileName = "NomadBranch.exe" and SMS_G_System_SoftwareFile.FileVersion like "6.%"  


All Systems with 1E NomadBranch 5
 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_SoftwareFile on SMS_G_System_SoftwareFile.ResourceID = SMS_R_System.ResourceId where SMS_G_System_SoftwareFile.FileName = "NomadBranch.exe" and SMS_G_System_SoftwareFile.FileVersion like "5.%"  

I also wanted sub versions so I created those also. I didn't know I had any version 5 so they are getting updated forcefully. With that said, I didn't care to expand those out and focus on 6 since its the current major version. So we look for version 6.0 and 6.1. These are limited to the main version 6.

All Systems with 1E NomadBranch 6.0
 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_SoftwareFile on SMS_G_System_SoftwareFile.ResourceID = SMS_R_System.ResourceId where SMS_G_System_SoftwareFile.FileName = "NomadBranch.exe" and SMS_G_System_SoftwareFile.FileVersion like "6.0%"  

All Systems with 1E NomadBranch 6.1
 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_SoftwareFile on SMS_G_System_SoftwareFile.ResourceID = SMS_R_System.ResourceId where SMS_G_System_SoftwareFile.FileName = "NomadBranch.exe" and SMS_G_System_SoftwareFile.FileVersion like "6.1%"  

For all the above versions, they are the same query with the version number changed so copying makes it quicker. A like is used since version is a string vs using a full match as Nomad uses X.X.X.X version structure and we have many sub versions around.

Finally, I wanted to know who was NOT on the latest patch level. At the time of this post it is 6.1.100.181. So we use the same query but use a ≠ (not equals) instead of a like for the version. As new patches come out we just modify this query so we can target any upgrades needed.

All Systems with 1E NomadBranch 6.1 NOT Current Patch
 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_SoftwareFile on SMS_G_System_SoftwareFile.ResourceID = SMS_R_System.ResourceId where SMS_G_System_SoftwareFile.FileName = "NomadBranch.exe" and SMS_G_System_SoftwareFile.FileVersion != "6.1.100.181"  

As a bonus I also wanted to know who had the 1E ConfigMgr Extensions installed. This is limited to a collection of who has the SCCM Console installed.

All Systems with 1E NomadBranch Admin Extensions
 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_ADD_REMOVE_PROGRAMS on SMS_G_System_ADD_REMOVE_PROGRAMS.ResourceID = SMS_R_System.ResourceId where SMS_G_System_ADD_REMOVE_PROGRAMS.DisplayName = "1E Nomad Branch Admin Extensions 2012"  


And who needs to be updated

All Systems with 1E NomadBranch Admin Extensions less then 6.1
 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_ADD_REMOVE_PROGRAMS on SMS_G_System_ADD_REMOVE_PROGRAMS.ResourceID = SMS_R_System.ResourceId where SMS_G_System_ADD_REMOVE_PROGRAMS.DisplayName = "1E Nomad Branch Admin Extensions 2012"  


-Kevin

No comments:

Post a Comment