topic Re: combine different fileds from different events in Splunk Search

combine different fileds from different events

mvagionakis — Tue, 29 Sep 2020 16:58:12 GMT

Hello,

I'm trying to combine values from two events and to make a table with them.
Let me explain you.
I have the same index, the same source and the same sourcetype but some fields are named differently.

Below an example:

event1:
SNMPv2-SMI::enterprises."5560.300.9002.1.3.111.112.113.114.0" = "state"
somestate = state

remote_gateway_st = 111.112.113.114
host = titi

index = someindex

linecount = 1

punct = -::."........."=""_

source = snmp://test
sourcetype = sourcetype_toto
splunk_server = host1

splunk_server_group = dmc_group_indexer

timestamp = none

event2:
SNMPv2-SMI::enterprises."5560.300.9002.1.2.217.167.157.241.0" = "a_client"

ClientName = a_client

remote_gateway = 111.112.113.114
host = titi

index = someindex

linecount = 1

punct = -::."........."=""_

source = snmp://test
sourcetype = sourcetype_toto
splunk_server = host1

splunk_server_group = dmc_group_indexer

timestamp = none

My goal is to combine them when remote_gateway_st=remote_gateway and to put in a table the fields remote_gateway_st ,ClientName,somestate.

I tried join function but I couldn't make it work.

Could you give me some help please?

Thank you in advance,
Michail

Re: combine different fileds from different events

DalJeanis — Tue, 28 Nov 2017 17:10:59 GMT

There are lots of ways.

Method 1 - Splunk Stew (This method is generally preferred)

index=someindex host="titi" source="snmp://test" sourcetype="sourcetype_toto"
( ClientName="a_client" OR somestate="state") 
| fields index host source sourcetype remote_gateway* somestate ClientName 
| eval remote_gateway_merged=coalesce(remote_gateway,remote_gateway_st)
| stats values(*) as * by remote_gateway_merged

Method 2 - Join

index=someindex host="titi" source="snmp://test" sourcetype="sourcetype_toto"
 ClientName="a_client"
| fields index host source sourcetype remote_gateway somestate ClientName 
| join remote_gateway [search 
    index=someindex host="titi" source="snmp://test" sourcetype="sourcetype_toto"
    somestate="state"
    | fields remote_gateway_st somestate  
    | rename  remote_gateway_st as remote_gateway
    | table remote_gateway somestate
    ]

Re: combine different fileds from different events

kamlesh_vaghela — Tue, 28 Nov 2017 17:11:52 GMT

Can you please try this?

index=someindex 
| eval remote_gateway_st=coalesce(remote_gateway,remote_gateway_st) 
| stats values(ClientName) as ClientName values(somestate) as somestate by remote_gateway_st

I have tried with your provided data:

| makeresults | eval _raw="SNMPv2-SMI::enterprises. \"5560.300.9002.1.3.111.112.113.114.0 \" =  \"state \"  \n 
somestate = state  \n 
remote_gateway_st = 111.112.113.114 \n 
host = titi  \n 
index = someindex  \n 
linecount = 1  \n 
punct = -::. \"......... \"= \" \"_  \n 
source = snmp://test  \n 
sourcetype = sourcetype_toto  \n 
splunk_server = host1  \n 
splunk_server_group = dmc_group_indexer  \n 
timestamp = none" | kv | append [| makeresults | eval _raw="SNMPv2-SMI::enterprises. \"5560.300.9002.1.2.217.167.157.241.0 \" =  \"a_client \" \n 
 \n 
ClientName = a_client  \n 
remote_gateway = 111.112.113.114  \n 
host = titi  \n 
index = someindex  \n 
linecount = 1  \n 
punct = -::. \"......... \"= \" \"_  \n 
source = snmp://test  \n 
sourcetype = sourcetype_toto  \n 
splunk_server = host1  \n 
splunk_server_group = dmc_group_indexer  \n 
timestamp = none" | kv] | eval remote_gateway_st=coalesce(remote_gateway,remote_gateway_st)  | stats values(ClientName) as ClientName values(somestate) as somestate by remote_gateway_st

Happy Splunking

Re: combine different fileds from different events

somesoni2 — Tue, 28 Nov 2017 19:19:47 GMT

I would go for option 1. Joins are expensive, so unless you have multiple events per emote_gateway values, you can use option 1.

Re: combine different fileds from different events

DalJeanis — Tue, 28 Nov 2017 20:37:45 GMT

@kamlesh_vaghela - Good job. One improvement..

This...

| eval remote_gateway_st=if(isnotnull(remote_gateway),remote_gateway,remote_gateway_st)

...can be written as this ...

| eval remote_gateway_st=coalesce(remote_gateway,remote_gateway_st)

...which makes the code easier to read - especially if you have one more item to coalesce together.

Re: combine different fileds from different events

kamlesh_vaghela — Wed, 29 Nov 2017 04:53:31 GMT

hi @DalKeanis, Yeah readability make sense. Thanks for improvement. 🙂

Re: combine different fileds from different events

mvagionakis — Wed, 29 Nov 2017 08:37:39 GMT

Hello everyone,

only the second method worked but partially.
By adding dedup command on "clientname" and by searching only the events that contains somestate AND clientname, I got the perfect result.

Thank you very much for your help and reactivity 🙂

Have a good day
Michail

Re: combine different fileds from different events

mvagionakis — Wed, 29 Nov 2017 08:40:30 GMT

hello Kamlesh, thanks for replying to my question.
update: it was my mistake as I said for DalJeanis reply...yours works also very well 🙂
I thank you again for your time 🙂

Have a great day.
Michail

Re: combine different fileds from different events

mvagionakis — Wed, 29 Nov 2017 14:00:17 GMT

Hello again,

Option 1 is better, it was my fault, I missed type a field..oups 😞

Thank you again DalJeanis 🙂
Have a great day

Re: combine different fileds from different events

DalJeanis — Wed, 29 Nov 2017 23:27:42 GMT

Ah, good. Glad to help.