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
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 |
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
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
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
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
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
OK. Thanks for you help, 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 😉