Splunk Search

how to maintain order in stats command

RSS_STT
Explorer

Fields value of 2nd and 3rd events are enter changing. please suggest how to maintain order in Splunk status command. I can't use any other fields in stats by clause than token_id.

 

Sample Event:

|makeresults
|eval token_id="c75136c4-bdbc-439b"|eval doc_no="GSSAGGOS_QA-2931"|eval key=2931|eval keyword="DK-BAL-AP-00613"
|append [| makeresults |eval token_id="c75136c4-bdbc-439b"|eval doc_no="GSSAGGOS_QA-2932"|eval key=2932|eval keyword="DK-Z13-SW-00002"]
|append [| makeresults |eval token_id="c75136c4-bdbc-439b"|eval doc_no="GSSAGGOS_QA-2933"|eval key=2933|eval keyword="DK-BAL-AP-00847"]
| stats values(key) as key values(keyword) as keyword values(doc_no) as doc_no by token_id
| eval row=mvrange(0,mvcount(doc_no))| mvexpand row| foreach doc_no keyword key
[| eval <<FIELD>>=mvindex(<<FIELD>>,row)]|fields - row

Search Result output
toke_idkeykeyworddoc_no
c75136c4-bdbc-439b2931DK-BAL-AP-00613GSSAGGOS_QA-2931
c75136c4-bdbc-439b2932DK-BAL-AP-00847GSSAGGOS_QA-2932
c75136c4-bdbc-439b2933DK-Z13-SW-00002GSSAGGOS_QA-2933
    
Expected Output
toke_idkeykeyworddoc_no
c75136c4-bdbc-439b2931DK-BAL-AP-00613GSSAGGOS_QA-2931
c75136c4-bdbc-439b2932DK-Z13-SW-00002GSSAGGOS_QA-2932
c75136c4-bdbc-439b2933DK-BAL-AP-00847GSSAGGOS_QA-2933
Labels (1)
Tags (1)
0 Karma

ITWhisperer
SplunkTrust
SplunkTrust

values() sorts (and dedups) - use the list() function (which neither sorts nor dedups)

|makeresults
|eval token_id="c75136c4-bdbc-439b"|eval doc_no="GSSAGGOS_QA-2931"|eval key=2931|eval keyword="DK-BAL-AP-00613"
|append [| makeresults |eval token_id="c75136c4-bdbc-439b"|eval doc_no="GSSAGGOS_QA-2932"|eval key=2932|eval keyword="DK-Z13-SW-00002"]
|append [| makeresults |eval token_id="c75136c4-bdbc-439b"|eval doc_no="GSSAGGOS_QA-2933"|eval key=2933|eval keyword="DK-BAL-AP-00847"]
| stats list(key) as key list(keyword) as keyword list(doc_no) as doc_no by token_id
| eval row=mvrange(0,mvcount(doc_no))| mvexpand row| foreach doc_no keyword key
[| eval <<FIELD>>=mvindex(<<FIELD>>,row)]|fields - row

 

0 Karma

richgalloway
SplunkTrust
SplunkTrust

The output of the values and list functions are always in lexicographical order.  That destroys any relationship that might exist between/among fields.

The solution is to combine related fields into a single field before stats and then break them apart again afterwards.

| eval tuple = mvzip(keyword, doc_no)
| stats values(tuple) as tuple by token_id
| eval pairs = split(tuple, ",")
| eval keyword = mvindex(pairs,0), doc_no = mvindex(pairs, 1)
| fields - tuple, pairs

 

---
If this reply helps you, Karma would be appreciated.
0 Karma

RSS_STT
Explorer

split function proving error.

0 Karma

richgalloway
SplunkTrust
SplunkTrust

@RSS_STT wrote:

split function proving error.


I'm not sure what to make of that, but take it you get an (undescribed) error with the code I provided.  I found a missing argument so please try the revised SPL.

---
If this reply helps you, Karma would be appreciated.
0 Karma
Get Updates on the Splunk Community!

What You Read The Most: Splunk Lantern’s Most Popular Articles!

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

See your relevant APM services, dashboards, and alerts in one place with the updated ...

As a Splunk Observability user, you have a lot of data you have to manage, prioritize, and troubleshoot on a ...

Index This | What goes away as soon as you talk about it?

May 2025 Edition Hayyy Splunk Education Enthusiasts and the Eternally Curious!  We’re back with this month’s ...