topic Re: inner join in Splunk Search

inner join

lostcauz3 — Tue, 07 Dec 2021 15:52:47 GMT

i have a query like

index = xyz
| eval assignment= upper(assignment)
| eval SO = upper(SO)
| eval Ser = upper(Ser)

| join type=inner assignment,SO,Ser [ I inputlookup xyz.csv
| table assignment,SO,Ser
| eval assignment= upper(assignment)
| eval SO = upper(SO)
| eval Ser = upper(Ser) ]

is this is a valid query because i want only the events containing the common fields (assignment,SO,Ser).

Re: inner join

gcusello — Tue, 07 Dec 2021 16:01:11 GMT

Hi @lostcauz3,

you don't need to use the join command but a subsearch:

the only attention must be that the field names that you use for the match must be the same and fieldnames are case sensitive.

Ciao.

Giuseppe

Re: inner join

richgalloway — Tue, 07 Dec 2021 16:10:56 GMT

The query is valid insofar as it is syntactically correct. Whether is truly valid or not depends on if it produces the right results for your use case. What is the query supposed to do? The choice of join type depends on what you want Splunk to do with events that don't match.

Re: inner join

lostcauz3 — Tue, 07 Dec 2021 16:50:13 GMT

i need the fields which are present in the index to be joined with the ones common to the ones in the lookup file.

like assigntment in the index to the assignment in the lookup

SO in the index to SO in the lookup file

Ser in the index to Ser in the lookup

the above 3 conditions are separate conditions they are not in pairs,

i need splunk to give me only results which values for these fields which is present in the lookup as well as the index

Re: inner join

lostcauz3 — Tue, 07 Dec 2021 16:55:41 GMT

can you please explain how the below join will happen, I'm new to splunk I'm getting confused

index = xyz

Re: inner join

gcusello — Tue, 07 Dec 2021 17:10:59 GMT

Hi @lostcauz3,

if you use the join as you described in your question, you are making a full match of all three keys in both main search and lookup, the same match of my answer (but without join).

if you want to make a join between a search and a lookup you can use the "lookup" command that works as a join (for more infos see at https://docs.splunk.com/Documentation/Splunk/8.2.3/SearchReference/Lookup).

Anyway, let me better understand: did you want to check which values of each single key (assignment, SO, Ser) of the main search are individually in the lookup, is it correct?

If in your lookup there's a field not present in the main search (e.g. "my_field"), you could run something like this:

Ciao.

Giuseppe

Re: inner join

lostcauz3 — Tue, 07 Dec 2021 17:15:47 GMT

Yes you got it correct @gcusello

I want to check the individual fields in the lookup to the one in the index and then join the result.

Re: inner join

gcusello — Tue, 07 Dec 2021 17:22:31 GMT

Hi @lostcauz3,

so try my previous answer and the following:

Ciao.

Giuseppe