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

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!

What's new in Splunk Cloud Platform 9.1.2312?

Hi Splunky people! We are excited to share the newest updates in Splunk Cloud Platform 9.1.2312! Analysts can ...

What’s New in Splunk Security Essentials 3.8.0?

Splunk Security Essentials (SSE) is an app that can amplify the power of your existing Splunk Cloud Platform, ...

Let’s Get You Certified – Vegas-Style at .conf24

Are you ready to level up your Splunk game? Then, let’s get you certified live at .conf24 – our annual user ...