topic Re: problem with join in Splunk Search

problem with join

jiaqya — Tue, 29 Sep 2020 18:26:41 GMT

i am trying to join 2 indexes and ClientName.
i find some rows are not joining on ClientName.
but if i explicitly mention ClientName="actualname" then i get the output of join.
if i dont mention it explicitly it works for most but does not work for few.

ex: this one does not give me output
index=sccm_sccmclient earliest=-2d@d latest=now ClientName="actualclient" |dedup ClientName sortby -_time| table ClientName,City,OU,SP,OS | eval ClientName=lower(ClientName) |eval ClientName=mvindex(split(ClientName,"."),0)|join type=left ClientName[search index=itsm_computers | eval ClientName=lower(ClientName) |eval ClientName=mvindex(split(ClientName,"."),0)]|table ClientName,Region,SiteCode

this one gives output
index=sccm_sccmclient earliest=-2d@d latest=now ClientName="actualclient" |dedup ClientName sortby -_time| table ClientName,City,OU,SP,OS | eval ClientName=lower(ClientName) |eval ClientName=mvindex(split(ClientName,"."),0)|join type=left ClientName[search index=itsm_computers ClientName="actualclient" | eval ClientName=lower(ClientName) |eval ClientName=mvindex(split(ClientName,"."),0)]|table ClientName,Region,SiteCode

i am not able to understand this why ? whats the difference here..

Re: problem with join

p_gurav — Wed, 14 Mar 2018 12:47:28 GMT

Can you try:

index=sccm_sccmclient earliest=-2d@d latest=now ClientName="actualclient" |dedup ClientName sortby -_time| table ClientName,City,OU,SP,OS | eval ClientName=lower(ClientName) |eval ClientName=mvindex(split(ClientName,"."),0)|join type=left ClientName[search index=itsm_computers ClientName="*" | eval ClientName=lower(ClientName) |eval ClientName=mvindex(split(ClientName,"."),0)]|table ClientName,Region,SiteCode

Re: problem with join

lguinn2 — Wed, 14 Mar 2018 13:09:24 GMT

First, how many events are being returned from the second search? There is a limit, and my guess is that the first search hits the limit, but that the second search - because it is constrained to a single client name - does not hit the limit.

Second, you may not be showing the entire search, but I see no need for the join at all. The following search would be much more efficient, although it might not be exactly what you wanted

(index=sccm_sccmclient OR index= itsm_computers) ClientName="actualclient" 
| fields ClientName City OU SP OS Region SiteCode
| stats list(*) as * by ClientName

Re: problem with join

jiaqya — Tue, 29 Sep 2020 18:26:49 GMT

Iguinn, Thanks a lot, this query you gave is much faster , but i have one correction to make,

the first index sccm_sccmclient has 30k row and the second one itsm_computers has 60k rows.
so with the query you mentioned im getting back 90k rows.

i want the first index to be basically the base so i should only get column updates to these 30k rows only. how can this be done with the above query..

Re: problem with join

jiaqya — Wed, 14 Mar 2018 14:20:15 GMT

Iguinn, would it work if i use the sourcetype

both these indexes have different sourcetype names. i could just ignore the 2nd sourcetype so i get the updated columns for the 1st source type.

would this be a good way to achieve this..

Re: problem with join

jiaqya — Thu, 15 Mar 2018 04:16:37 GMT

my basic requirement : as you said there are more than 60k rows which i am looking to join due to which its not joining completely . i just need to add 3 columns from the 2nd index to the columns of 1st index. i need help with query.
due to 60k rows, its not joining all of them.

Re: problem with join

jiaqya — Fri, 16 Mar 2018 14:33:17 GMT

instead of choosing to increase the limit for join , i went ahead and used the lookup option. this works well for me..
Iguinn , thanks for the direction..

previous query > | lookup test.csv ClientName OUTPUT |table ClientName,City,Site,OU

john.