topic Re: stats and event order in Splunk Search

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

genesiusj — Mon, 14 Sep 2020 18:53:54 GMT

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

Re: stats and event order

ITWhisperer — Fri, 11 Sep 2020 21:46:45 GMT

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

Not sure if that's what you meant

Re: stats and event order

marycordova — Sat, 12 Sep 2020 03:45:32 GMT

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.

Re: stats and event order

genesiusj — Mon, 14 Sep 2020 14:03:24 GMT

@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

Re: stats and event order

ITWhisperer — Wed, 16 Sep 2020 12:15:36 GMT

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

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

Nextbeat — Thu, 22 Feb 2024 20:01:28 GMT

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

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