Is there a way to keep row data together when using the stats command?

[genesiusj]

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

[Nextbeat]

When using stats, rather than using values, use list for each field instead:

| stats list(FirstName), list(LastName) by Loc

[genesiusj]

@marycordova and @ITWhisperer 

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

[ITWhisperer]

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

[marycordova]

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.  

[ITWhisperer]

| eval joined=mvappend(FirstName, LastName)
| eval joined=mvjoin(joined, " ")
| stats values(joined) as Name BY Loc

Not sure if that's what you meant