Merge Values from Two Fields into a New Field

[jodros]

I have two fields, src_ip and dest_ip. These two fields show up in the same log. I am trying to merge all values of these two fields into a new generic field, ip. I have tried using rename, eval with coalesce(), rex, as well as field aliases. In all cases, only one of the fields (either src_ip or dest_ip) shows as ip.

Summary:
log 12:00:00 src_ip src_port dest_ip dest_port action

src_ip = 1.1.1.1
dest_ip = 2.2.2.2
New ip field = 1.1.1.1 and 2.2.2.2

Any suggestions would be appreciated.

Thanks

[Runals]

Why not create a field alias for src_ip to ip and dest_ip to ip. It's what I've done.

props.conf

[sourcetype1]  
FIELDALIAS_st1_src_to_ip = src_ip AS ip  
FIELDALIAS_st1_dest_to_ip = dest_tip AS ip  

[jodros]

Because it will only take the last FIELDALIAS statement it reads. So actually in your example, the ip field will only contain the values from the dest_ip field.

[yong_ly · Accepted solution]

it sounds like what you want is a multi-value field rather than combining the two (unless you want to have unique combinations of (dest/src). You can do this via an inline extraction or configuring it via props.conf.

Try this search. It will look for all IP addresses and match up to two values in one event:

[search term] | rex field=_raw "(?P<ip_addr>\d+.\d+.\d+.\d+)" max_match=2 | stats count by ip_addr

Similiarly you can set up the regular expression in props.conf/transforms.conf

props.conf

[yoursourcetype]
REPORT-ip_addr = extract_ipaddr

transforms.conf

[extract_ipaddr]
REGEX = (?P<ip_addr>\d+.\d+.\d+.\d+)
MV_ADD = true

[jodros]

Hey yong.ly, sorry about the last comment 18 hours ago. Apparently my session wasn't fresh and I did not see your comments. Yes, using the mvexpand is exactly what I need. I really appreciate the assistance.

[yong_ly]

did using the mvexpand command not work for either of those things?

I think you're mistaken about timechart and MV fields. I just tested it and it works fine. Can you show me your full query? I don't think searches appended AFTER a timechart are valid.. so you need to filter BEFORE the timechart as I mentioned above.

[jodros]

So at this point, I need to know why I can run a stats count with a multi value field, but not a timechart count. Also, the iplocation is a custom python script and I guess I need to know how to make it work with a multi value field.

I also need to decide if this is worth it, to extract both IP's (src and dest) from logs and display them by unique IP count on a map.

[yong_ly]

Hi Jodros,

1. regarding the first point, you would probably want to run the "|search NOT ip=***" BEFORE the stats or timechart command.. for example:

... | search NOT ip=**** | timechart ...

1. I haven't used iplocation, but if it doesn't work on MV fields, then you can use mvexpand to separate it out to different events.. e.g:

[search term] | rex field=_raw "(?P\d+.\d+.\d+.\d+)" max_match=2 | mvexpand ip_addr | iplocation ip_addr

What this will do is create two events from the one event that look identical, except that the ip_addr field will have different values.

[jodros]

And apparently I cannot run a "| iplocation" on a multi-value field. Hmmmm.

[jodros]

Ok, so now how can I further filter out my transformed search results. I am able to use a "| search NOT ip=10.0.0.0/8" when I run a stats, but not a timechart. I would like to filter out results from internal IP's and only display foreign IP's.

[jodros]

THANK YOU! This is what I needed. I appreciate it.

[jodros]

I don't know if this is possible for the same sourcetype when I want to evaluate both IP addresses and then assign them to one single field.

[jodros]

Tried separate EXTRACT commands for the sourcetype. Didn't work.

I think I am running into an issue where a field cannot exist more than once per sourcetype possibly. I know that I can create field aliases for fields from differing sourcetypes. For instance, I can define a field alias src = src_ip for sourcetype a, and another field alias sip = src_ip for sourcetype b. The when I run the search src_ip=1.1.1.1, if the value exists across both sourcetypes, it will return data from both.

[jpass]

I combine a bunch of fields into a single field with a field extraction. I created it in Splunk web under "Admin / Fields / Field Extractions".

\s(ip|domain|url|filename|md5|cve|email_addr|cve)=(?<phrase>\S+)

[jodros]

I don't think that this will work. For one the logs don't actually contain the field names, so it doesn't looks like src_ip=1.1.1.1 dest_ip=2.2.2.2 in the log. However if we took this approach anyway, I think this regex would always match the first and would not show results from both for the new field "phrase".

Thanks for the suggestion.

[jodros]

Or if there is a way to sum the output of a two separate stats commands:

stats count by src_ip
1.1.1.1 count 2

stats count by dest_ip
1.1.1.1 count 3

sum of both stats
1.1.1.1 count 5

Thanks

[jodros]

I just tried running stats on the src_ip and dest_ip in separate searches and appending it together. That didn't work either.

Neither did a join. Was looking at foreach command. Wonder if it would do it?

[jodros]

Ok. So maybe that isn't what a want to do. I thought if I were able to merge all values from two fields into one field, than I could show the total count for an unique IP on a map, whether it was the source or destination in a log.

Is there a better way to accomplish this?

Ultimate goal:
log 12:00:00 src_ip=10.10.10.10 src_port=12345 dest_ip=1.1.1.1 dest_port=80 action=allow
log 12:00:01 src_ip=1.1.1.1 src_port=12345 dest_ip=10.10.10.10 dest_port=80 action=block

The stats count for a new field ip should show 1.1.1.1 as having a count of 2.

Let me know if this makes more sense.

Thanks

[rakesh_498115]

Hi Jodros,

try this

|fillnull value="N/A" src_ip,dest_ip | eval NewField = src_ip + "and" + dest_ip

[kristian_kolb]

eval sounds like what you need;

... | eval new_ip_field = src_ip . " and " . dest_ip

the dot (.) is the concatenation operator.

---

UPDATE:

to test the solution suggested by @yong.ly, try this in the search;

...| eval ip = src_ip . "," . dest_ip | makemv delim = "," ip | stats c by ip

/K