When main query return 0 results, how to stop subquery give error?

[xiaoyunwuxie]

In my subquery, I'm using results returned from main query, when main query have results it works. But when main query return 0 results, it will return the following error: "Error in 'map': Did not find value for required attribute 'id'.", how can I make it so it will just return 0 results instead of give error.

| dbxquery connection="oracle_test" query="SELECT 1 id
FROM dual where 1=0"
| map search="dbxquery connection=\"oracle_test_1\" query=\"select dummy col_text from dual where 1 in ('$id$')\""
| table col_text

[woodcock]

You have to create a fake event before you call map and then throw it away before you use it, like this:

... | rename COMMENT1of5 AS "Without the 'ThisIsZeroIfThereAreNoEvents' stuff, when there are no events,"
| rename COMMENT2of5 AS "we get 'field not defined' errors from the 'map' call.  This is because,"
| rename COMMENT3of5 AS "with no events, the field-generating/renaming commands get optimized out."
| rename COMMENT4of5 AS "The solution is to ensure that there is always at least 1 dummy/fake event"
| rename COMMENT5of5 as "that has all the fields and then we drop it right before the 'map' call."
| appendpipe
[ stats count AS ThisIsZeroIfThereAreNoEvents
| where ThisIsZeroIfThereAreNoEvents=0
| eval enumerate = "FAKE"
| eval each      = "FAKE"
| eval of        = "FAKE"
| eval your      = "FAKE"
| eval fields    = "FAKE"
| eval here      = "FAKE"]

| rename COMMENT AS "Drop the dummy/fake event"
| where isnull(ThisIsZeroIfThereAreNoEvents)

| map search=" ... "

[xiaoyunwuxie]

@woodcook, I've tried your method, but still give me the same error.

[niketn · Accepted solution]

@xiaoyunwuxie since map command expects results from the main search, and this is for alert, you would have to create query in a way that even in case of no results the main search default some value which would not return and result (but at the same time does not throw map command error).

Following is a run anywhere search query based on Splunk's _internal log which gives count of log_level FATAL. In case you do not have access to query _internal index or there are no FATAL logs, then the map command will error out stating Error in 'map': Did not find value for required attribute 'log_level'..

index=_internal sourcetype=splunkd log_level=FATAL 
| stats count by log_level 
| map search=" search index=_internal sourcetype=splunkd log_level=\"$log_level$\" | stats count by log_level"

Following is the query with dummy search appended to add default values for log_level FATAL in case it is not present using append and dedup command.

index=_internal sourcetype=splunkd log_level=FATAL 
| stats count by log_level 
| append
    [| makeresults 
    | fields - _time 
    | eval log_level="FATAL",count=0]
| dedup log_level
| map search=" search index=_internal sourcetype=splunkd log_level=\"$log_level$\" | stats count by log_level"

Please try out and confirm. Also adjust accordingly for your use case as your query uses bdxquery and you would want to decide which query to run/default in case main query does not return results.

[niketn]

@xiaoyunwuxie is this for dashboard or report?

[xiaoyunwuxie]

@niketnilay This is for splunk alert

[arjunpkishore5]

try this

| dbxquery connection="oracle_test" query="SELECT 1 id FROM dual where 1=0"
| search id=*
| map search="dbxquery connection=\"oracle_test_1\" query=\"select dummy col_text from dual where 1 in ('$id$')\""
| table col_text

Let me know if this works

[xiaoyunwuxie]

@arjunpkishore5 I still get the same error: Error in 'map': Did not find value for required attribute 'id'.