Splunk Search

How do I include fields from a lookup table to charted data?

pipegrep
Path Finder

I'm using this search to retrieve indexing data by month;

index="_internal" source="*metrics.log" group="per_host_thruput"| chart sum(kb) by series date_month | sort + series

It returns in this format, which works well for me.

HOST January February March ...
hosta 1234567 1234567 1234567 ...
hostb 2345678 1234567 1234567 ...
hostc 3456789 1234567 1234567 .

I've created a lookup table that gives every indication of success when I look at the search output in event mode, I see them listed,
E.g.;
host_owner Infrastructure
host_role Splunk indexer

I expect including these fields will reduce downstream questions but I'm having problems getting a good search when including them. I think part of my problem might be that the search is actually getting the host name from the series and I'm using the data inappropriately. Actually now that I look at it I'm certain. The host for each of these servers is an indexer.

Does this mean that I need to create a different lookup table? Ultimately I need to add some additional information beyond the hostname.

0 Karma
1 Solution

musskopf
Builder

The search:

index="_internal" source="*metrics.log" group="per_host_thruput" | chart sum(kb) by series date_month 

is not returning a column named host so the lookup will not be able to find anything. You could rename series to *host before the lookup:

index="_internal" source="*metrics.log" group="per_host_thruput" | chart sum(kb) by series date_month | rename series AS host | lookup host_tags host | sort + host

Another thing to note is that lookups are case-sensitive.

View solution in original post

0 Karma

pipegrep
Path Finder

And of course that was it. Thanks!

index="_internal" source="*metrics.log" group="per_host_thruput" | eval gb=((kb/1024)/1024) | chart sum(kb) by series date_month | lookup host_tags host as series | sort + series

0 Karma

musskopf
Builder

The search:

index="_internal" source="*metrics.log" group="per_host_thruput" | chart sum(kb) by series date_month 

is not returning a column named host so the lookup will not be able to find anything. You could rename series to *host before the lookup:

index="_internal" source="*metrics.log" group="per_host_thruput" | chart sum(kb) by series date_month | rename series AS host | lookup host_tags host | sort + host

Another thing to note is that lookups are case-sensitive.

0 Karma

musskopf
Builder

Hello pipegrep,

I think you could resolve you problem with a single lookup, let's say hosts.csv, which would looks like:

host, role, owner
hosta, aaa, john
hostb, ccc, mary

now you could use your search like:

index="_internal" source="*metrics.log" group="per_host_thruput"| chart sum(kb) by series date_month | lookup hosts.csv host | sort + series

The search command would grab the corresponding Role and Owner from the lookup file and add the additional columns to your chart.

ps.: remember to save your lookup inside the $SPLUNK_HOME/etc/apps//lookups. `` normally is search.

Does that makes sense?

Cheers,

0 Karma

pipegrep
Path Finder

It seems like it should, but columns are not appearing. If I use the method you show and reference my CSV filename, the file is not found. Since I have a lookup table defined and saved, I used that.

This search; | inputlookup host_tags

Outputs data in the anticipated format
host host_owner host_role
clks01 Infrastructure Linux Utility Server
clksapp01 Plant FIX CMDB
clksvjpvm01 YB Clarksville YB Plant 1
clksvjpvm02 YB Clarksville YB Plant 2

In the end, this search doesn't display the additional "host_tags" columns;

index="_internal" source="*metrics.log" group="per_host_thruput" | chart sum(kb) by series date_month | lookup host_tags host | sort + series

Baffling.

0 Karma
Get Updates on the Splunk Community!

What's new in Splunk Cloud Platform 9.1.2312?

Hi Splunky people! We are excited to share the newest updates in Splunk Cloud Platform 9.1.2312! Analysts can ...

What’s New in Splunk Security Essentials 3.8.0?

Splunk Security Essentials (SSE) is an app that can amplify the power of your existing Splunk Cloud Platform, ...

Let’s Get You Certified – Vegas-Style at .conf24

Are you ready to level up your Splunk game? Then, let’s get you certified live at .conf24 – our annual user ...