Splunk Search

split columns based on delimeter

Communicator

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.

Tags (3)
0 Karma

Motivator

@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 category1 up to category_10), if you want more add more in the query. Replace first two lines with your existing query.

Hope this helps!!!

0 Karma

Motivator

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

0 Karma

SplunkTrust
SplunkTrust

@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

0 Karma