Archive

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

Explorer

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

Tags (1)
1 Solution

Legend

@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.

____________________________________________
| makeresults | eval message= "Happy Splunking!!!"

View solution in original post

0 Karma

Esteemed Legend

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=" ... "
0 Karma

Explorer

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

0 Karma

Legend

@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.

____________________________________________
| makeresults | eval message= "Happy Splunking!!!"

View solution in original post

0 Karma

Legend

@xiaoyunwuxie is this for dashboard or report?

____________________________________________
| makeresults | eval message= "Happy Splunking!!!"
0 Karma

Explorer

@niketnilay This is for splunk alert

0 Karma

Motivator

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

0 Karma

Explorer

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

0 Karma
State of Splunk Careers

Access the Splunk Careers Report to see real data that shows how Splunk mastery increases your value and job satisfaction.

Find out what your skills are worth!