Splunk Enterprise

How to sort data in stats command output

Nraj87
Explorer

Dear All,

Kindly suggest , How to sort data in stats command output as per event time .

Example: 
Requirement : VPN login details as per source user In last one hour.

SPL Query :
index="network" sourcetype=vpn
| eval "Login_Time" = strftime(_time, "%m/%d/%Y %I:%M:%S %p")
| stats values(SourceUser)as User  values(Login_Time) as VPN_Login_Time count by _time host

DateHostUserVPN Login TimeCount
1/7/2025 0:0010.10.8.45Amar
Rajesh
Zainab
01/07/2025 06:01:25 AM
01/7/2025  06:30:21 AM
01/7/2025  06:50:49 AM
3


challenge in above example output,  Amar was logged in at 01/7/2025  06:30:21AM and  Zainab was logged in at 01/07/2025 06:01:25 AM but in output result user were sorted with alphabetical order and Login time were  sorted in descending order.  And User field can not be added as third field in by expression .

Labels (1)
Tags (3)
0 Karma
1 Solution

bowesmana
SplunkTrust
SplunkTrust

If you want to end up with several multivalue fields that are correlated with each other, you can't use stats values() as the output from a values() aggregation is always in sorted order.

There are a number of options

1. Use stats list() which will record the item for EVERY event but the order is preserved, but of course if you have duplicates for the same user on the same _time, you will have multiple entries. Note that list() has a maximum list length of 100 items

2. Make a combination field of the items you want to end up with and use stats values(new_field) and then split them out again, e.g. like this

 

...
| eval _tmp=SourceUser."###".Login_Time
| stats values(_tmp) as _tmp count by _time host
| rex field=_tmp max_match=0 "(?<User>.*)###(?<VPN_Login_Time>.*)"
| fields - _tmp

 

3. Do this to handle the potential duplicate logins on the same _time for the same user

 

...
| stats values(Login_Time) as VPN_Login_Time count by _time host SourceUser
| stats list(*) as * sum(count) as count by _time host

 

so include the SourceUser initially then use stats list finally

Hope this helps

 

 

View solution in original post

bowesmana
SplunkTrust
SplunkTrust

If you want to end up with several multivalue fields that are correlated with each other, you can't use stats values() as the output from a values() aggregation is always in sorted order.

There are a number of options

1. Use stats list() which will record the item for EVERY event but the order is preserved, but of course if you have duplicates for the same user on the same _time, you will have multiple entries. Note that list() has a maximum list length of 100 items

2. Make a combination field of the items you want to end up with and use stats values(new_field) and then split them out again, e.g. like this

 

...
| eval _tmp=SourceUser."###".Login_Time
| stats values(_tmp) as _tmp count by _time host
| rex field=_tmp max_match=0 "(?<User>.*)###(?<VPN_Login_Time>.*)"
| fields - _tmp

 

3. Do this to handle the potential duplicate logins on the same _time for the same user

 

...
| stats values(Login_Time) as VPN_Login_Time count by _time host SourceUser
| stats list(*) as * sum(count) as count by _time host

 

so include the SourceUser initially then use stats list finally

Hope this helps

 

 

Get Updates on the Splunk Community!

Updated Data Type Articles, Anniversary Celebrations, and More on Splunk Lantern

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

A Prelude to .conf25: Your Guide to Splunk University

Heading to Boston this September for .conf25? Get a jumpstart by arriving a few days early for Splunk ...

4 Ways the Splunk Community Helps You Prepare for .conf25

.conf25 is right around the corner, and whether you’re a first-time attendee or a seasoned Splunker, the ...