Dashboards & Visualizations

how to insert row on zero count and still use group by multiple fields

sjou20
New Member

this is a sample of what i need to generate (first group by appName, then range). (I already researched on rangemap and inputlookup but it didn't seem to solve my particular issue.)

appName | range | count
abc  |  1-60 | 5
abc  |  61-600 | 0
abc  |  601-600000 | 5
abc  |  600001-1600000 | 5
xyz  |  1-60 | 0
xyz  |  61-600 | 1
xyz  |  601-600000 | 15
xyz  |  600001-1600000 | 0

here's my search using rangemap and lookup csv. it sort of works but it's not grouping by appName

search  | rangemap field=waitTimeSec "1-60"=0-60 "61-600"=61-600 "601-6000000"=601-6000000, "6000001-1600000"=6000001-1600000 |top limit=0 range |inputlookup append=true ntfn-lookup.csv |stats max(count) as mycount by range | sort range

here's the result

range   mycount
1-60     4
61-600   7
601-6000000  14
6000001-1600000  0

here's my lookup.csv

range, count
1-60, 0
61-600, 0
601-6000000,0
6000001-1600000,0

I need it to group by appName first , so I tried

|stats max(count) as mycount by appName, range | sort range

but this returns no result at all. any pointers? thanks.

Tags (1)
0 Karma

somesoni2
SplunkTrust
SplunkTrust

Give this a try (no lookup required)

index="blah" ntfnRead appName | rangemap field=waitTimeSec "1-60"=0-60 "61-600"=61-600 "601-6000000"=601-6000000, "6000001-1600000"=6000001-1600000 | stats count(eval(range="1-60")) as "1-60" count(eval(range="61-600")) as "61-600" count(eval(range="601-6000000")) as "601-6000000" count(eval(range="6000001-1600000")) as "6000001-1600000" by appName |  untable appName range count
0 Karma

martin_mueller
SplunkTrust
SplunkTrust

Once you do top range you lose the appName field. Consider this:

search | rangemap ... | inputlookup ... | stats count by appName range | eval count = count - 1

Note, you'll need to add the appName field to your lookup as well. As it is now it won't work when grouped by both fields.

0 Karma

sjou20
New Member

changed query to:

index="blah" ntfnRead appName | rangemap field=waitTimeSec "1-60"=0-60 "61-600"=61-600 "601-6000000"=601-6000000, "6000001-1600000"=6000001-1600000 |inputlookup append=true ntfn-lookup.csv |stats count by appName range | eval count = count - 1

added appName to csv, but no luck. is my query above correct?

range, count, appName
1-60, 0
61-600, 0
601-6000000,0
6000001-1600000,0

the result:
appName range count
abc 601-6000000 0
abc 61-600 0
xyz 1-60 3
bbb 601-6000000 1

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