Why is my join not working here?

[Woodpecker]

Hi,

In my first search, I got all the details which needs to be displayed in the results but it doesn't have an IP field.. So, in my second search same index different category, has an IP fields and I try to join it using the user field.

Both searches when run separately has results, but when using join it is not working.

index=A category=Requiredevents
| rename required.user as user 
| fields user category identity time 
| join type=left user [| search index=A category=Requiredevents2 required.user=* required.ipaddress=*
| rename required.user as user required.ipaddress as ipaddress | fields user ipaddress]
| table user category identity time ipaddress

I also tried using stats like this, but it didn't work

(index=A category=Requiredevents) OR (| search index=A category=Requiredevents2 )
| rename required.user as user required.ipaddress as ipaddress
| fields user category identity ipaddress time 
| stats count user category identity time ipaddress

Any help would be appreciated, thank you

[gcusello]

Hi @Woodpecker,

what do you mean with "it doesn't work"? no results, inconsistent results, what else?

Anyway, I don't see the join search because join is a solution to use only when there isn't any other solution and when you have very few events.

at first is "time" a field of your events or are you speaking of _time?

in both cases to put the timestamp in a BY clause of a stats command, you have to group values using bin or put it in the values choosing earliest or latest value

Then you cannot use "| search" in the main search.

At lease the searches thart you used in the join are different than the ones in the stats.

Then in the stats command you didin't group by user as in the join.

so see my approach and adapt it to your use case:

(index=A category=Requiredevents) OR (index=A category=Requiredevents2 required.user=* required.ipaddress=*)
| rename required.user as user required.ipaddress as ipaddress
| stats 
   count 
   values(category) AS category 
   values(identity) AS identity
   earliest(_time) AS _time
   values(ipaddress) AS ipaddress BY user 

Ciao.

Giuseppe 

[Woodpecker]

Hi @gcusello , 

I meant the inconsistent results.

I have all my required values from 

index=A category=Requiredevents

ie., the user identity category time (time- is a field in my events)

I just need the equivalent IP address from

index=A category=Requiredevents2 required.user=* required.ipaddress=*

So, I tried to perform a join using user to fetch only the IPaddress.. If I try with search proposed below I'm not seeing any values from category=Requiredvents instead I'm seeing all values- (user identity category)  from category=Requiredvents2

[gcusello]

Hi @Woodpecker,

if you separately run the two searches, have congruent results (in term of field names and values)?

The stats command runs only if they are congruent.

About the time field, if you want only one value you have to transform it in epochtime (using strptime) and take one of them or, in the stats command, use first or last option to take one value.

Ciao.

Giuseppe

[isoutamo]

Hi

as @gcusello said you should avoid to use join if possible. Here is one post how you could replace those https://community.splunk.com/t5/Splunk-Search/What-is-the-relation-between-the-Splunk-inner-left-joi...

Also on .conf have kept lot of presentation how to avoid join. Here is link to one https://conf.splunk.com/files/2020/slides/TRU1761C.pdf

r. Ismo