Splunk Search

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

timcolpo
Explorer

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

Tags (1)
0 Karma

somesoni2
Revered Legend

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
0 Karma
Get Updates on the Splunk Community!

See just what you’ve been missing | Observability tracks at Splunk University

Looking to sharpen your observability skills so you can better understand how to collect and analyze data from ...

Weezer at .conf25? Say it ain’t so!

Hello Splunkers, The countdown to .conf25 is on-and we've just turned up the volume! We're thrilled to ...

How SC4S Makes Suricata Logs Ingestion Simple

Network security monitoring has become increasingly critical for organizations of all sizes. Splunk has ...