How do I edit my if else search for comparing two values?

[sushmitha_mj]

I have a field Name and a field ID. So a person named Adam has an ID 1. The next time Adam is renamed Rob, but ID remains 1, and next time, he could become Sam.
I want to write a search that would return the original name Adam in all three cases where ID is 1.

Table structure :
Name ------ ID
Adam ------ 1
Rob -------- 1
Sam -------- 1
So to know which value is the original, I have a theory. There is a pattern to all names: the 2nd and 3rd letters are always "da" . How can I retrieve this value?
My search :

| stats values(Name)  by ID | eval original_name=if (substr(Name,2,2)="da",names)     

Is this the right way or should I use a regex to do this?

[woodcock]

Try this:

...| stats values(Name) AS names BY ID | eval original_name=coalesce(
(substr(mvindex(names,0),2,2)="da"),mvindex(names,0),
(substr(mvindex(names,1),2,2)="da"),mvindex(names,1),
(substr(mvindex(names,2),2,2)="da"),mvindex(names,2),
(substr(mvindex(names,3),2,2)="da"),mvindex(names,3),
(substr(mvindex(names,4),2,2)="da"),mvindex(names,4),
(substr(mvindex(names,5),2,2)="da"),mvindex(names,5))

[aljohnson_splun · Accepted solution]

I think you could use stats earliest to do this.

... | stats earliest(Name) by ID

as long as you look far back enough in time to make sure you're including the earliest time.

[sushmitha_mj]

Thank you... It worked...

[fdi01]

see what Mr aljohnson_splunk do, just renam like:

... | stats earliest(Name) as original_name by ID