topic Re: merge two sourcetypes that have the same data but different field names in Splunk Search

merge two sourcetypes that have the same data but different field names

supersnedz — Wed, 30 Sep 2020 03:51:25 GMT

Hello,

I have two sourcetypes in the same index, however the fields names are different. Is it possible to rename both fields to the same and then search for a value in the newly named field?

Both my sourcetypes and fields are as follows:

index=siem_cyber_ca sourcetype=cs:epv:cef externalid="\\"0538ef14-4281-11ea-a80f-005056af449f\\""
index=siem_cyber_ca sourcetype=cs:pta:cef cs1="0538ef14-4281-11ea-a80f-005056af449f"

I would like both externalid and cs1 to be called the same name, so i can search for 0538ef14-4281-11ea-a80f-005056af449f and recieve both set of results.

Re: merge two sourcetypes that have the same data but different field names

gcusello — Wed, 29 Jan 2020 15:23:28 GMT

Hi @supersnedz,
you can follow two ways:

create for one of the sourcetypes an alias for the field (e.g. cs1 AS externalid) in [Settings -- Fields -- Field Aliases];
use eval and coalesce in your searches e.g. index=siem_cyber_ca | eval externalid=coalesce(externalid,cs1).

There's also a third choice because I see that the field value in externalid is a little bit different than cs1: extract from externalid the value using rex: | rex field=externalid "\"\\\\\\\"(?<externalid>[^\!]*)".

Ciao.
Giuseppe

Re: merge two sourcetypes that have the same data but different field names

richgalloway — Wed, 29 Jan 2020 15:24:32 GMT

If I understand the question, there are a few ways to do that. The first is with OR

index=siem_cyber_ca (sourcetype=cs:epv:cef OR sourcetype=cs:pta:cef) (externalid=foo OR cs1=foo)

Or you could give both fields a common name and then search them.

index=siem_cyber_ca (sourcetype=cs:epv:cef OR sourcetype=cs:pta:cef) (externalid=* OR cs1=*)
| eval searchField = coalesce(externalid, cs1)
| search searchField = foo

Like you suggested, you could also rename both fields.

index=siem_cyber_ca (sourcetype=cs:epv:cef OR sourcetype=cs:pta:cef) (externalid=* OR cs1=*)
| rename externalid as searchField, cs1 as searchField
| search searchField = foo

Re: merge two sourcetypes that have the same data but different field names

to4kawa — Thu, 30 Jan 2020 06:20:18 GMT

Fields » Calculated fields » Add new

Destination app: search   
Apply to sourcetype  named: cs:epv:cef
Name: cs1
Eval expression:  coalesce(replace(externalid,"\\\\\"",""),cs1)

Hi, try Calculated fields and use fieldname cs1

|makeresults
|eval externalid="\\\"0538ef14-4281-11ea-a80f-005056af449f\\\""
|eval cs1=coalesce(replace(externalid,"\\\\\"",""),cs1)

the result is like above.

Re: merge two sourcetypes that have the same data but different field names

abhijeet01 — Wed, 30 Sep 2020 03:58:00 GMT

Hi supersnedz,

Understood.

You can try below command .

index=siem_cyber_ca (sourcetype= "cs:epv:cef" OR sourcetype="cs:pta:cef") | rex field=_raw ((externalid=)\"\\\"|(cs1=)\")(?P[a-z0-9-]*) | stats count(xyz) as XYZ by XYZ .

You don't need to rename field . The regex command will create new/rename field.

Thanks,
Abhijeet B.

Re: merge two sourcetypes that have the same data but different field names

to4kawa — Thu, 30 Jan 2020 08:54:52 GMT

please use code sample

Re: merge two sourcetypes that have the same data but different field names

supersnedz — Thu, 30 Jan 2020 10:57:53 GMT

Thank you this works wonders

Re: merge two sourcetypes that have the same data but different field names

supersnedz — Thu, 30 Jan 2020 10:58:50 GMT

Thank you, at first i had unbalanced quotes, but its sorted. Thanks again