Splunk Search

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

alexandermunce
Communicator

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!

0 Karma
1 Solution

sundareshr
Legend

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

View solution in original post

alexandermunce
Communicator

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??

0 Karma

sundareshr
Legend

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

alexandermunce
Communicator

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

0 Karma

alexandermunce
Communicator

@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?

0 Karma

sundareshr
Legend

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

alexandermunce
Communicator

@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?

0 Karma

sundareshr
Legend

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

0 Karma

alexandermunce
Communicator

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

0 Karma

alexandermunce
Communicator

@sundareshr

0 Karma

sundareshr
Legend

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

0 Karma

alexandermunce
Communicator

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?

0 Karma
Get Updates on the Splunk Community!

Index This | I am a number, but when you add ‘G’ to me, I go away. What number am I?

March 2024 Edition Hayyy Splunk Education Enthusiasts and the Eternally Curious!  We’re back with another ...

What’s New in Splunk App for PCI Compliance 5.3.1?

The Splunk App for PCI Compliance allows customers to extend the power of their existing Splunk solution with ...

Extending Observability Content to Splunk Cloud

Register to join us !   In this Extending Observability Content to Splunk Cloud Tech Talk, you'll see how to ...