Splunk Search

Iist IPs from different columns

splunkrocks2014
Communicator

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.

Tags (1)
0 Karma
1 Solution

DalJeanis
SplunkTrust
SplunkTrust

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

View solution in original post

DalJeanis
SplunkTrust
SplunkTrust

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

splunkrocks2014
Communicator

Thanks. This is I am looking for.

Get Updates on the Splunk Community!

What's new in Splunk Cloud Platform 9.1.2312?

Hi Splunky people! We are excited to share the newest updates in Splunk Cloud Platform 9.1.2312! Analysts can ...

What’s New in Splunk Security Essentials 3.8.0?

Splunk Security Essentials (SSE) is an app that can amplify the power of your existing Splunk Cloud Platform, ...

Let’s Get You Certified – Vegas-Style at .conf24

Are you ready to level up your Splunk game? Then, let’s get you certified live at .conf24 – our annual user ...