topic Re: Merge Values from Two Fields into a New Field in Splunk Search

Merge Values from Two Fields into a New Field

jodros — Mon, 28 Sep 2020 15:11:37 GMT

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

Re: Merge Values from Two Fields into a New Field

kristian_kolb — Mon, 04 Nov 2013 15:23:22 GMT

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

Re: Merge Values from Two Fields into a New Field

rakesh_498115 — Mon, 28 Sep 2020 15:11:40 GMT

Hi Jodros,

try this

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

Re: Merge Values from Two Fields into a New Field

jodros — Mon, 28 Sep 2020 15:11:46 GMT

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

Re: Merge Values from Two Fields into a New Field

jodros — Mon, 28 Sep 2020 15:11:54 GMT

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?

Re: Merge Values from Two Fields into a New Field

jodros — Mon, 04 Nov 2013 19:15:40 GMT

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

Re: Merge Values from Two Fields into a New Field

jpass — Mon, 04 Nov 2013 19:50:31 GMT

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+)

Re: Merge Values from Two Fields into a New Field

jodros — Mon, 28 Sep 2020 15:12:02 GMT

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.

Re: Merge Values from Two Fields into a New Field

jodros — Mon, 28 Sep 2020 15:12:05 GMT

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.

Re: Merge Values from Two Fields into a New Field

jodros — Mon, 04 Nov 2013 22:16:05 GMT

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.

Re: Merge Values from Two Fields into a New Field

yong_ly — Mon, 04 Nov 2013 23:53:44 GMT

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

Re: Merge Values from Two Fields into a New Field

jodros — Tue, 05 Nov 2013 14:05:13 GMT

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

Re: Merge Values from Two Fields into a New Field

jodros — Tue, 05 Nov 2013 14:34:25 GMT

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.

Re: Merge Values from Two Fields into a New Field

jodros — Tue, 05 Nov 2013 14:41:13 GMT

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

Re: Merge Values from Two Fields into a New Field

yong_ly — Mon, 28 Sep 2020 15:12:40 GMT

Hi Jodros,

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 ...

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.

Re: Merge Values from Two Fields into a New Field

jodros — Tue, 05 Nov 2013 22:44:14 GMT

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.

Re: Merge Values from Two Fields into a New Field

yong_ly — Tue, 05 Nov 2013 23:14:02 GMT

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.

Re: Merge Values from Two Fields into a New Field

jodros — Wed, 06 Nov 2013 17:47:19 GMT

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.

Re: Merge Values from Two Fields into a New Field

Runals — Wed, 13 Nov 2013 12:25:52 GMT

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

Re: Merge Values from Two Fields into a New Field

jodros — Wed, 13 Nov 2013 13:35:02 GMT

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.