Splunk Search

How to fetch top 5 rows based on a value in table

ashanka
Explorer

index= aab sourcetype=topconnections earliest=-10m latest=-5m
| table SESSION_AUTH_ID , CONNECTION_COUNT
| addcoltotals labelfield=SESSION_AUTH_ID label=TotalCount

SESSION_AUTH_ID CONNECTION_COUNT
a 178
b 65
v 36
d 21
e 12
f 12
g 10
h 8
h 5
f 4
f 4
l 3
o 2
z 2
TotalCount 201

how do i get the top 5 rows from table when the total count is greater than 200.

when the total count reached 200 i need to get the top 5 SESSION_AUTH_ID and its CONNECTION_COUNT

Tags (1)
0 Karma

to4kawa
Ultra Champion

your sample check:

| makeresults
| eval _raw="SESSION_AUTH_ID,CONNECTION_COUNT
a,178
b,65
v,36
d,21
e,12
f,12
g,10
h,8
h,5
f,4
f,4
l,3
o,2
z,2"
| multikv forceheader=1
| table SESSION_AUTH_ID,CONNECTION_COUNT
| eventstats sum(CONNECTION_COUNT) as total
| stats list(*) as * by total
| eval tmp=mvzip(SESSION_AUTH_ID,CONNECTION_COUNT)
| eval result =if( total > 200, mvindex(tmp,0,4),"the total count is smaller than 200")
| table total result

recommend:

index= aab sourcetype=topconnections earliest=-10m latest=-5m
| table SESSION_AUTH_ID , CONNECTION_COUNT
| eventstats sum(CONNECTION_COUNT) as total
| stats list(*) as * by total
| eval tmp=mvzip(SESSION_AUTH_ID,CONNECTION_COUNT)
| eval result =if( total > 200, mvindex(tmp,0,4),"the total count is smaller than 200")
| table total result
| rex field=result "(?<SESSION_AUTH_ID>[^\,]+),(?<CONNECTION_COUNT>.+)"
| table total result SESSION_AUTH_ID CONNECTION_COUNT

Hi folks. how about this?

0 Karma

ashanka
Explorer

but that does not valdiate the total count..only of the count is above 200 i need head -5

0 Karma

cvsvineeth
Engager

Try this. Hope this is what you are looking for

index= aab sourcetype=topconnections earliest=-10m latest=-5m
| table SESSION_AUTH_ID , CONNECTION_COUNT
|eventstats sum(CONNECTION_COUNT) as TotalCount
| where TotalCount> 200
| sort - CONNECTION_COUNT
| head 5

0 Karma

dol_splunk_user
New Member

See if this works . Add the following to the end or your search query.

| head 5

0 Karma
Get Updates on the Splunk Community!

Index This | I am a number, but when you add ‘G’ to me, I go away. What number am I?

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

What’s New in Splunk App for PCI Compliance 5.3.1?

The Splunk App for PCI Compliance allows customers to extend the power of their existing Splunk solution with ...

Extending Observability Content to Splunk Cloud

Register to join us !   In this Extending Observability Content to Splunk Cloud Tech Talk, you'll see how to ...