In my article from last week, What Is A 4-Part Version Numbers? I showed you how Add Remove Program (ARP) versions are actually a string and not a number, like most people think it is. But how can you query the 3rd octet of a version string, if the math function don’t work? The answer is to use parsename SQL function with the math functions. This article and the associated video will show you how all of this works.
Just as a remind as to what a four-part version number is. Using Google Chrome for my example. The major version is 108, whereas the minor version is 0 and revision number is 5359 and the build is 125. It should be noted that a 4-part version number is also referred to as Octet. For example, in this case 3rd octet is 5359.
As a Microsoft Configuration Manager (MCM / SCCM / ConfigMgr/ MEM) administrator we are always asked for more details from ConfigMgr. One of the tricky points is how to query items like the ARP version string and show all computer with a version less then x. In some cases this is easy and in others it is hard. Using the ParseName trick will allow all versions numbers to be compared.
What is Parsename?
Parsename is intended to parse SQL object details. The details are as such: <server>.<database>.< Schema >.<object {read as SQL view name}>. Using my SQL server as an example, I can query the SQL view memem.cm_mem.dbo.v_r_system, but most people will use dbo.v_r_system or just v_r_system. This is because SQL server allow you to be lazy. Read my article on why this can be a bad thing, What Are The Four-Part Names Of A Sql Server Object?
Anyways to make a long story short, the SQL team create function that allow you to grab each object details. It accepts two values, the object name and a int representing the Octet.
There is a minor trick here, you would think that the first octet was one e.g. server. It is not, it is 4. With Database = 3, schema = 2 and the object =1
For full details of ParseName, see the Online documentation.
ParseName Trick
Because ParseName handles, four-part strings, it also means that it will handle 4-part ARP Version string. When you use parsename function it will return a string which is the octet that you are looking for. This will mean that it will STILL do a string compare. However, if to also combine the Cast function
And it also means that it will handle IP addresses too!
Query the 3rd Octet of ARP Version String
Select
RV.Netbios_Name0,
arp.DisplayName0,
parsename(arp.Version0,2) as '3rd octet',
arp.Version0
from
dbo.v_R_System_Valid RV
join dbo.v_Add_Remove_Programs ARP on RV.ResourceID = ARP.ResourceID
Where
ARP.ProdID0 = 'askgarth'
and Cast(parsename(arp.Version0,2) as int) > '984'
This query will return all computer with the software title of askgarth who’s 3rd octet is greater then 984.
IP Addresses
This is really the same trick as ARP version strings, but I thought it was important to call it out so that you know about it and can use it.
Query the 3rd Octet of IPv4 Address
Select
RV.Netbios_Name0,
NA.Description0,
parsename(NDS.IPAddress0,2) as '3rd octet',
NDS.IPAddress0
from
dbo.v_R_System_Valid RV
join dbo.v_GS_NETWORK_ADAPTER NA on RV.ResourceID = NA.ResourceID
join dbo.v_Network_DATA_Serialized NDS on (RV.ResourceID = NDS.ResourceID) and (NA.DeviceID0 = NDS.Index0)
Where
NDS.IPEnabled0 = 1
and NDS.IPAddress0 not like '%:%'
and cast(parsename(NDS.IPAddress0,2) as int) < 6
Order by
RV.Netbios_Name0
This query will return all computer with a IP address (3rd octet) is less then 6.
What is Partname and 4 Part Version String?
In this video, I will go over the step listed above so that you can see the whole processes in action.
Hopefully this help clear up four-part version strings being believed to be version numbers. Don’t get me wrong I still call them version numbers but I know that they are strings.
Finally, don’t forget that you can subscribe to my RRS feed to stay on top of the latest trips and tricks. Additionally, if you have any questions, please feel free to touch base @Garthmj.