topic Re: How to change how field values appear on a chart (ex: "cat" to "gato") via a lookup table? in Splunk Search

How to change how field values appear on a chart (ex: "cat" to "gato") via a lookup table?

choward94002 — Thu, 16 Apr 2015 21:05:59 GMT

I have a chart which graphs counts of things over time; so, animals per second. There are columns for cats, dogs and rats and each gets its own column and its own label on the side ... inbound field of "animal" which can contain "rat, cat or dog" over time. What I would like to do is translate "cat" to "gato", "dog" to "perro", "rat" to "rata" at the time of the chart being drawn. Programmatically this would be accomplished via a lookup table at the time that the chart was being drawn so that the legend for "dog" would be displayed as "perro" ...

Is this possible with either simple or advanced XML?

Thx!

Re: How to change how field values appear on a chart (ex: "cat" to "gato") via a lookup table?

somesoni2 — Thu, 16 Apr 2015 21:16:40 GMT

Could you provide more details like, how is your current query and its output and what is expected from the search result point of view?

Re: How to change how field values appear on a chart (ex: "cat" to "gato") via a lookup table?

choward94002 — Fri, 17 Apr 2015 15:59:50 GMT

Thanks for your help! The index being used contains two values, "Timestamp" and "Animal" where each entry contains the time of the event and what kind of animal occurred; cat, dog, rat, etc. ... so,

00:01:30,Dog
00:01:31,Cat
00:01,31,Rat
00:01,45,Dog

I want to display a column chart of animals per minute, so this chart would have three "bins", the first bin containing one "Dog" column count, the second bin containing one "Cat" and one "Rat" count column, the third bin containing one "Dog" column

The query is [index="Foo" | chart count by Timestamp, Animal]

That all works, and on the right of the chart I get a legend listing "Dog", "Cat" and "Rat" corresponding to the data values for "Animal" ... what I'd like, though, is for some sort of lookup to change "Dog" to "Perro", "Cat" to "Gato" and "Rat" to "Rata" on the legend. I don't want to post-process the index itself, changing all of the "Dog"'s to "Perro"'s, and I can't change the incoming data to say "Gato" rather than "Cat" ... the change needs to happen at the time the chart is generated. Programmatically I could do it using C# and a charting package, but I was curious if that was possible using the provided Splunk stuff ..

Re: How to change how field values appear on a chart (ex: "cat" to "gato") via a lookup table?

masonmorales — Fri, 17 Apr 2015 20:49:07 GMT

First create a CSV file, with all the current, and new names you want:

Animal,NewAnimal
Cat,Gato
Dog,Perro

Next, add your CSV file to Splunk, by going to Settings -> Lookups -> Lookup table files -> Add new

Choose your lookup file and give it a destination file name (it can be the same as the existing file name). Click Save.

Then, add a lookup definition by going to Settings -> Lookups -> Lookup definitions -> Add new

Give the lookup a name. Again, it can be the same as your file name, or you could simply call it "animals". Leave it on "File-based" and then select your CSV file from the drop-down menu. Click Save.

Now, you can use your lookup file in your search. Assuming you called the lookup definition "animals", you could do:

index="Foo" | lookup animals Animal OUTPUT NewAnimal| chart count by Timestamp, NewAnimal

Re: How to change how field values appear on a chart (ex: "cat" to "gato") via a lookup table?

choward94002 — Fri, 17 Apr 2015 21:42:29 GMT

Cool, I'll give that a try, much thanks!

Re: How to change how field values appear on a chart (ex: "cat" to "gato") via a lookup table?

masonmorales — Fri, 17 Apr 2015 21:46:43 GMT

Please click "Accept Answer" if this worked for you