Aggregate/subtotal the output by locations (not currently an index field) so I can produce a graph by location

[tlmayes]

I have a query below that produces the sum of bandwidth used by remote intermediate forwarders. The output give me a simple linear output with sum by host.

index=_internal metrics thruput site-hub 11001 host=server0*  | stats sum(kb) by host

What I am trying to get without success is to aggregate/subtotal the output by locations (not currently an index field) so that I can produce a graph by location rather than a graph by host.

[DalJeanis · Accepted solution]

So you need something that maps the hosts to the locations.

This could be something like...

| stats sum(kb) as totKb by host
| join type=left host [something that gets your host to location mapping | table host location]
| eval location=coalesce(location,"unknown")
| stats sum(totKb) as totKb by location

You could also use lookup or any number of other methods.

[tlmayes]

Thanks... the lookup was the key (and the tree hiding in the forest).

[DalJeanis]

@tlmayes - yep, I've wandered that forest many times in the last 8 months. Now it's just kind of "wave and a path appears..."

[Sukisen1981]

hi,
Is the location mentioned in the events or you want to graph by iplocation?
It will be nice to see a sample of your events

[tlmayes]

No, there is no mention of location in the index, and assumed this field would be created via an eval statement.

As for the events, they are standard for every Splunk deployment, output from the "metrics.log" adding the kb field.