How to sort value in a multivalue field of IP address

[mnj1809]

Hello, 

I know that  mvsort command sort values lexicographically.

But I want the output as below:

62.0.3.75
63.0.3.84
75.0.3.80
92.0.4.159
119.0.6.159

@ITWhisperer 

[soniya-01]

Sorting values in a multi-value field of IP addresses involves arranging the IPs in a specific order. Here's a simple way to do it:

1. Separate IP Addresses:

   • If your multi-value field contains multiple IPs in a single string, separate them into individual values.

2. Convert IPs to Numeric Format:

   • Convert each IP address to its numeric equivalent. You can do this by treating each part of the IP as a number and combining them.

3. Sort Numeric Values:

   • Sort the numeric representations of the IPs in ascending or descending order, depending on your preference.

4. Convert Back to IP Format:

   • Once sorted, convert the numeric values back to IP address format.

Example: Suppose you have IP addresses like "192.168.1.2," "10.0.0.1," and "172.16.0.5."

1. Separate: ["192.168.1.2", "10.0.0.1", "172.16.0.5"]
2. Convert: [3232235778, 167772161, 2886729733]
3. Sort: [167772161, 2886729733, 3232235778]
4. Convert Back: ["10.0.0.1", "172.16.0.5", "192.168.1.2"]

You can use programming languages like Python or JavaScript for this task. Always consider the specific requirements of your project and the tools available for your development environment.

[mnj1809]

Thanks Yuanliu for your quick reply. Shared solution works for me. But now I am getting error 'list' command: Limit of '100' for values reached. Additional values may have been truncated or ignored. Could you please suggest to resolve this issue without updating the limits.conf file?

@yuanliu 

[yuanliu]

If presenting result in multivalue format is that important, you can pad decimal octet IPv4 address to full three digits, then use mvsort.

| makeresults
| eval ip = split("119.0.6.159,62.0.3.75,63.0.3.84,75.0.3.80,92.0.4.159", ",")
``` data emulation above ```
| eval idx = mvrange(0,4)
| foreach ip mode=multivalue
    [eval sorted_ip = mvappend(sorted_ip, mvjoin(mvmap(idx, printf("%.3d", tonumber(mvindex(split(<<ITEM>>, "."), idx)))), "."))]
| eval sorted_ip = mvsort(sorted_ip)
| table ip sorted_ip

You'll get

ip	sorted_ip
119.0.6.159 62.0.3.75 63.0.3.84 75.0.3.80 92.0.4.159	062.000.003.075 063.000.003.084 075.000.003.080 092.000.004.159 119.000.006.159

If you want the decimal octets to be stripped of padding, you can do that with printf or any number of other methods.  I'll leave this for your homework.

[tscroggins]

At the risk of spoiling @mnj1809's homework, I would condense this to a single eval statement:

| eval ip=mvmap(mvsort(mvmap(ip, mvjoin(mvmap(split(ip, "."), substr("00".ip, -3)), "."))), mvjoin(mvmap(split(ip, "."), coalesce(nullif(ltrim(ip, "0"), ""), "0")), "."))

 However, understanding nested mvmap magic is also homework.

[mnj1809]

@tscroggins @yuanliu Yes it's really complex to understand below SPL code due to nested commands. could you please brief how is the below code is working?

| makeresults
| eval ip = split("119.0.6.159,62.0.3.75,63.0.3.84,75.0.3.80,92.0.4.159", ",")
| eval idx = mvrange(0,4)
| foreach ip mode=multivalue
[eval sorted_ip = mvappend(sorted_ip, mvjoin(mvmap(idx, printf("%.3d", tonumber(mvindex(split(<<ITEM>>, "."), idx)))), "."))]
| eval sorted_ip=mvmap(mvsort(mvmap(sorted_ip, mvjoin(mvmap(split(sorted_ip, "."), substr("00".sorted_ip, -3)), "."))), mvjoin(mvmap(split(sorted_ip, "."), coalesce(nullif(ltrim(sorted_ip, "0"), ""), "0")), "."))

