How to merge the values of two fields from two sourcetypes into a new field?

[markwymer]

HI,

Apologies if this is answered elsewhere but I can't find a question that fits my situation although I'm sure that I'm not alone in trying to achieve this.

I am searching two sourcetypes (and indexes) that have (amongst many other fields) two automatically extracted fields that contain IP addresses. One is called c_ip and the other source_ip.

What I am trying to get is a new field called src that merges the values in the existing extracted IP address fields.

For example:-

search index=a sourcetype=source1 | stats count by c_ip

produces:

c_ip   count
1.1.1.1    9
2.2.2.2    5

and

search index=b sourcetype=source2 | stats count by source_ip

produces:

source_ip    count
1.1.1.1       3
3.3.3.3       7

What I would like to get is the above two fields merged into one such that

search index=* (sourcetype=source1 OR sourcetype=source2) | {whatever code is required here to merge the data into src} | stats count src

produces

 src      count
    1.1.1.1          12
    2.2.2.2           5
    3.3.3.3           7

This is obviously very simplified. In reality I have hundreds of thousands of events and thousands of IP addresses, many of which only appear in one or the other extracted fields.

Hopefully my examples will help explain my dilemma,

Many thanks,
Mark.

[cmerriman · Accepted solution]

(index=a sourcetype=source1) OR (index=b sourcetype=source2) |eval IP=coalesce(c_ip,source_ip)| stats count by IP

try something like this

[markwymer]

Hi cmerriman,

Thanks for your response. I did try coalesce and it worked fine with my test data ( 20 ish events ) but my live data (200,000 ish events) produced mixed results.

I have to admit that my SPL search is very long with quite a few field renames, coalesce's, case's, location lookups and a seconday search - perhaps I'm trying to do too much in the wrong way!

Anyway, many thanks for confirming that it is 'coalesce' that I need.
Mark.