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 | Registration Open!

Hello, hello! I come bearing good news: Registration for .conf24 is now open!   conf is Splunk’s rad annual ...

ICYMI - Check out the latest releases of Splunk Edge Processor

Splunk is pleased to announce the latest enhancements to Splunk Edge Processor.  HEC Receiver authorization ...

Introducing the 2024 SplunkTrust!

Hello, Splunk Community! We are beyond thrilled to announce our newest group of SplunkTrust members!  The ...