How to group disparate data in stats (Interface Names and Routers)

[albyva]

Here is my sample data:

CoreRouter peer uplink speed

--

Core1.stl gw1.stlouis fe-0/0 100000

Core2.stl gw1.stlouis fe-0/0 100000

Core1.bos gw1.boston fe-0/0 100000

Core2.bos gw1.boston fe-0/1 100000

When I run this search, I get the following output:

[search] | stats latest(bps_out) AS CurrentBPS BY CoreRouter,Uplink,peer,speed | stats sum(speed) AS MaxSpeed, sum(CurrentBPS) AS CurrentBPS by peer,Uplink | eval Util=round((CurrentBPS/MaxSpeed)*100)

peer count UpLink MaxSpeed CurrentBPS Util

gw1.stlouis 2 fe-0/0 200000 120620 60

gw1.boston 1 fe-0/0 100000 38634 39

gw1.boston 1 fe-0/1 100000 37506 38

What's happening is that I need to group uplinks together per CoreRouter. When the uplinks are the same name (ie: fe-0/0) they are grouped together so I get the aggregate of both links, which is what I want. But when the uplink names are different (ie: fe-0/0 & fe-0/1), they don't get grouped together which prevents me from getting the aggregate data I need.

How can I group together uplinks in the same CoreRouter, but have different uplink names? In the example above, how can two links in Core1/2.bos be grouped together while also maintaining the aggregation of Core1/2.stlouis? What I should end up with is something like this:

peer count MaxSpeed CurrentBPS Util

gw1.stlouis 2 200000 120620 60

gw1.boston 2 200000 76140 38

NOTE: I don't need the Uplink interface name to be displayed in the output. I just listed it above to show the differences in the data.

Thanks,

[somesoni2]

Try this

[search] | stats latest(bps_out) AS CurrentBPS values(uplink) as uplink BY CoreRouter ,peer,speed | stats count, sum(speed) AS MaxSpeed, sum(CurrentBPS) AS CurrentBPS by peer,Uplink | eval Util=round((CurrentBPS/MaxSpeed)*100)

Update

try this

[search] | stats latest(bps_out) AS CurrentBPS BY CoreRouter,Uplink ,peer,speed | stats count, sum(speed) AS MaxSpeed, sum(CurrentBPS) AS CurrentBPS values(uplink) as uplink by peer | eval Util=round((CurrentBPS/MaxSpeed)*100)

OR 
[search] | stats latest(bps_out) AS CurrentBPS BY CoreRouter,Uplink ,peer,speed | stats count, sum(speed) AS MaxSpeed, sum(CurrentBPS) AS CurrentBPS by peer | eval Util=round((CurrentBPS/MaxSpeed)*100)

The output should be like this

peer        uplink   count MaxSpeed CurrentBPS Util   

gw1.stlouis  fe-0/0  2     200000   120620     60  

gw1.boston   fe-0/0  2     200000   76140      38
             fe-0/1

[albyva]

On the Update, it do see that it's now displaying the interfaces, but it's not aggregating the those with disparate interface names together.

I'm no so sure | stats is up to the job. Maybe I need a |transaction entry? Cause splunk needs to identify every interface associated with a peer and then add up their data.

I might have to go back to the drawing board on this one.

[albyva]

No luck. 😞

What's happening in the Search is this:

CoreRouter Uplink bps_out

Core1.stl fe-0/0 38634
Core2.stl fe-0/1 37506

I then run the data through a CVS file (| lookup uplinks.csv CoreRouter as CoreRouter Uplink as Uplink OUTPUT speed peer). In the CVS file it just has the Core Router, Uplink, Peer, and speed of the link.

From that, I then add in the stats:
| stats latest(bps_out) AS CurrentBPS BY CoreRouter,Uplink,peer,speed | stats sum(speed) AS MaxSpeed, sum(CurrentBPS) AS CurrentBPS by peer,Uplink | eval Util=round((CurrentBPS/MaxSpeed)*100)