topic Re: Join two fields within the same index in Splunk Search

Join two fields within the same index

LIUJIEER — Mon, 22 Aug 2016 15:49:11 GMT

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

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!

Re: Join two fields within the same index

JDukeSplunk — Mon, 22 Aug 2016 19:01:35 GMT

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.

Re: Join two fields within the same index

JDukeSplunk — Mon, 22 Aug 2016 19:07:50 GMT

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

Re: Join two fields within the same index

sundareshr — Mon, 22 Aug 2016 19:08:06 GMT

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

Re: Join two fields within the same index

LIUJIEER — Tue, 23 Aug 2016 01:45:42 GMT

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

Re: Join two fields within the same index

sundareshr — Tue, 23 Aug 2016 01:56:35 GMT

Remove the Source_Address="xx.xx.xxx"

Re: Join two fields within the same index

sundareshr — Tue, 23 Aug 2016 11:27:24 GMT

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

Re: Join two fields within the same index

LIUJIEER — Tue, 23 Aug 2016 15:23:30 GMT

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.