Splunk Search

split columns based on delimeter

surekhasplunk
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

VatsalJagani
SplunkTrust
SplunkTrust

@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!!!

0 Karma

VatsalJagani
SplunkTrust
SplunkTrust

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

0 Karma

kamlesh_vaghela
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
Get Updates on the Splunk Community!

Index This | I am a number, but when you add ‘G’ to me, I go away. What number am I?

March 2024 Edition Hayyy Splunk Education Enthusiasts and the Eternally Curious!  We’re back with another ...

What’s New in Splunk App for PCI Compliance 5.3.1?

The Splunk App for PCI Compliance allows customers to extend the power of their existing Splunk solution with ...

Extending Observability Content to Splunk Cloud

Register to join us !   In this Extending Observability Content to Splunk Cloud Tech Talk, you'll see how to ...