Splunk Search

How to split data into multiple columns?

ayush1906
Path Finder

I am having data in a single field in this format:
1. xyz 2. dsh bh 3. sdh dsd() 4. trrt .... so on

I want to split this data into multiple column like this

no.  |    name
1           xyz
2          dsh bh
3          sdh dsd
4          trrt

I have tried using delimiter but not getting the expected result.

0 Karma
1 Solution

harsmarvania57
Ultra Champion

Hi @ayush1906,

Based on sample data you can try below query

<yourBaseSearch>
| rex field=_raw "(?<a>\d+)\.\s(?<b>[^\d]*)(?:$|\s)" max_match=0
| eval c=mvzip(a,b)
| mvexpand c
| eval no=mvindex(split(c,","),0), name=mvindex(split(c,","),1)
| rex field=name mode=sed "s/\(\)//g"
| table no, name

Here is run anywhere search based on sample data

| makeresults
| eval _raw="1. xyz 2. dsh bh 3. sdh dsd() 4. trrt"
| rex field=_raw "(?<a>\d+)\.\s(?<b>[^\d]*)(?:$|\s)" max_match=0
| eval c=mvzip(a,b)
| mvexpand c
| eval no=mvindex(split(c,","),0), name=mvindex(split(c,","),1)
| rex field=name mode=sed "s/\(\)//g"
| table no, name

View solution in original post

0 Karma

vnravikumar
Champion

Hi @ayush1906

Try like

| makeresults 
| eval msg="1. xyz 2. dsh bh 3. sdh dsd() 4. trrt" 
| rex field=msg max_match=0 "(?P<no>\d+).\s(?P<name>[a-z\s]*)"
0 Karma

harsmarvania57
Ultra Champion

Hi @ayush1906,

Based on sample data you can try below query

<yourBaseSearch>
| rex field=_raw "(?<a>\d+)\.\s(?<b>[^\d]*)(?:$|\s)" max_match=0
| eval c=mvzip(a,b)
| mvexpand c
| eval no=mvindex(split(c,","),0), name=mvindex(split(c,","),1)
| rex field=name mode=sed "s/\(\)//g"
| table no, name

Here is run anywhere search based on sample data

| makeresults
| eval _raw="1. xyz 2. dsh bh 3. sdh dsd() 4. trrt"
| rex field=_raw "(?<a>\d+)\.\s(?<b>[^\d]*)(?:$|\s)" max_match=0
| eval c=mvzip(a,b)
| mvexpand c
| eval no=mvindex(split(c,","),0), name=mvindex(split(c,","),1)
| rex field=name mode=sed "s/\(\)//g"
| table no, name
0 Karma
Get Updates on the Splunk Community!

Exciting News: The AppDynamics Community Joins Splunk!

Hello Splunkers,   I’d like to introduce myself—I’m Ryan, the former AppDynamics Community Manager, and I’m ...

The All New Performance Insights for Splunk

Splunk gives you amazing tools to analyze system data and make business-critical decisions, react to issues, ...

Good Sourcetype Naming

When it comes to getting data in, one of the earliest decisions made is what to use as a sourcetype. Often, ...