[yuanliu]

That's a fair question.  So, first let me break the search to a less nested form, then describe the intentions/semantics. (As always, consult syntax and usage in Search Reference.)

| makeresults
| eval ip = split("119.0.6.159,62.0.3.75,63.0.3.84,75.0.3.80,92.0.4.159", ",")
``` data emulation above ```
| eval idx = mvrange(0,4)
| foreach ip mode=multivalue
[| eval interim1 = split(<<ITEM>>, "."),
  interim2 = mvmap(idx, printf("%.3d", tonumber(mvindex(interim1, idx)))),
  interim3 = mvjoin(interim2, "."),
  sorted_ip_padded = mvappend(sorted_ip_padded, interim3)]
| eval sorted_ip_padded = mvsort(sorted_ip_padded)
| foreach sorted_ip_padded mode=multivalue
[| eval interim4 = split(<<ITEM>>, "."),
  interim5 = mvmap(idx, printf("%d", tonumber(mvindex(interim4, idx)))),
  interim6 = mvjoin(interim5, "."),
  sorted_ip = mvappend(sorted_ip, interim6)]

(I'm using a different approach to fill your homework from @tscroggins's proposal because foreach is easier to break down.  I also use a more symmetric approach so it can be explained more readily.)

Here is a key card behind the formula:

split	An IPv4 address will result will contain a 4-element array, indexed from 0 to 3.
mvrange	Generate a 4-element array with values from 0 to 3.
mvsort	This is lexicographic sort of array elements.  For 0-padded IPv4 addresses, it is equivalent to numeric sort that you desired.
printf	Pad and unpad

To examine the formula, first take a look at output below:

idx	interim1	interim2	interim3	interim4	interim5	interim6	ip	sorted_ip	sorted_ip_padded
0 1 2 3	92 0 4 159	092 000 004 159	092.000.004.159	119 000 006 159	119 0 6 159	119.0.6.159	119.0.6.159 62.0.3.75 63.0.3.84 75.0.3.80 92.0.4.159	62.0.3.75 63.0.3.84 75.0.3.80 92.0.4.159 119.0.6.159	062.000.003.075 063.000.003.084 075.000.003.080 092.000.004.159 119.000.006.159

Inside the foreach loop, the following steps are followed:

1. Breakdown IPv4 into octets. (interim1, interim4)
2. Pad/unpad each octet. (interim2, interim5)
3. Reassemble IPv4 with/without padding. (interim3, interim6)
4. Assemble padded/unpadded IPv4 into array. (sorted_ip_padded, sorted_ip)

Now, the only nested element is padding/unpadding: mvmap(idx, printf("%.3d", tonumber(mvindex(interim1, idx))))/mvmap(idx, printf("%d", tonumber(mvindex(interim4, idx)))).  Because of the way tonumber works in SPL, it cannot be broken down further.  But the principle is not too complicated.  idx is the index of octet.  So, it is an iteration of printf("%d", octet) over each octet, where octet = mvindex(interim4, idx) wrapped in tonumber().

Hope this helps.

[mnj1809]

Thanks for the explanation @yuanliu 

[mnj1809]

Thank you so much @tscroggins. This helped me.

[yuanliu · Accepted solution]

Using split in your emulation kind of obscures characteristics of the real data, how real data becomes multivalued, etc.  But working from this - please post test code/mock code in text, not screenshot, there is a potential way to leverage sort command that automatically recognizes IPv4 format.

| makeresults
| eval ip = split("119.0.6.159,62.0.3.75,63.0.3.84,75.0.3.80,92.0.4.159", ",")
| mvexpand ip
| sort ip
| stats values(ip) as ip list(ip) as sorted_ip

This gives you

ip	sorted_ip
119.0.6.159 62.0.3.75 63.0.3.84 75.0.3.80 92.0.4.159	62.0.3.75 63.0.3.84 75.0.3.80 92.0.4.159 119.0.6.159

mvexpand can be expensive.  list can be memory hungry.  So, think of data strategy if performance is a concern.  Do you have to present results in multivalue format?