- Mark as New
- Bookmark Message
- Subscribe to Message
- Mute Message
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
Is there a way to keep row data together when using the stats command?
Hello,
Is there a way to keep row data together when using the stats command?
ID Loc FirstName LastName
1 NYC Tom Jones
2 CHI Peggy Sue
3 BOS Phil Collins
4 BOS John Lennon
5 NYC Paul McCartney
If I used `| stats values(FirstName), values(LastName) BY Loc` I believe I would get this.
BOS John Collins
Phil Lennon
CHI Peggy Sue
NYC Paul Jones
Tom McCartney
How do I keep FirstName and LastName together BY Loc?
This is a scaled-down example. I have more than 20 fields, and over 10,000 events.
Thanks in advance.
Stay safe and healthy, you and yours.
God bless,
Genesius
- Mark as New
- Bookmark Message
- Subscribe to Message
- Mute Message
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
- Mark as New
- Bookmark Message
- Subscribe to Message
- Mute Message
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
Thank you for your answers.
Your solutions would work if my event contained a few fields. However, the data includes 20+ fields [Loc, FN, LN, Address, City, State, Zip, Phone, ID, etc.]; and will be increasing to over 100 fields. If we mvappend all of these fields, this would be extremely inefficient, as well as make sorting/searching on a specific field(s) very cumbersome.
A colleague suggested using list instead of values: | stats list(FirstName), list(LastName) BY Loc. However, I don't believe Splunk would handle event data where a field was null or blank properly. It would not enter a blank line in the results table.
Thanks again and God bless,
Genesius
- Mark as New
- Bookmark Message
- Subscribe to Message
- Mute Message
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content

You are right about nulls/empty strings being omitted from the list so you could try using a space filler
| fillnull value=" " FirstName, LastName
| stats list(FirstName) as FirstName, list(LastName) as LastName by Loc
Note it can't be an empty string
- Mark as New
- Bookmark Message
- Subscribe to Message
- Mute Message
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content


I like to use mvzip:
| eval Name=mvzip('LastName','FirstName',", ")
| stats values(Name) as Name by Loc
That gives you something like:
BOS | Collins, Phil Lennon, John |
CHI | Sue, Peggy |
NYC | Jones, Tom McCartney, Paul |
I use this kind of thing with a DHCP lookup table I build so that the IP to NIC/MAC to hostname to timestamp relationship is preserved. Otherwise if you just did stats values IP values MAC by hostname you wouldn't know which IP went with which MAC address. And if you want to preserve a reference history you also need to link these assignments to the date/time they were assigned.
- Mark as New
- Bookmark Message
- Subscribe to Message
- Mute Message
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content

| eval joined=mvappend(FirstName, LastName)
| eval joined=mvjoin(joined, " ")
| stats values(joined) as Name BY Loc
Not sure if that's what you meant
