Splunk Search

How to replace values without using a join

robertlynch2020
Motivator

I am using a join, but is there a better way to replace values?

I have the following table. (NICKNAME + Human_Name_Nickname are the headers)

NICKNAME    Human_Name_Nickname
mx  MX_BASIC
smcrisk_engine  RISK_ENGINE
mxtraderepository_engine    MX_TRADE_REPO_ENGINE
smcobjectrepository_engine  SM_ENGINE
mxmlexchange_mxtaskxa   MXMLEXCHANGE
mxdealscanner_engine    DEAL_SCANNER
mx_cesar    CESAR
mx_marketdata_repository_engine MARKET_DATA
mxprocessingscript  PROCESSING_SCRIPT

I am retriving back thousands of lines of data with NICKNAME, i want to replace values from the lookup table.
E.G find "mx" and replace it with "MX_BASIC" etc.. so lots of entries. Then find "smcrisk_engine" and replace it with "RISK_ENGINE" if no match use the original value.

This is what i have- But i have been told not to use joins...can i do this better?

| join  NICKNAME type=left [inputlookup TEST_MXTIMING_NICKNAME.csv  ] | fillnull Human_Name_Nickname | eval Human_Name_Nickname=if(Human_Name_Nickname=0,$$NICKNAME$$,Human_Name_Nickname) | rename Human_Name_Nickname AS NICKNAME.

This works, but i am concerned of performance.

0 Karma
1 Solution

woodcock
Esteemed Legend

Instead do this:

... | lookup TEST_MXTIMING_NICKNAME NICKNAME
| eval NICKNAME=coalesce(Human_Name_Nickname, NICKNAME)
| fields - Human_Name_Nickname

View solution in original post

0 Karma

woodcock
Esteemed Legend

Instead do this:

... | lookup TEST_MXTIMING_NICKNAME NICKNAME
| eval NICKNAME=coalesce(Human_Name_Nickname, NICKNAME)
| fields - Human_Name_Nickname
0 Karma

robertlynch2020
Motivator

Brill - Cheers 🙂

0 Karma

gcusello
SplunkTrust
SplunkTrust

Hi robertlynch2020,
lookup command is a left join so you can write something like this

your_search
| lookup TEST_MXTIMING_NICKNAME.csv NICKNAME OUTPUT other_lookup_fields
| ...

if the NICKNAME field is different between search and lookup, you can write

your_search
| lookup TEST_MXTIMING_NICKNAME.csv NICKNAME AS different_NICK_NAME OUTPUT other_lookup_fields
| ...

Bye.

Giuseppe

0 Karma

richgalloway
SplunkTrust
SplunkTrust

Joins do not perform well so it's a good idea to avoid them. What you are trying to do seem pretty straightforward and can easily be done without a join.

<your search that returns events with NICKNAME field> | lookup TEST_MXTIMING_NICKNAME.csv NICKNAME OUTPUT Human_Name_Nickname | eval NICKNAME=coalesce(Human_Name_Nickname,NICKNAME) | ...

This query searches the lookup file for the given NICKNAME and returns the associates Human_Name_Nickname. The coalesce statement sets NICKNAME to the value if Human_Name_Nickname if it is not null, otherwise it's set to itself.

---
If this reply helps you, Karma would be appreciated.
Get Updates on the Splunk Community!

What's new in Splunk Cloud Platform 9.1.2312?

Hi Splunky people! We are excited to share the newest updates in Splunk Cloud Platform 9.1.2312! Analysts can ...

What’s New in Splunk Security Essentials 3.8.0?

Splunk Security Essentials (SSE) is an app that can amplify the power of your existing Splunk Cloud Platform, ...

Let’s Get You Certified – Vegas-Style at .conf24

Are you ready to level up your Splunk game? Then, let’s get you certified live at .conf24 – our annual user ...