topic How do I include fields from a lookup table to charted data? in Splunk Search

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

pipegrep — Mon, 28 Sep 2020 18:20:30 GMT

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.

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

musskopf — Mon, 08 Dec 2014 03:26:57 GMT

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,

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

pipegrep — Mon, 28 Sep 2020 18:20:37 GMT

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.

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

musskopf — Mon, 08 Dec 2014 21:25:54 GMT

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.

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

pipegrep — Mon, 28 Sep 2020 18:20:50 GMT

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