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

Nextbeat
Path Finder

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

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

Tags (3)
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
SplunkTrust
SplunkTrust

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.  

@marycordova
0 Karma

ITWhisperer
SplunkTrust
SplunkTrust
| 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
Get Updates on the Splunk Community!

What's new in Splunk Cloud Platform 9.1.2312?

Hi Splunky people! We are excited to share the newest updates in Splunk Cloud Platform 9.1.2312! Analysts can ...

What’s New in Splunk Security Essentials 3.8.0?

Splunk Security Essentials (SSE) is an app that can amplify the power of your existing Splunk Cloud Platform, ...

Let’s Get You Certified – Vegas-Style at .conf24

Are you ready to level up your Splunk game? Then, let’s get you certified live at .conf24 – our annual user ...