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!

Index This | I am a number, but when you add ‘G’ to me, I go away. What number am I?

March 2024 Edition Hayyy Splunk Education Enthusiasts and the Eternally Curious!  We’re back with another ...

What’s New in Splunk App for PCI Compliance 5.3.1?

The Splunk App for PCI Compliance allows customers to extend the power of their existing Splunk solution with ...

Extending Observability Content to Splunk Cloud

Register to join us !   In this Extending Observability Content to Splunk Cloud Tech Talk, you'll see how to ...