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

[ashanka]

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

[to4kawa]

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?

[ashanka]

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

[cvsvineeth]

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

[dol_splunk_user]

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

| head 5