Archive

problem with join

Builder

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

Tags (1)
0 Karma
1 Solution

Legend

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

View solution in original post

0 Karma

Builder

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.

0 Karma

Builder

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.

0 Karma

Builder

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

0 Karma

Builder

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

the first index sccmsccmclient has 30k row and the second one itsmcomputers 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..

0 Karma

Legend

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

View solution in original post

0 Karma

Champion

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
0 Karma