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?
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.
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))
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.
Thank you... It worked...
see what Mr aljohnson_splunk do, just renam like:
... | stats earliest(Name) as original_name by ID