Splunk Search

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

ericrobinson
Path Finder

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.

Tags (1)
0 Karma

woodcock
Esteemed Legend
0 Karma

woodcock
Esteemed Legend

show us your search.

0 Karma

ericrobinson
Path Finder

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

0 Karma

sloshburch
Splunk Employee
Splunk Employee

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.

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