Splunk Search

Dealing with 0 count items from lookup in a stats count

ft_kd02
Path Finder

Hi all,

I'm in the process of setting up performance reporting for services provided for a client. The logic in question is very simple: average response of the service and volume over a predetermined time period. I'm having trouble including services with 0 calls during the time period in question. It is important to the end user that they see which services are not being called as well. I've coded up two solutions and neither is giving me what I need. I've also looked at https://community.splunk.com/t5/Splunk-Search/Include-zero-count-items-from-lookup/m-p/177260, but I'm having trouble understanding how this applies to my solution outside of the outputlookup. 

The format of the table desired is: Service - Average Response - Volume.

Solution 1 - base search - works but does not include 0 values for obvious reasons:

index=********************************************
| lookup services.csv service AS liveDataField OUTPUT service
| stats avg(ResponseTime) AS AverageResponseTime, count AS Volume BY service
| eval AverageResponseTime=round((1000*AverageResponseTime), 2)
| fillnull value=0 AverageResponseTime



My first thought was to add in a lookup table with all the services, and build off of that:

index=********************************************
| inputlookup append=t services.csv
| lookup services.csv service AS liveDataField OUTPUT service
| stats avg(ResponseTime) AS AverageResponseTime, count AS Volume BY service
| eval AverageResponseTime=round((1000*AverageResponseTime), 2)
| fillnull value=0 AverageResponseTime



This gives me what I need in terms of zero values, but instead of returning a count of 0, it returns Volume=1 for each service with zero hits. It also increments each service's volume by one erroneously. I suspect this is due to the inputlookup append=t. I've also tried doing an eval count=0 initially. 

TL;DR - adding in a lookup to address zero count items caused each service's volume to be incremented by 1. Is there a quick fix for this? Or perhaps a better way of doing it altogether?

Cheers




Labels (2)
0 Karma

ft_kd02
Path Finder

Edit: This solution seems to work, but I'm still working out the reasoning behind it. 

index=**************************
| lookup services.csv service AS liveDataField OUTPUT service
| stats avg(ResponseTime) AS AverageResponseTime, count BY service
| inputlookup append=t services.csv
| fillnull value=0 count
| dedup service
| eval AverageResponseTime=round((1000*AverageResponseTime), 2)
| fillnull value=0 AverageResponseTime
| sort service

0 Karma
Get Updates on the Splunk Community!

New in Observability - Improvements to Custom Metrics SLOs, Log Observer Connect & ...

The latest enhancements to the Splunk observability portfolio deliver improved SLO management accuracy, better ...

Improve Data Pipelines Using Splunk Data Management

  Register Now   This Tech Talk will explore the pipeline management offerings Edge Processor and Ingest ...

3-2-1 Go! How Fast Can You Debug Microservices with Observability Cloud?

Register Join this Tech Talk to learn how unique features like Service Centric Views, Tag Spotlight, and ...