A few years back I wrote a version of RpmVerCmp() in C#. It has been surprisingly useful in unexpected ways. Implementing RPM Compare in C#
The initial purpose of my implementation was to use the method as a SQL Server CLR function. At that time, it allowed me to compare a specific Linux RPM (a software package version) against what is installed on a given asset. Within a database like SQL Server, it allows me to compare package versions installed on many thousands of assets in the blink of an eye.
Combine this feature with an up-to-date list of installed software across the enterprise, and you have a way to measure assets against configuration standards. Configuration and release management requires the ability to measure state. RpmVerCmp() made this measurement much easier, more powerful, faster, and accurate.
Over time I found RpmVerCmp() has other uses. For example, it works with non-Linux software versions, like Chrome and Edge browser release versions. Note, for example, the dotted notation for Edge, “109.0.1518.61” in this image.
Does that structure look familiar? Maybe like an IPv4 address (What is IP Address, Versions and Format of IP Address? – WebNots)?
It was a short jump to test RpmVerCmp() against IPv4 addresses. It works well. But testing if one string value against another is not difficult. Is “192.168.1.1” equivalent to “192.168.0.1”? No. Done.
Where RPM Compare shines is in the range check. Is “192.168.1.1” (X below) in the range between “192.168.0.1” (A below) and “192.168.2.100” (B below)? Yes. But why?
RpmVerCmp(“192.168.1.1”, “192.168.0.1”) returns 1, X > A.
RpmVerCmp(“192.168.1.1”, “192.168.2.100”) returns -1, X < B.
RPM Compare logic would show X as in range when RpmVerCmp(X,A) >= 0 and RpmVerCmp(X,B) <= 0.
I wrote a method, RpmInRange (X, A, B) which uses the logic above and returns 0 (zero) when X is between A and B inclusive.
This is useful as a scalar database function. But I wanted more.
I want to have a table of IP ranges, and to know if in a list of IP addresses which are within a list of IP ranges and the range in which appears. Something like the following.
-- A list of predefined IP ranges
CREATE TABLE Official_IP_Range (
Range_Name varchar(30),
Ip_Start varchar(16),
Ip_End varchar(16)
)
-- A list of assets with their IP addresses
CREATE TABLE My_Asset (
Asset_Name varchar(128),
Asset_IP varchar(16)
)
-- For this pseudo-code example:
INSERT INTO Official_IP_Range VALUES (‘Nerva.com’, ‘68.168.208.1’, ‘68.168.208.100’);
INSERT INTO My_Asset VALUES
(‘Nerva.com’, ‘68.168.208.58’),
(‘Bogus.com’, ‘172.0.0.0);
My ideal query will return a dataset like this:
| Asset_Name | Asset_Ip | Ip_start | Ip_End | Range_Name |
|---|---|---|---|---|
| Nerva.com | 68.168.208.58 | 68.168.208.1 | 68.168.208.100 | My_Domains_Ip |
| Bogus.com | 172.0.0.0 | NULL | NULL | NULL |
The RpmInRange() method can be used in a couple of ways.
-- Example 1:
SELECT
ma.Asset_Name
,ma.Asset_IP
,Ip_Start = (SELECT TOP 1 oir.Ip_Start FROM Official_IP_Range AS oir
WHERE dbo.RpmInRange(ma.Asset_IP, oir.Ip_Start, oir.Ip_End) = 0 )
,Ip_End = (SELECT TOP 1 oir.Ip_End FROM Official_IP_Range AS oir
WHERE dbo.RpmInRange(ma.Asset_IP, oir.Ip_Start, oir.Ip_End) = 0 )
,Range_Name = (SELECT TOP 1 oir.Range_Name FROM Official_IP_Range AS oir
WHERE dbo.RpmInRange(ma.Asset_IP, oir.Ip_Start, oir.Ip_End) = 0 )
FROM
My_Asset AS ma
;
-- Example 2:
WITH cte_assets AS
(
SELECT DISTINCT
ma.Asset_Name
,ma.Asset_IP
FROM
My_Asset AS ma
)
SELECT DISTINCT
ca.Asset_Name
,ca.Asset_IP
,oir.Ip_Start
,oir.Ip_End
,oir.Range_Name
FROM
cte_assets as ca
left outer join Official_IP_Range AS oir
ON (dbo.RpmInRange(ca.Asset_IP, oir.Ip_Start, oir.Ip_End) = 0)
;I much prefer Example 2. Specifically, because the Common Table Expression (CTE) query method allows access to any of the attributes in the Official_IP_Range table in the resulting dataset as a single table lookup. Also, if an Asset_IP falls into multiple distinct ranges, each range will be returned. Example 1 does not allow for multiple range matching.
I have tested the CTE format with about 30,000 unique assets on a list of about 100 distinct IP ranges with surprisingly fast results.
I will post the user defined functions for RpmVerCmp() and RpmInRange() another time.