Splunk Search

how to get count numbers based on the values

jenniferhao
Explorer

I have the following fields: x, value, I want to get number that count by value of x.
for example : 267 is the smallest value of 101L1, then the number should be 1, the next one 268 is 2...

x value number
101L1 267 1
101L1 268 2
101L1 269 3
101L1 270 4
101L1 271 5
101L1 272 6
101L1 273 7
101L1 274 8
101L1 275 9
101L1 276 10
101L1 277 11
101L1 278 12
101L1 279 13
101L1 280 14
101L1 281 15

101L2       28     1
101L2       29     2
101L2       30     3
101L2       31     4
101L2       32     5

101L3       27.3    1
101L3       27.4    2
101L3       27.5    3
101L3       27.6    4
101L3       27.7    5
101L3       27.8    6
Tags (2)
0 Karma
1 Solution

woodcock
Esteemed Legend

Assuming that you also have to properly account for "ties", then this:

| makeresults
| eval _raw="x,value
101L1,267
101L1,268
101L1,269
101L1,270
101L1,271
101L1,272
101L1,273
101L1,274
101L1,275
101L1,276
101L1,277
101L1,278
101L1,279
101L1,280
101L1,281
101L1,267
101L1,268
101L1,269
101L1,270
101L1,271
101L1,272
101L2,28
101L2,29
101L2,30
101L2,31
101L2,32
101L3,27.3
101L3,27.4
101L3,27.5
101L3,27.6
101L3,27.7
101L3,27.8"
| multikv forceheader=1
| table x value

| rename COMMENT AS "Everything above generates sample event data; everything below is your solution"

| sort 0 - value
| streamstats count AS number BY x
| eventstats min(number) AS number BY x value

View solution in original post

0 Karma

jenniferhao
Explorer

Thanks, it works for me.

0 Karma

woodcock
Esteemed Legend

Assuming that you also have to properly account for "ties", then this:

| makeresults
| eval _raw="x,value
101L1,267
101L1,268
101L1,269
101L1,270
101L1,271
101L1,272
101L1,273
101L1,274
101L1,275
101L1,276
101L1,277
101L1,278
101L1,279
101L1,280
101L1,281
101L1,267
101L1,268
101L1,269
101L1,270
101L1,271
101L1,272
101L2,28
101L2,29
101L2,30
101L2,31
101L2,32
101L3,27.3
101L3,27.4
101L3,27.5
101L3,27.6
101L3,27.7
101L3,27.8"
| multikv forceheader=1
| table x value

| rename COMMENT AS "Everything above generates sample event data; everything below is your solution"

| sort 0 - value
| streamstats count AS number BY x
| eventstats min(number) AS number BY x value
0 Karma

to4kawa
Ultra Champion
| makeresults count=2
| streamstats count
| eval _time = if (count==2,relative_time(_time,"@m"), relative_time(_time,"-1h@m")) 
| makecontinuous span=1m
| streamstats count
| eval x="101L".(count % 3 + 1), value=random() % 30 + 210
| table x value
`comment("this is sample data")`
| sort 0 value
| streamstats count by x
| sort 0 x count

Hi, @jenniferhao @vnravikumar
reset_on_change=true is not required.
Considering the case of disparity, I tried to count after sorting.

0 Karma

vnravikumar
Champion

Hi

Try with

... |streamstats count by x reset_on_change=true|table x value count
0 Karma
Get Updates on the Splunk Community!

Enterprise Security Content Update (ESCU) | New Releases

In November, the Splunk Threat Research Team had one release of new security content via the Enterprise ...

Index This | Divide 100 by half. What do you get?

November 2024 Edition Hayyy Splunk Education Enthusiasts and the Eternally Curious!  We’re back with this ...

Stay Connected: Your Guide to December Tech Talks, Office Hours, and Webinars!

❄️ Celebrate the season with our December lineup of Community Office Hours, Tech Talks, and Webinars! ...