>1 Lookups in a search for a timechart Returns a SIngleRow only.

[timcolpo]

I have a need to pull a couple of totals from a lookup table within a search statement.

I have a "nat_total" and a "test_total" that are computed from a sum from a lookup table. In these lookups a sum will be returned.
For example a test search as follows:

1st TEST
Hard coding both totals for nat_total and test_total)

|inputlookup XYZ_defect_kvs | search sub_type="server" | eval _time=strptime(week_date,"%Y-%m-%d")
| eval nat_total=10000
| eval test_total=20000
| eval report_nat_total=100/nat_total | eval report_test_total=100/test_total
| eval report_total=report_nat_total+report_test_total
| timechart sum(report_nat_total) as NAT sum(report_test_total) as TEST sum(report_total) as TOTAL by failure

operates perfectly

2nd TEST
replacing "nat_total" to pull a single value from a lookup with an appendcols.

|inputlookup ATT__defect__kvs | search sub_type="server" | eval _time=strptime(week_date,"%Y-%m-%d")
| appendcols [inputlookup ATT__test_execution_count | where sw_type="nat" sub_type="server" | stats sum(dut_count) | rename sum(dut_count) as nat_total]

| eval test_total=20000
| eval report_nat_total=100/nat_total
| eval report_test_total=100/test_total
| eval report_total=report_nat_total+report_test_total
| timechart sum(report_nat_total) as NAT sum(report_test_total) as TEST sum(report_total) as TOTAL by failure

also operates perfectly.

3rd TEST
Also using a lookup to get test_total

|inputlookup ATT_defectkvs | search sub_type="server" | eval _time=strptime(week_date,"%Y-%m-%d")
| appendcols [inputlookup ATTtest_execution_count | where sw_type="nat" sub_type="server" | stats sum(dut_count) | rename sum(dut_count) as nat_total]

| appendcols [inputlookup ATT_test_execution_count | where sw_type="test" sub_type="server" | stats sum(dut_count) | rename sum(dut_count) as test_total]
| eval report_nat_total=100/nat_total
| eval report_test_total=100/test_total
| eval report_total=report_nat_total+report_test_total
| timechart sum(report_nat_total) as NAT sum(report_test_total) as TEST sum(report_total) as TOTAL by failure

Is only producing one row.

I cannot work out why this will be the case. I have also tried nesting the appendcols but I cannot get this. Basically I know that I have a very specific query against the "ATT__test_execution_count" that will always produce a value that can be used as a single value. I do know that this is a sum, but there appears to be an internal ordering in the search decompilation that is not operating, or I do not understand it properly.

[somesoni2]

Try like this

|inputlookup ATT_defect_kvs | search sub_type="server" | eval time=strptime(week_date,"%Y-%m-%d") 
| eval nat_total=[| inputlookup ATTtest_execution_count | where sw_type="nat" sub_type="server" | stats sum(dut_count)  as search ] | eval test_total= [| inputlookup ATT_test_execution_count | where sw_type="test" sub_type="server" | stats sum(dut_count) as search ] 
| eval report_nat_total=100/nat_total 
| eval report_test_total=100/test_total 
| eval report_total=report_nat_total+report_test_total 
| timechart sum(report_nat_total) as NAT sum(report_test_total) as TEST sum(report_total) as TOTAL by failure