topic Is there a way to add results to dbquery search when none are returned? Similar to 'fillnull' but for 'no results'. in Splunk Search

Is there a way to add results to dbquery search when none are returned? Similar to 'fillnull' but for 'no results'.

ericrobinson — Thu, 03 Mar 2016 14:02:10 GMT

I am using DBquery to access a DB that has our monitoring configuration. I am trying to determine ratio of monitors meeting a certain monitor naming standard. I am doing this by parsing the monitor name and searching for and counting those meeting those standards, and then counting the total number of monitors. Most business groups have a least a couple monitors meeting the standard, but a few do not. This results in the standards search for those groups returning no results. What I want, is for it to return the number 0 so that I can calculate a coverage percentage with eval.

Example:

Biz group A has 100 monitors and 10 match standard = 10% coverage
Biz group B has 200 monitors and NONE match standards. Therefore I cannot calculate the ratio.

Re: Is there a way to add results to dbquery search when none are returned? Similar to 'fillnull' but for 'no results'.

woodcock — Thu, 03 Mar 2016 16:09:13 GMT

show us your search.

Re: Is there a way to add results to dbquery search when none are returned? Similar to 'fillnull' but for 'no results'.

ericrobinson — Tue, 29 Sep 2020 08:58:37 GMT

| dbquery DB limit=10000 "select * from Monitors where NOTIFY_GROUP like '%fimt-aps-aat%'" |search DISABLED!=Yes | search NOTIFY_GROUP!=support-group| where like(OBJECT_NAME, "%[AP%") | rex field=OBJECT_NAME "[(?AP\d+)]" |stats count by NOTIFY_GROUP | fillnull value=0 count| rename count AS app_id_count | appendcols [dbquery DB limit=10000 "select * from Monitors where NOTIFY_GROUP like '%support-group%'" |search DISABLED!=Yes | stats count by NOTIFY_GROUP | rename count AS monitor_count] | eval app_id_coverage_perc=round((app_id_count/monitor_count),2)*100| eval app_id_coverage=app_id_coverage_perc."%" | table app_id_count, monitor_count, app_id_coverage

The problem is that the first search returns no results for come of the support groups. I want to remove the no result and have it report as "0".

Re: Is there a way to add results to dbquery search when none are returned? Similar to 'fillnull' but for 'no results'.

woodcock — Thu, 03 Mar 2016 17:33:23 GMT

I think that one of these 2 approaches should work:

https://answers.splunk.com/answers/293823/how-to-table-list-of-values-from-lookup-not-found.html
https://answers.splunk.com/answers/321170/fill-in-0-if-no-result-is-returned.html

Re: Is there a way to add results to dbquery search when none are returned? Similar to 'fillnull' but for 'no results'.

sloshburch — Mon, 07 Mar 2016 17:58:11 GMT

Random performance questions:

You might be able to speed up your search by doing a DBQUERY that looks for either types of NOTIFY_GROUP and then use evals in the stats to count the individual items (like, stats count(eval(NOTIFY_GROUP=="*support-group*")) AS sg_count)
You can probably collapse some of the search and where commands into one: | search DISABLED!=Yes NOTIFY_GROUP!=support-group OBJECT_NAME="*[AP*"
rex is used but there is no capture group. Perhaps something was removed for posting in answers. If not, it'll save some cycles to remove that line since it doesn't do anything
Since only a few fields are used in the table at the end, you can probably collapse some of the final evals into one: | eval app_id_coverage=round((app_id_count/monitor_count),2)*100."%"

I realize you probably butchered the search to be able to post it so some of my questions might be derived from those changes.