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

[sjou20]

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.

[gkanapathy]

http://answers.splunk.com/answers/62196/any-way-to-return-zero-result-count-stats-of-a-field-such-as...

and

http://answers.splunk.com/answers/6646/search-events-against-a-lookup-table-and-show-matching-count/...

address similar problems.

[somesoni2]

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

[martin_mueller]

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.

[sjou20]

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