problem with join

[jiaqya]

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

[jiaqya]

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.

[jiaqya]

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.

[jiaqya]

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

[jiaqya]

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

[lguinn2 · Accepted solution]

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

[p_gurav]

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