inner join

[lostcauz3]

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).

[richgalloway]

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.

[lostcauz3]

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

 index = xyz

| search [ | inputlookup xyz.csv | fields assignment SO 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) ]

[lostcauz3]

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

[gcusello]

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:

index = xyz 
| lookup xyz.csv assignment OUTPUTNEW my_field
| lookup xyz.csv SO OUTPUTNEW my_field
| lookup xyz.csv Ser OUTPUTNEW my_field
| search my_field=*
| table assignment SO Ser
| eval assignment= upper(assignment)
| eval SO = upper(SO)
| eval Ser = upper(Ser)

Ciao.

Giuseppe

[lostcauz3]

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.

[gcusello]

Hi @lostcauz3,

so try my previous answer and the following:

index = xyz ( [ | inputlookup xyz.csv | rename assignment AS query | fields query ] OR [ | inputlookup xyz.csv | rename SO AS query | fields query ] OR [ | inputlookup xyz.csv | rename ser AS query | fields query ] )
| table assignment SO Ser
| eval assignment= upper(assignment)
| eval SO = upper(SO)
| eval Ser = upper(Ser)

Ciao.

Giuseppe

[gcusello]

Hi @lostcauz3,

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

index = xyz [ | inputlookup xyz.csv | fields assignment SO Ser ]
| table assignment,SO,Ser
| eval assignment= upper(assignment)
| eval SO = upper(SO)
| eval Ser = upper(Ser) ]

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