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