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!

.conf24 | Registration Open!

Hello, hello! I come bearing good news: Registration for .conf24 is now open!   conf is Splunk’s rad annual ...

Splunk is officially part of Cisco

Revolutionizing how our customers build resilience across their entire digital footprint.   Splunk ...

Splunk APM & RUM | Planned Maintenance March 26 - March 28, 2024

There will be planned maintenance for Splunk APM and RUM between March 26, 2024 and March 28, 2024 as ...