How to coalesce three events

[shimada-k]

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

[yuanliu]

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

or

| 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

[gcusello]

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

[shimada-k]

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

[gcusello]

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

[shimada-k]

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

[gcusello]

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

[shimada-k]

OK. Thanks for you help, gcusello.

[gcusello]

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 😉