Splunk Search

combine different fileds from different events

mvagionakis
Path Finder

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

Tags (1)
0 Karma
1 Solution

DalJeanis
SplunkTrust
SplunkTrust

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
    ]

View solution in original post

kamlesh_vaghela
SplunkTrust
SplunkTrust

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
SplunkTrust
SplunkTrust

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

0 Karma

kamlesh_vaghela
SplunkTrust
SplunkTrust

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

0 Karma

mvagionakis
Path Finder

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

0 Karma

DalJeanis
SplunkTrust
SplunkTrust

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
SplunkTrust
SplunkTrust

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

0 Karma

mvagionakis
Path Finder

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

0 Karma

mvagionakis
Path Finder

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
SplunkTrust
SplunkTrust

Ah, good. Glad to help.

0 Karma
Get Updates on the Splunk Community!

Index This | I am a number, but when you add ‘G’ to me, I go away. What number am I?

March 2024 Edition Hayyy Splunk Education Enthusiasts and the Eternally Curious!  We’re back with another ...

What’s New in Splunk App for PCI Compliance 5.3.1?

The Splunk App for PCI Compliance allows customers to extend the power of their existing Splunk solution with ...

Extending Observability Content to Splunk Cloud

Register to join us !   In this Extending Observability Content to Splunk Cloud Tech Talk, you'll see how to ...