topic Re: why is my join not working here? in Splunk Search

Why is my join not working here?

Woodpecker — Fri, 07 Jul 2023 17:00:22 GMT

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.

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

Re: why is my join not working here?

gcusello — Thu, 06 Jul 2023 06:40:45 GMT

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

Re: why is my join not working here?

Woodpecker — Thu, 06 Jul 2023 07:01:54 GMT

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

Re: why is my join not working here?

gcusello — Thu, 06 Jul 2023 07:04:40 GMT

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

Re: why is my join not working here?

isoutamo — Thu, 06 Jul 2023 07:34:00 GMT

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-join-and-the/m-p/391290/highlight/true#M113950

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