Splunk Search

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

markwymer
Path Finder

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.

0 Karma
1 Solution

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

try something like this

View solution in original post

cmerriman
Super Champion
(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
Path Finder

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.

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