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!

Index This | I am a number, but when you add ‘G’ to me, I go away. What number am I?

March 2024 Edition Hayyy Splunk Education Enthusiasts and the Eternally Curious!  We’re back with another ...

What’s New in Splunk App for PCI Compliance 5.3.1?

The Splunk App for PCI Compliance allows customers to extend the power of their existing Splunk solution with ...

Extending Observability Content to Splunk Cloud

Register to join us !   In this Extending Observability Content to Splunk Cloud Tech Talk, you'll see how to ...