Splunk Search

How to sort value in a multivalue field of IP address

mnj1809
Path Finder

Hello, 

I know that  mvsort command sort values lexicographically.

mnj1809_0-1702049305752.png

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 

Labels (3)
0 Karma
1 Solution

yuanliu
SplunkTrust
SplunkTrust

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?

View solution in original post

0 Karma

soniya-01
Loves-to-Learn

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.

0 Karma

mnj1809
Path Finder

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 

0 Karma

yuanliu
SplunkTrust
SplunkTrust

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.

Tags (1)
0 Karma

tscroggins
Influencer

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.

0 Karma

mnj1809
Path Finder

@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")), "."))

 

0 Karma

yuanliu
SplunkTrust
SplunkTrust

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:

splitAn IPv4 address will result will contain a 4-element array, indexed from 0 to 3.
mvrangeGenerate a 4-element array with values from 0 to 3.
mvsortThis is lexicographic sort of array elements.  For 0-padded IPv4 addresses, it is equivalent to numeric sort that you desired.
printfPad 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.

0 Karma

mnj1809
Path Finder

Thanks for the explanation @yuanliu 

0 Karma

mnj1809
Path Finder

Thank you so much @tscroggins. This helped me.

0 Karma

yuanliu
SplunkTrust
SplunkTrust

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?

0 Karma
Get Updates on the Splunk Community!

How to Monitor Google Kubernetes Engine (GKE)

We’ve looked at how to integrate Kubernetes environments with Splunk Observability Cloud, but what about ...

Index This | How can you make 45 using only 4?

October 2024 Edition Hayyy Splunk Education Enthusiasts and the Eternally Curious!  We’re back with this ...

Splunk Education Goes to Washington | Splunk GovSummit 2024

If you’re in the Washington, D.C. area, this is your opportunity to take your career and Splunk skills to the ...