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!

Synthetic Monitoring: Not your Grandma’s Polyester! Tech Talk: DevOps Edition

Register today and join TekStream on Tuesday, February 28 at 11am PT/2pm ET for a demonstration of Splunk ...

Instrumenting Java Websocket Messaging

Instrumenting Java Websocket MessagingThis article is a code-based discussion of passing OpenTelemetry trace ...

Announcing General Availability of Splunk Incident Intelligence!

Digital transformation is real! Across industries, companies big and small are going through rapid digital ...