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!

.conf24 | Registration Open!

Hello, hello! I come bearing good news: Registration for .conf24 is now open!   conf is Splunk’s rad annual ...

Splunk is officially part of Cisco

Revolutionizing how our customers build resilience across their entire digital footprint.   Splunk ...

Splunk APM & RUM | Planned Maintenance March 26 - March 28, 2024

There will be planned maintenance for Splunk APM and RUM between March 26, 2024 and March 28, 2024 as ...