Splunk Search

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

genesiusj
Builder

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

Labels (2)
0 Karma

genesiusj
Builder

@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

0 Karma

ITWhisperer
Ultra Champion

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

0 Karma

marycordova
SplunkTrust
SplunkTrust

I like to use mvzip:

 

| eval Name=mvzip('LastName','FirstName',", ")
| stats values(Name) as Name by Loc

 

That gives you something like:

BOSCollins, Phil
Lennon, John
CHISue, Peggy
NYCJones, 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.  

0 Karma

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

Not sure if that's what you meant

0 Karma
Take the 2021 Splunk Career Survey

Help us learn about how Splunk has
impacted your career by taking the 2021 Splunk Career Survey.

Earn $50 in Amazon cash!