Formatting in CSV when using Stats

[NicholasC]

I'm using stats to group sets of data by IP C blocks.
When I export the data I am looking for( in this case multiple user ID's per C) is formatted by a space per unique user id rather than a carriage return.

When viewing the results prior to export in the web client the returned values are returned one per line, however when exporting its all values on one line separated by a space.

Is there any way to separate values with a return rathern than a space?

IE i see the following in the web client:

values(UserId)
###
###
###
###

however when exported into a csv i see(when opening with excel)

values(UserId)
### ### ### ###

I would prefer to have one result per line else i will need to post process the results and replace the spaces with returns.

Edit for clarification:

I am using quite a few additional columns in the CSV export, multiple values can occur in each additional column and ideally each event would display the corresponding values for each column.

The results i get back look like (only using the first 6 due to formatting constraints) the following except every unique value for each Cblock is listed one after another on the same line, eg multiple email addresses and userid's per line separated by a space:

Cblock   values(UserId)   values(Email)    values(SignupIP)    Values(IPCountry)   values( PreferredCulture)
1.2.3    #### ####...    people@fqd.com... 1.2.3.4 1.2.3.5...  US                  en-us

What i would hope to get back is:

Cblock   values(UserId)   values(Email)    values(SignupIP)    Values(IPCountry)   values( PreferredCulture)
1.2.3    ####             people@fqd.com   1.2.3.4             US                  en-us
1.2.3    ####             people1@fqd.com  1.2.3.5             US                  en-us
1.2.3    ####             people4@fqd.com  1.2.3.7             US                  en-us
1.6.4    ####             man@fqd1.com     1.6.4.2             PK                  en-us
1.6.4    ####             bear@fqd1.com    1.6.4.6             PK                  en-us
1.6.4    ####             dog@fqd1.com     1.6.4.8             PK                  en-us

Sample of the log i am working with:

2012-01-31 09:58:00,112  Log="foo"
SignupFeature = BAR
Message = Welcome Email sent
UserId = ####
Email = people@domain.com
FLName = "firstname.lastname"
SignupIP = ###.###.###.###
IPCountry = US
PreferredCulture = en-us
Referer = https://domain.com

And the search i'm using to pull the data out (additional values are used but are removed for the example)

SignupFeature="BAR" | rex field=SignupIP "(?<Cblock>\d+\.\d+\.\d+)" | stats values(UserId) values(Email) values(SignupIP) values(IPCountry) values(PreferredCulture) values(Referer) count(Cblock) AS Blocks BY Cblock | where Blocks>=2

The overall basic idea is to to group events by /24, gather all fields for all grouped events and export the matching grouped events with all fields in a format that lists one event per line

Lastly here is a sample of the formatted results i am seeing in the web client.

[woodcock]

There is late-breaking news on this front:
https://yourprodismy.dev/splunk/2019/06/26/splunk_mv_lookups.html

The bottom line, though, is that by default, Splunk calls | foreach * _* [nomv <<FIELD>>] whenever you do outputlookup or outputcsv to a file, so that you only have single-value fields. If you output to a KV store, multi-value fields are preserved.
What most people would prefer, and what you can do is to add this:

... | foreach Your List of Multi-valued Fields Here [eval <<FIELD>>=mvdedup(<<FIELD>>) | mvexpand <<FIELD>>]

[richardcbristol]

I have a working solution! Only 8 years late but hopefully might help someone... the issue is with stats and values. This works:

dedup IMSI | table IMSI

Replace IMSI with whatever your field is. Should handle multiple fields. And the resulting CSV has one entry per line as god intended.

[indiechixor]

Undervalued comment of last year. Thanks!

[richardcbristol]

I also tried the rex solution above. But still get my output csv file looking like this (same as without it):

values(IMSI)
234301300000115 234301300000726 234301300000809 234301300001152 234301300001211 234301300001290 234301300001629 234301300001733 234301300001747

All on one line, no newlines between records. Useless as a csv file therefore. Probably something to do with using values and only one item output per line? not sure...

[richardcbristol]

I've just run across this problem when trying to output a list of unique values from a set of log files. The csv for some reason by default has 3 values per line in the resulting output! Bit silly. For a csv separate values by commas, newline for next record. Standard! I would suggest this is a bug with Splunk and been there for over 5 years! Surprised... Anyway I've just tried the work around of appending a newline after each value as suggested by martinaire above... but that doesn't work for me. My emailed report just has \n's after each value. I'm at the moment stuck at running the thing manually myself and copy and pasting the output rows.

Splunk output in web browser looks like this:

Values(IMSI)
234301300000115
234301300000726
234301300001152
234301300001211
234301300001290
234301300001629
234301300001733
etc.

But in csv file emailed to me I have this:

values(IMSI)
234301300000115\n 234301300000726\n 234301300001152\n 234301300001211\n 234301300001290\n 234301300001629\n 234301300001733\n 234301300001747\n 234301300001823\n 234301300001851\n 234301300001944\n 234301300002254\n

Hopefully there's a better way to do it? Will look at the other suggestions above

[richardcbristol]

Appreciate this is old but I found it when looking for an answer to my problem... may help someone? Incidentally my solution maxxed out at 10k rows. Believe that to be amendable in config but i havent access to do that myself on the installation I'm reporting from.

[richgalloway]

@richardcbristol This thread is more than six years old so it's not likely anyone will see your response. You should post a new question describing your problem.

[rsxrox]

I know this post is old. But, i've started to play around with splunk recently. Below seemed liked a better solution (v 6.4.1)

|rex mode=sed field= UserId "s/\s+/\n/g" 
|rex mode=sed field=Email "s/\s+/\n/g" 
|rex mode=sed field=SignupIP "s/\s+/\n/g"

This seemed like a better solution for me

[martinaire]

Option #1

Add the following in front of STATS:

| eval UserId=UserId."
" | eval Email=Email."
" | eval SignupIP=SignupIP."
" |

There is a new line between each set of quotation marks. Use SHIFT+ENTER to insert the new line into the search field. This concatenates (appends) a new line at the end of each of the values. I tested it and noticed inconsistent results (some values disappeared). But when opened in excel, each value in the excel cell is separated by a newline as opposed to just a space. So it works but make sure to validate it for accuracy.

Option #2

Install Splunk for Excel Export. I have not tested this but it may work.

[NicholasC]

The solution I ended up going with is to just copy and paste the data out of the web UI. If I have the results emailed from a scheduled search, the body of the email looks to be formatted correctly but this is due to the column widths forcing the values to wrap to a new line. Even in the email they are separated by a space and not a return.

[NicholasC]

I updated the original question to help clarify and provide more exacting examples. The primary reason I went with stats and used values was to be able to group data then report out the values I wanted in a grouped format.

[gkanapathy]

Also, I pretty well try to avoid using values() in most stats commands (most of the time, I believe they are misused)and it seems to me that this might be a case where you should also. Tell us a bit about how you're generating this data in the first place, and maybe you should be generating them in separate rows in the first place?

[gkanapathy]

Are there any other columns in your CSV export? How do you want to handle that? If the other columns are single-valued fields, how do you want them associated with the multiple values in this column? Do they get mapped to each value, or is each just a different one, and all you want is to somehow format your data differently.