Join two fields within the same index

[LIUJIEER]

From one single index, there contains the following four fields, Source, Name, EquivalentName (part of the records under EquivalentName having the same data as the field, Name) and Result.
Source Name EquivalentName Result
A 1$ [blank] [blank]
B 2$ [blank] [blank]

C 2$ [blank] [blank]
D 5$ [blank] [blank]
[blank] [blank] 1 X
[blank] [blank] 2 Y
[blank] [blank] 3 Z
[blank] [blank] 4 W

The end result: I would like to retrieve the list of Result when searching for a list of Source name.

My query looks like this

index=INDEX1 Source_Address="xx.xx.xxx" |rex field=Name mode=sed "s/\$//g" |table Source, Name |dedup Name|join Name[|search index=INDEX1 |where condition |rename EquivalentName as Name] |table Name, Result

I do not get any results from this. However, when i break down the codes into two queries and retrieve 'manual', i am able to find some results. Not sure what went wrong. Kindly advise, thanks!

[sundareshr]

How about something like this

index=INDEX1 Source_Address="xx.xx.xxx" |rex field=Name mode=sed "s/\$//g" | where Name=EquivalentName | stats values(Result) as Result) by Name

*OR*

index=INDEX1 Source_Address="xx.xx.xxx" |rex field=Name mode=sed "s/\$//g" | where Name=EquivalentName | dedup Name | table Name Results

[LIUJIEER]

I realized that when i do a

index=INDEX1 Source="*"

instead of looking for the specific source using

 index=INDEX1 Source="xx.xx.xxx"

My original query has some output. But not all sources are displayed.

[LIUJIEER]

this does not work because with the Source_Address="xx.xx.xxx", the rows with Source as [blank] are filtered out. Left with rows with only Name filled and EquivalentName as [blank]. Therefore, when we do where name=equivalentname will not return any rows

[sundareshr]

Remove the Source_Address="xx.xx.xxx"

[sundareshr]

Try this

index=INDEX1 | rex field=Name mode=sed "s/\$//g" | eval Equivalent=if(Source_Address="xx.xx.xxx", Name, Equivalent) |  where Name=EquivalentName | stats values(Result) as Result) by Name

[JDukeSplunk]

It might be that you are using |table early on in the query. I think subsiquent commands will only have the data from the table to work with. So basically you are removing "EquivalentName" and "Result" early on in your search.

[JDukeSplunk]

Also, I might not understand fully, but if say I have have field1 and field2 and I need them to display as field3 I use this little bit of code.

|eval FIELD3=field1."-".field2

Which returns a new field that looks like field1-field2