topic Re: split columns based on delimeter in Splunk Search

split columns based on delimeter

surekhasplunk — Mon, 10 Jun 2019 10:51:32 GMT

Hi,

I have a field called categories. And the values look like below. Please help me with regex or a way to split them and add values to new columns.

General News
Instant Messaging, Web Phone
Business, Software/Hardware
Search Engines
Finance/Banking, General News, Search Engines

I want :

categories| category1 | category2 | category3
General News | General News
Instant Messaging, Web Phone | Instant Messaging | Web Phone
Finance/Banking, General News, Search Engines | Finance/Banking | General News | Search Engines

Basically delimeted with comma.

Re: split columns based on delimeter

kamlesh_vaghela — Mon, 10 Jun 2019 11:08:13 GMT

@surekhasplunk

Can you please try this?

YOUR_SEARCH | eval category1=mvindex(split(categories,","),0),category2=mvindex(split(categories,","),1), category3=mvindex(split(categories,","),2)

Sample Search:

| makeresults 
| eval categories="General News|Instant Messaging, Web Phone|Business, Software/Hardware|Search Engines|Finance/Banking, General News, Search Engines" 
| eval categories=split(categories,"|") 
| mvexpand categories 
| eval category1=mvindex(split(categories,","),0),category2=mvindex(split(categories,","),1), category3=mvindex(split(categories,","),2)

Thanks

Re: split columns based on delimeter

VatsalJagani — Wed, 30 Sep 2020 00:50:26 GMT

@surekhasplunk,

Please try below search:

| makeresults 
| eval categories="Finance/Banking, General News, Search Engines" 
| makemv categories delim=", " 
| eval order="1,2,3,4,5,6,7,8,9,10" 
| makemv order delim="," 
| eval cat=mvzip(order,categories)
| mvexpand cat
| rex field=cat "^(?<order>\d+),(?<cat>.*)"
| eval category_{order} = cat
| fields - order,cat
| stats values(*) as * by _time

In your search fields other than categories, put all in place of _time in this query. This search can accept categories value upto 10 (meaning you can have category_1 up to category_10), if you want more add more in the query. Replace first two lines with your existing query.

Hope this helps!!!

Re: split columns based on delimeter

VatsalJagani — Mon, 10 Jun 2019 12:04:55 GMT

Please checkout answer by @kamlesh_vaghela where he used mvindex command. Check whichever is faster use that.