topic Re: Iist IPs from different columns in Splunk Search

Iist IPs from different columns

splunkrocks2014 — Tue, 14 Nov 2017 15:29:37 GMT

Is it an easy way to list IP's from different columns into one? For instance,

header     ip1         ip2       ip3
-------   -------    --------   --------
record1   1.1.1.1    2.2.2.2    3.3.3.3
record2    4.4.4.4   5.5.5.5    6.6.6.6

The end result looks like the following:

 header      ip
 ---------- -----------
 record1    1.1.1.1
            2.2.2.2
            3.3.3.3
 record2    4.4.4.4
            5.5.5.5
            6.6.6.6

Here is my search query:

| makeresults 
| eval header="record1", ip1="1.1.1.1", ip2="2.2.2.2", ip3="3.3.3.3" 
| append [| makeresults | eval header="record2", ip1="4.4.4.4", ip2="5.5.5.5", ip3="6.6.6.6"] 
| fields - _time 
| eval ip=ip1+"|" + ip2+"|"+ip3 
| fields - ip1 ip2 ip3 
| makemv delim="|" ip

It works as expected, but it seems cumbersome. Is there a better way to achieve the same result? Thanks.

Re: Iist IPs from different columns

DalJeanis — Tue, 14 Nov 2017 15:55:54 GMT

There are two easy ways.

First, if the event is a record that has been extracted and has a _raw field...

| makeresults 
| eval _raw="header=\"record1\", ip1=\"1.1.1.1\", ip2=\"2.2.2.2\", ip3=\"3.3.3.3\"" 
| append [| makeresults | eval _raw="header=\"record2\", ip1=\"4.4.4.4\", ip2=\"5.5.5.5\", ip3=\"6.6.6.6\""] 
| rex field=_raw "(?<myIP>\d+\.\d+\.\d+\.\d+)(\D|$)" max_match=0

Second, if the above is not the case...

| makeresults 
| eval header="record1", ip1="1.1.1.1", ip2="2.2.2.2", ip3="3.3.3.3" 
| append [| makeresults | eval header="record2", ip1="4.4.4.4", ip2="5.5.5.5", ip3="6.6.6.6"] 
| fields - _time
| streamstats count as recno
| untable recno fieldname fieldvalue
| regex fieldvalue="^\d+\.\d+\.\d+\.\d+$"
| stats list(fieldvalue) as myIP by recno

Re: Iist IPs from different columns

splunkrocks2014 — Tue, 14 Nov 2017 16:31:09 GMT

Thanks. This is I am looking for.