Splunk Search

Is it possible to replace the names of a field, thanks to a lookup table, but only when they exists in the lookup table?

Explorer

Hello,

I have a column with names, I will call it "Costumers_Names". The "names" are actually unique identifiers (unique IDs).
I also have a short list with a correspondance between IDs and actual names of the costumers. But I do not have all the names correspondance.

I would like, at the end, to have, in the "Costumers_Names" column, the actual names of the costumers (for those where I know the correspondance between the IDs and the names), and for those where I do not know the correspondace, I would like to keep the unique IDs I already have.

I thought about trying to create a lookup table with only the correspondances I know about. The problem is: When I try replacing, I have a blank space for those I do not know the name.
As there is always new Costumers and Costumers that disapear, I think that it would take me too many ressources to run a script that would run every day to update a lookup table that would look like
"
id, name
10X1Q25, 10X1Q25
1Z02A10, Frank
523D6FT, Nami
95GU2D, 95GU2D
543V215, 543V215
...
"
Moreover, I do not want to delete a Costumer ID just because he/she was not in active one day.

I also do not want to put directly the correspondance in the search (with "eval" for example), because I have multiple searches and alerts where I use that correspondance. So I would like to have something like just 1 file where I can put the correspondances I know and use it in many searches.

Therefore, do you think it is possible to replace the names of a field, thanks to a lookup table, but only when they exists in the lookup table, and keep the original name if not?
Or do you have another idea without a lookup table?

0 Karma
1 Solution

SplunkTrust
SplunkTrust

Two thoughts on accomplishing this.

First, there's an OUTPUTNEW clause, which according to the docs (and my experience) -
" If the OUTPUTNEW clause is specified, the lookup is not performed for events in which the output fields already exist. "

So if you were to do

... | lookup MyCostumerLookup name AS costumer_id OUTPUTNEW costumerid 

It could possibly maybe work. But since you didn't provide the search or the lookup syntax you were actually using, I'm not sure.

The second idea is that you can, if you have naming conflicts, just rename them to a new field name coming out of the lookup, then rename them again conditionally into the right names.

... | lookup MyCostumerLookup costumer_id AS name OUTPUTNEW costumer_id AS MyTempOutput 
| eval name = if(MyTempOutput == "", name, MyTempOutput)

I may have bodged up the direction of the AS, it's something I do. Look at the docs linked before, they have the right syntax and for some reason my brain doesn't like it. 🙂

Anyway, the idea is to do the lookup but spit the new name/looked up field out into an entirely new field called MyTempOutput. Then assign name the value of what it already is if MyTempOutput is nothing, or MyTempOutput if MyTempOutput is a value.

NOTE you said it appears to be a blank space, so you might have to fiddle with "" or " " or whatever for that match, but know that when Splunk displays a nothing on a field, it'll display a single blank space, so that could be misleading. IIRC this might be because of the precise syntax you used in the CSV (I think it's that empty quotes like "" for an empty field makes it do that. Or maybe it's no empty quotes for an empty field that does it. Gosh, I'm all over the place with my 'darn it, one way or the other' stuff today... sorry).

View solution in original post

SplunkTrust
SplunkTrust

Two thoughts on accomplishing this.

First, there's an OUTPUTNEW clause, which according to the docs (and my experience) -
" If the OUTPUTNEW clause is specified, the lookup is not performed for events in which the output fields already exist. "

So if you were to do

... | lookup MyCostumerLookup name AS costumer_id OUTPUTNEW costumerid 

It could possibly maybe work. But since you didn't provide the search or the lookup syntax you were actually using, I'm not sure.

The second idea is that you can, if you have naming conflicts, just rename them to a new field name coming out of the lookup, then rename them again conditionally into the right names.

... | lookup MyCostumerLookup costumer_id AS name OUTPUTNEW costumer_id AS MyTempOutput 
| eval name = if(MyTempOutput == "", name, MyTempOutput)

I may have bodged up the direction of the AS, it's something I do. Look at the docs linked before, they have the right syntax and for some reason my brain doesn't like it. 🙂

Anyway, the idea is to do the lookup but spit the new name/looked up field out into an entirely new field called MyTempOutput. Then assign name the value of what it already is if MyTempOutput is nothing, or MyTempOutput if MyTempOutput is a value.

NOTE you said it appears to be a blank space, so you might have to fiddle with "" or " " or whatever for that match, but know that when Splunk displays a nothing on a field, it'll display a single blank space, so that could be misleading. IIRC this might be because of the precise syntax you used in the CSV (I think it's that empty quotes like "" for an empty field makes it do that. Or maybe it's no empty quotes for an empty field that does it. Gosh, I'm all over the place with my 'darn it, one way or the other' stuff today... sorry).

View solution in original post

SplunkTrust
SplunkTrust

Has this answer helped you solve your problem? If so, please "Accept" it so the next person stumbling across it in a search will know that this worked.

If this is still unresolved, please provide more information on what's not working.

If this is resolved and you've found another answer - great! Post that here as an answer, and go ahead and mark it as Accepted! It's OK to gather karma for yourself occasionally like that.

Explorer

Hello,
Thank you very much, it is working!

Sorry for the delay, it happened that I had to work somewhere else for some time and on another topic. I am back again and I will stay there now.

To rephrase what I wanted for the next poeple reading this: "The lookup is performed for all events. If there is no match between the lookup table and the old output field, the value of the old outputfield is kept. And if there s a match, it is the result of the lookup that is kept. And those results are in the same new output field.".

ex:
SEARCH
result of the initial search:
id, item
10X1Q25, computer
1Z02A10, mouse
523D6FT, computer
95GU2D, computer
543V215, mouse

lookuptable_name.csv:
id, name
1Z02A10, Frank
523D6FT, Nami

the result I wanted:
name_, item
10X1Q25, computer
Frank, mouse
Nami, computer
95GU2D, computer
543V215, mouse

Here is the code the code that worked (Thanks to @rrich7177!):

SEARCH | lookup lookuptable_name.csv id AS id OUTPUT name AS MyTempOutput 
| eval name_ = if(MyTempOutput != "", MyTempOutput, id) | fields name_,item
0 Karma