topic Re: How to replace multiple field values with the same replacement value in a search? in Splunk Search

How to replace multiple field values with the same replacement value in a search?

alexandermunce — Wed, 07 Dec 2016 00:53:11 GMT

I am working with a field named product which contains an array of values which I would like to replace with more meaningful values for reporting purposes.

Currently I am using something along the lines of:

search query | replace product_1 with "Meaningful Product 1 Name", product_2 with "Meaningful Product 2 Name"

This seems to be working fine, however, the problem lies in instances where there are multiple values within the ** field which all relate to the one product. I attempted to approach this as:

search query | replace product_x with "Meaningful Product X Name", product_x2 with "Meaningful Product X Name"

However, this throws an error upon running the search - what would be the correct approach to use here?

Side note; I am replacing 100+ values within the product field which is making the search rather verbose - is there a more efficient way to go about this?

Cheers!

Re: How to replace multiple field values with the same replacement value in a search?

sundareshr — Wed, 07 Dec 2016 15:19:47 GMT

Have you considered lookup tables? Would work something like this

1) Create a lookup csv with two columns - product meaningful_product

2) Use the lookup in your search to make dynamic replacement/addition, like this

base search | lookup productlist.csv product OUTPUT meaningful_product AS product | ...

https://docs.splunk.com/Documentation/Splunk/6.5.1/Knowledge/Addfieldsfromexternaldatasources

Re: How to replace multiple field values with the same replacement value in a search?

alexandermunce — Fri, 09 Dec 2016 03:32:13 GMT

Thank you for your response - however I am curious as to whether this dynamic replacement will be viewable to other parties who I may share the report/dashboard with?

Re: How to replace multiple field values with the same replacement value in a search?

alexandermunce — Fri, 09 Dec 2016 03:32:28 GMT

@sundareshr

Re: How to replace multiple field values with the same replacement value in a search?

sundareshr — Fri, 09 Dec 2016 03:37:40 GMT

Permissions for lookups are configured the same as for report. They can be kept private (no one else can see it), shared at app level (everyone with access to app can see it) globa (everyone can see it). Additionally, lookups can be made automatic as well, So everytime the specific sourcetype is query, meaningful_product is included.

https://docs.splunk.com/Documentation/Splunk/6.5.1/Knowledge/ConfigureCSVlookups

Re: How to replace multiple field values with the same replacement value in a search?

alexandermunce — Tue, 13 Dec 2016 05:10:59 GMT

Hi I have implemented the lookup CSV file however when I attempt to invoke it, it does not return any results.

However I did not implement Step 2 in the below guide - is Step 2 essential?

https://docs.splunk.com/Documentation/Splunk/6.5.1/Knowledge/ConfigureCSVlookups

Re: How to replace multiple field values with the same replacement value in a search?

alexandermunce — Tue, 13 Dec 2016 05:27:24 GMT

I believe I have figured this out by adding the CSV under Lookup Definitions!
Testing now.

Re: How to replace multiple field values with the same replacement value in a search?

alexandermunce — Tue, 13 Dec 2016 05:41:13 GMT

@sundareshr
The stanza for the CSV has been added so I am using the following in my search:

lookup vgate_prod_names product as product OUTPUT meaningful_product AS product

So the search is now returning results, however the Product field is displaying as blank for all events?

Re: How to replace multiple field values with the same replacement value in a search?

alexandermunce — Tue, 13 Dec 2016 06:12:51 GMT

I have setup a lookup CSV which looks something like:

product, meaningful_product
product_1, "Meaningful Name 1"
product_2, "Meaningful Name 2"

etc..

I have added the lookup file to the Splunk Lookup Table files as below;

/opt/splunk/etc/apps/search/lookups/product_names.csv

I have added a Lookup Definition for the CSV as below:

Name                  Type      Supported Fields
vgate_prod_names      file      product,meaningful_product

Now that I have completed all of the above steps, I am testing this in one of my searches in a similar method to the below:

search query | lookup vgate_prod_names product AS product OUTPUT meaningful_product AS product | more search

I have also tried this syntax:

search query | lookup vgate_prod_names product OUTPUT meaningful_product AS product | more search

However in both instances, the search returns all of the results it should, however the product field contains only blank values.

What am I doing wrong??

Re: How to replace multiple field values with the same replacement value in a search?

sundareshr — Tue, 13 Dec 2016 14:10:45 GMT

There are few things to consider

1) Permission on the lookup table. I would suggest start by setting it to global, verify everything is working and then scale back.

2) Values in the lookup field has to identical (case-sensitive) to the values in index field

3) see if you get any result for this | inputlookup vgate_prod_names

4) Try changing the fieldname in the output lookup vgate_prod_names product as product OUTPUT meaningful_product AS product_name

Re: How to replace multiple field values with the same replacement value in a search?

alexandermunce — Tue, 13 Dec 2016 16:34:40 GMT

@sundareshr as a side note, if I change my query to:

lookup vgate_prod_names product as product OUTPUT meaningful_product AS product_new

The I get a new field added to my search called "product_new" with all the values whigh I expected from my original search.

Is it true that I need to have a replacement value for EVERY possible value of product in my lookup table for this function to work correctly?

Re: How to replace multiple field values with the same replacement value in a search?

sundareshr — Tue, 13 Dec 2016 16:49:34 GMT

For the replacement to work correctly, the value in product field has to match. You do have the option of setting up your lookup with wildcards, but the replacement will the same for all matching values. For example prod1, prod2, prod3 can have a single prod* in the lookup to map to "same description for any of the three variations". For this to work, you will have to configure your lookup as

https://answers.splunk.com/answers/52580/can-we-use-wildcard-characters-in-a-lookup-table.html