Splunk Search

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

supersnedz
Explorer

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.

0 Karma
1 Solution

richgalloway
SplunkTrust
SplunkTrust

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
---
If this reply helps you, Karma would be appreciated.

View solution in original post

abhijeet01
Path Finder

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.

supersnedz
Explorer

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

0 Karma

to4kawa
Ultra Champion

please use code sample

0 Karma

to4kawa
Ultra Champion
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.

richgalloway
SplunkTrust
SplunkTrust

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
---
If this reply helps you, Karma would be appreciated.

supersnedz
Explorer

Thank you this works wonders

0 Karma

gcusello
Esteemed Legend

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

Get Updates on the Splunk Community!

Tips & Tricks When Using Ingest Actions

Tune in to learn about:Large scale architecture when using Ingest ActionsRegEx performance considerations ...

Announcing Our Splunk MVPs

We are excited to announce the first cohort of the Splunk MVP program. Splunk MVPs are passionate members of ...

Dashboard Studio Challenge - Learn New Tricks, Showcase Your Skills, and Win Prizes!

Reimagine what you can do with your dashboards. Dashboard Studio is Splunk’s newest dashboard builder to ...