topic Re: How to coalesce three events in Splunk Search

How to coalesce three events

shimada-k — Fri, 07 Jun 2024 04:09:48 GMT

Hi Experts,

I would like to create the following table from the three events.

ipv4-entry_prefix network-instance_name interface ---------------------------------------------------------------------- 1.1.1.0/24 VRF_1001 Ethernet48

Both event#1 and event#2 have "tags.next-hop-group" field and both event#2 and event#3 have "tags.index" field.All events are stored in the same index. I tried to write a proper SPL to achieve the above, but I couldn't. Could you please tell me how to achieve this?

- event#1 { "name": "fib", "timestamp": 1717571778600, "tags": { "ipv4-entry_prefix": "1.1.1.0/24", "network-instance_name": "VRF_1001", "next-hop-group": "1297036705567609741", "source": "r0", "subscription-name": "fib" } } - event#2 { "name": "fib", "timestamp": 1717572745136, "tags": { "index": "140400192798928", "network-instance_name": "VRF_1001", "next-hop-group": "1297036705567609741", "source": "r0", "subscription-name": "fib" }, "values": { "index": "140400192798928" } } -event#3 { "name": "fib", "timestamp": 1717572818890, "tags": { "index": "140400192798928", "network-instance_name": "VRF_1001", "source": "r0", "subscription-name": "fib" }, "values": { "interface": "Ethernet48" }

Many thanks,

Kenji

Re: How to coalesce three events

gcusello — Fri, 07 Jun 2024 04:26:52 GMT

Hi @shimada-k ,

please try this:

index=your_index ("tags.next-hop-group"=* OR "tags.index"=*) | rename "tags.next-hop-group" AS tags_next_hop_group "tags.index" AS tags_index "ipv4-entry_prefix" AS ipv4_entry_prefix "network-instance_name" AS network_instance_name | eva tags_index=coalesce(tags_index, tags_next_hop_group) | stats vaues(ipv4_entry_prefix) AS ipv4_entry_prefix values(network_instance_name) AS network_instance_name values(interface) AS interface BY tags_next_hop_group

in other words, you have to coalesce events with the fields "tags.next-hop-group" and "tags.index" and use it as key in a stats command.

I had to rename your fields because sometimes eval and stats commands doesn't correctly work when inside the field there are spaces, dots or minus char.

Ciao.

Giuseppe

Re: How to coalesce three events

yuanliu — Fri, 07 Jun 2024 04:41:17 GMT

What about

| stats values(tags.ipv4-entry_prefix) as ipv4-entry_prefix values(tags.network-instance_name) as network-instance_name values(values.interface) as interface

| fields *.ipv4-entry_prefix *.network-instance_name *.interface | stats values(*) as *

The latter will give

tags.ipv4-entry_prefix	tags.network-instance_name	values.interface
1.1.1.0/24	VRF_1001	Ethernet48

Re: How to coalesce three events

shimada-k — Fri, 07 Jun 2024 06:49:21 GMT

Hi gcusello,

Thanks for your prompt reply. I tried your solution. It's almost perfect, but interface field does not appear. I would appreciate it if you could give me an additional advice to resolve it.

index=gnmi ("tags.next-hop-group"=* OR "tags.index"=*) | rename "tags.next-hop-group" AS tags_next_hop_group "tags.index" AS tags_index "tags.ipv4-entry_prefix" AS ipv4_entry_prefix "tags.network-instance_name" AS network_instance_name | eval tags_index=coalesce(tags_index, tags_next_hop_group) | stats values(ipv4_entry_prefix) AS ipv4_entry_prefix values(network_instance_name) AS network_instance_name values(tags.interface) AS interface BY tags_index | sort ipv4_entry_prefix network_instance_name

Result

Many thanks,

Kenji

Re: How to coalesce three events

gcusello — Fri, 07 Jun 2024 07:14:12 GMT

Hi @shimada-k ,

sorry I mistyped the field name, probably the interface field name is different, probably its only "interface",

please see the exact field name and replace it in the search:

index=gnmi ("tags.next-hop-group"=* OR "tags.index"=*) | rename "tags.next-hop-group" AS tags_next_hop_group "tags.index" AS tags_index "tags.ipv4-entry_prefix" AS ipv4_entry_prefix "tags.network-instance_name" AS network_instance_name | eval tags_index=coalesce(tags_index, tags_next_hop_group) | stats values(ipv4_entry_prefix) AS ipv4_entry_prefix values(network_instance_name) AS network_instance_name values(interface) AS interface BY tags_index | sort ipv4_entry_prefix network_instance_name

Ciao.

Giuseppe

Re: How to coalesce three events

shimada-k — Fri, 07 Jun 2024 08:41:31 GMT

Thanks again, gcusello. Much appreciated.

Do I need to add <"values.interface" AS interface> in rename, correct?

I executed the following query.

index=gnmi ("tags.next-hop-group"=* OR "tags.index"=*) earliest="06/07/2024:08:28:14" | rename "tags.next-hop-group" AS tags_next_hop_group "tags.index" AS tags_index "tags.ipv4-entry_prefix" AS ipv4_entry_prefix "tags.network-instance_name" AS network_instance_name "values.interface" AS interface | eval tags_index=coalesce(tags_index, tags_next_hop_group) | stats values(ipv4_entry_prefix) AS ipv4_entry_prefix values(network_instance_name) AS network_instance_name values(interface) AS interface BY tags_index | sort ipv4_entry_prefix network_instance_name

Then I received the following result.

My expectation is that "Ethernet48" appears in 1st and 2nd line.

The data is as follows.

Many thanks,

Kenji

Re: How to coalesce three events

gcusello — Fri, 07 Jun 2024 08:50:06 GMT

Hi @shimada-k ,

Yes correct.

you don't have the interface field in all the events so you cannot display it in all raws.

Ciao.

Giuseppe

Re: How to coalesce three events

shimada-k — Sat, 08 Jun 2024 04:36:44 GMT

OK. Thanks for you help, gcusello.

Re: How to coalesce three events

gcusello — Sun, 09 Jun 2024 09:51:48 GMT

Hi @shimada-k ,

good for you, see next time!

let us know if we can help you more, or, please, accept one answer for the other people of Community.

Ciao and happy splunking

Giuseppe

P.S.: Karma Points are appreciated by all the contributors 😉