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
Revered Legend

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!

.conf24 | Day 0

Hello Splunk Community! My name is Chris, and I'm based in Canberra, Australia's capital, and I travelled for ...

Enhance Security Visibility with Splunk Enterprise Security 7.1 through Threat ...

 (view in My Videos)Struggling with alert fatigue, lack of context, and prioritization around security ...

Troubleshooting the OpenTelemetry Collector

  In this tech talk, you’ll learn how to troubleshoot the OpenTelemetry collector - from checking the ...