How do you join a field which does not exist in the subsearch?

[bollam]

I need help with the following scenario.

I want to join one of the fields of the main search to the sub search,l which does not exist in the sub search, to make some calculations.

First search:

 index=main type=test 
| eval totalUsage=upper(mvindex(split(usage,"."),0)) 
| table index, total_memory, type

The second search does not contain the total_memory field and I want to inherit "total_memory" from the first search in order to calculate the "used_memory".

Below is the query I have written, but it does not give me the results. I would require help achieving the desired results.

index=main type=test 
| eval totalUsage=upper(mvindex(split(usage,"."),0)) 
| table index, total_memory, type 
| join total_memory 
    [ search index=main type=test 
    | used_memory = round(current_memory/total_memory * 100, 2) ]
| table index used_memory total_memory

[DalJeanis]

The term "join" is not helpful here.

what are you comparing the memory of? is it the same host? Use host.

The events you are combining appear to be in the same index and type. Are there more than one record? Do you want to take only the latest number of each type? if so, do you want the latest number of all, or the latest for each host?

I'm going to assume that there are two different kinds of event data, and that you want the most recent of each.

index=main type=test
|  fields  index type .... list all the fields you need from either type...
|  eval rectype=case(if it is the first kind of record, "1", if it is the second kind of record, "2")
| dedup rectype
| stats values(*) as * 

Now all the fields from both events are together on a single record.

[accsam]

Your sample query represents that your looking for the same data(index and type) and comparing.In that case no need to use subsearch.. below query should help you.

index=main type=test
| eval totalUsage=upper(mvindex(split(usage,"."),0))
| eval used_memory = round(current_memory/total_memory * 100, 2) ]
| table index used_memory total_memory

In case you are looking from two different data sets where you require to have a common field to correlate and that common field should be part of table in the subsearch.

[niketn]

@bollam some kind of correlation must be present between the two indexes. Could you please provide what is it if not the total_memory?

[HiroshiSatoh]

I think that I can not reply with this search sentence.
It is better to present sample data.