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!

Splunk Training for All: Meet Aspiring Cybersecurity Analyst, Marc Alicea

Splunk Education believes in the value of training and certification in today’s rapidly-changing data-driven ...

The Splunk Success Framework: Your Guide to Successful Splunk Implementations

Splunk Lantern is a customer success center that provides advice from Splunk experts on valuable data ...

Investigate Security and Threat Detection with VirusTotal and Splunk Integration

As security threats and their complexities surge, security analysts deal with increased challenges and ...