Why are new events resulting from mvexpand picking up special characters when exporting to CSV and how to avoid this?

[pjb2160]

This is a strange one, I have a data source which has multiple values in two separate fields so I use the makemv and then mvexpand commands which works well, and as expected, when rendered in Splunk. Here's my code:

index=main sourcetype=approvals
| makemv delim=";" groups | makemv delim=";" users | mvexpand groups | mvexpand users
| table groups admin users action _raw

I can see each new event and the relevant value from the mvexpand looks to only include the characters I expect (e.g. no special characters) when rendered in Splunk.

The problem is when I export the results to a .csv file. The new events resulting from the mvexpand command pick up some special characters. I can see this when I view the .csv in an editor such as Notepad++.

I've mocked up an example here showing only two events. I've used [LF], [CR] and [Tab] to represent the special characters (line feed, carriage return and tab):

"groups","admin","users",action,"_raw" [LF]
"GR-group-1",admin_1,user_1,Add,"2015-02-02T22:40:05.000 [LF]
[Tab] GROUPS=GR-group-1; [LF]
GR-group-2; [LF]
ACTION=Add [LF]
USERS=user_1 [LF]
ADMIN=admin_1" [CR][LF]
" [LF]
[Tab] GR-group-2;",admin_1,user_1,Add,"2015-02-02T22:40:06.000 [LF]
GROUPS=GR-group-1; [LF]
[Tab] GR-group-2; [LF]
ACTION=Add [LF]
USERS=user_1 [LF]
ADMIN=admin_1" [CR][LF]

Each line ends with a [LF] and the event, after the closing quotation for _raw ends in a [CR][LF].

The issue is the beginning of the second event (highlighted in red). I do not want to include the special characters in the .csv export.

Does my question make sense? I would welcome the opportunity to discuss further and would even more so welcome someone showing me I have just over looked the simplest of solutions!!! (simple is good)

Many thanks,
Paul

[pjb2160 · Accepted solution]

Success, I have used a regex to strip out what I don't want:

index=main sourcetype=approvals
 | makemv delim=";" groups | makemv delim=";" users | mvexpand groups | mvexpand users
 | rex field=groups "\s+(?<groups>.*)"
 | table groups admin users action _raw

Woo!