combine different fileds from different events

[mvagionakis]

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

[kamlesh_vaghela]

HI

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

[DalJeanis]

@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.

[kamlesh_vaghela]

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

[mvagionakis]

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

[DalJeanis · Accepted solution]

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
    ]

[somesoni2]

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

[mvagionakis]

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

[mvagionakis]

Hello again,

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

Thank you again DalJeanis 🙂
Have a great day

[DalJeanis]

Ah, good. Glad to help.