Splunk Search

Join two fields within the same index

LIUJIEER
Explorer

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!

Tags (1)
0 Karma

sundareshr
Legend

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
0 Karma

LIUJIEER
Explorer

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.

0 Karma

LIUJIEER
Explorer

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

0 Karma

sundareshr
Legend

Remove the Source_Address="xx.xx.xxx"

0 Karma

sundareshr
Legend

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
0 Karma

JDukeSplunk
Builder

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.

0 Karma

JDukeSplunk
Builder

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

0 Karma
Get Updates on the Splunk Community!

How I Instrumented a Rust Application Without Knowing Rust

As a technical writer, I often have to edit or create code snippets for Splunk's distributions of ...

Splunk Community Platform Survey

Hey Splunk Community, Starting today, the community platform may prompt you to participate in a survey. The ...

Observability Highlights | November 2022 Newsletter

 November 2022Observability CloudEnd Of Support Extension for SignalFx Smart AgentSplunk is extending the End ...