How to use Appendcols in subsearch lookup eval?

[smaran06]

Hi Team,

I have two searches one is normal search and another in lookup, both returns the count. Both always return a single value so I used appendcols. My end goal is to perform eval operation on them as shown below.

Query

index=data
| stats dc(number) as X_data
| appendcols
[| inputlookup data.csv
| stats dc(number) as Y_data]
| eval result =X_data/Y_data

Since the outer search results returns fast in this case X_data, the eval is evaluating the expression before outer search is complete Y_data. 

Example

• X_Data =237
• Y_data =71
• Expected result = 3.29
• Actual result = 1.00

How do I fix this issue

[smaran06]

Are you saying that in your final table with 3 columns, you have X_data showing 237, Y_data showing 71 and result showing 1.00?

• Yes That is correct

You are using dc(number) which is counting the distinct values of your field 'number'. Is that what you intended to use?

•   Yes want to use distinct values

[bowesmana]

Odd, that just doesn't seem possible.

Can you run these two and confirm what you get

index=data
| stats dc(number) as X_data
| appendcols [
  | makeresults
  | eval Y_data=71
]
| eval result =X_data/Y_data

| makeresults
| eval X_data=237
| appendcols [
  | inputlookup data.csv
  | stats dc(number) as Y_data
]
| eval result =X_data/Y_data

[bowesmana]

Are you saying that in your final table with 3 columns, you have X_data showing 237, Y_data showing 71 and result showing 1.00?

Subsearches (your inputlookup search) run before the main search (outer index=data search).

It would not be true that one search completing before another affects the results.

You are using dc(number) which is counting the distinct values of your field 'number'. Is that what you intended to use?