topic Re: How can I copy a field value based on another field value ? in Splunk Search

How can I copy a field value based on another field value ?

ctaf — Tue, 29 Sep 2020 12:26:22 GMT

Hi,

I have the following table:

ID, Team, Department
1, Manager, A65

After performing a lookup, I've got the following:

ID, Team, Department, field_to_copy
1, Manager, A65, Team

As you can see, the lookup tells me which field is interesting and I'd like to copy.
The final result I'd like is:

ID, Team, Department, field_to_copy
1, Manager, A65, Manager

Is there an "eval" statement that could copy the value of "Team" and place it in "field_to_copy" ?
In my example it is "Team" but it could anything (i.e. "Department", but not only).

Thank you

Re: How can I copy a field value based on another field value ?

davebrooking — Thu, 12 Jan 2017 11:31:18 GMT

I think this answer may help you to do that?

Dave

Re: How can I copy a field value based on another field value ?

ctaf — Thu, 12 Jan 2017 12:46:21 GMT

Hi Dave, I've seen this answer, but it copies the field value to create a field name, not a field value.

Re: How can I copy a field value based on another field value ?

ctaf — Tue, 29 Sep 2020 12:27:26 GMT

If it's not doable, can I just use the value of "field_to_copy" in a where clause?

For example, I would like:

where field_A=$field_to_copy$

And the "$" sign would translate "$field_to_copy$" into "Team" so that it filter this way:

where field_A=Team

Re: How can I copy a field value based on another field value ?

skalliger — Tue, 29 Sep 2020 12:23:50 GMT

At first this sounded like an easy question. However, after reading it several times I am not so sure about it anymore.

So, let's talk about your example. First of all, I need to understand what exactly you want to archieve, that's not clear to me. Your "field_to_copy" naming kinda confuses me.

So this:

Column names: ID, Team, Department, field_to_copy
Values: 1, Manager, A65, Team

Becomes this:

Column names: ID, Team, Department,  field_to_copy
Values: 1, Manager, A65, Manager

Which means simply changing the values of field_to_copy to the values of Team?
Am I correct?

If so:

Your search with lookup first followed by an eval:

search [...] | fields ID, team, Department, field_to_copy | eval field_to_copy=$team$ | table ID, team, Department, field_to_copy as Whatever_its_real_column_name_is

Is that what you are trying to do?
When I'm wrong I'll convert to comment. But maybe you can clear my confusion. 🙂

Skalli

Re: How can I copy a field value based on another field value ?

ctaf — Tue, 29 Sep 2020 12:27:31 GMT

Hi Skalli,

First, thank you for trying to help me!

You're correct but your solution works only if, at the beginning, field_to_copy=Team.
Let's say field_to_copy=Department. In that case, I would like the result to be:

Column names: ID, Team, Department,  field_to_copy
 Values: 1, Manager, A65, A65

Re: How can I copy a field value based on another field value ?

DalJeanis — Tue, 29 Sep 2020 12:27:34 GMT

So, the trivial answer, assuming you have a small number of fields, is to use the case statement.

| eval field_to_copy = case(field_to_copy=="Team",Team,field_to_copy=="Department",Department,...)

CURLY BRACES ON THE RIGHT OF THE ASSIGNMENT DOES NOT WORK.

Deleted all but the following example of curly braces on the LEFT to avoid confusing people with erroneous guesswork.

For instance, if field_to_copy had the value "Team", this would cause the variable Team to be set to "George".

| eval {field_to_copy} = "George"

Re: How can I copy a field value based on another field value ?

ctaf — Tue, 17 Jan 2017 16:48:25 GMT

Nice! But the list of fields is not predefinied (my example was simplified so that the question would not be too hard to understand) and I always avoid to hardcode things that can change. Is there another possibility to generalize it?

The following, unfortunately, does not work...

| eval field_to_copy = if(field_to_copy==*,*,"null")

Re: How can I copy a field value based on another field value ?

DalJeanis — Tue, 17 Jan 2017 16:52:11 GMT

My answer was just updated to include curly braces, which I just found.

Tested, and it does not work. Still looking for a solution for you.

see somesoni2's answer, which works perfectly.

Re: How can I copy a field value based on another field value ?

somesoni2 — Tue, 17 Jan 2017 17:19:15 GMT

Pheww.. this was fun. Something like this works for me (run anywhere query with sample data. replace first line with your query)

| gentimes start=-1 | eval ID=1 | table ID | eval Team="Team1" | eval Manager="Manager2" | eval field_to_copy="Team"  
| foreach * [eval field_to_copy=if("<<FIELD>>"='field_to_copy','<<FIELD>>',field_to_copy)]

Re: How can I copy a field value based on another field value ?

DalJeanis — Tue, 17 Jan 2017 17:29:33 GMT

Nice. works for me too.

Now, how can we handle a NULL result?

maybe wrap with coalesce(... ,"(NULL)") ?

| foreach * [eval field_to_copy=coalesce(if("<<FIELD>>"='field_to_copy','<<FIELD>>',field_to_copy),"(NULL)")]

Re: How can I copy a field value based on another field value ?

somesoni2 — Tue, 29 Sep 2020 12:23:56 GMT

NULL in field_to_copy field??