Dashboards & Visualizations

splitting many rows in columns

fabrizioalleva
Path Finder

Hello,
I need to transform this table:

Fruits Euro
Apples 2
Banana 1
Strawberry 3
Ananas 3
Blueberry 4
Pear 2

into :
Fruits Euro Fruits Euro
apples 2 Banana 1
Strawb 3 Ananas 3
Bluebe 4 Pear 2

Is there a way ?

Thanks Fabrizio

Tags (1)
0 Karma
1 Solution

dmarling
Builder

You cannot have the same field name in a table have multiple columns when making a table in Splunk. You would need to do a hack to get it to work by appending a space to the name so it appears to be the same but is actually different:

| makeresults count=1
| fields - _time
| eval data="Apples 2
Banana 1
Strawberry 3
Ananas 3 
Blueberry 4
Pear 2"
| rex field=data max_match=0 "(?<data>[^\n\e]+)"
| eval data=trim(data)
| mvexpand data
| rex field=data "(?<Fruits>[^\s]+) (?<Euro>\d+)"
| table Fruits Euro
| streamstats reset_after="count=2" count
| streamstats values(Fruits) as joiner window=1 current=f
| eval "Fruits "=if(count=2, Fruits, null())
| eval "Euro "=if(count=2, Euro, null())
| eval joiner=if(count=1, Fruits, joiner)
| eval Fruits=if(count=1, Fruits, null())
| eval Euro=if(count=1, Euro, null())
| stats values(Fruits) as Fruits values(Euro) as Euro values("Fruits ") as "Fruits " values("Euro ") as "Euro " by joiner
| fields - joiner
If this comment/answer was helpful, please up vote it. Thank you.

View solution in original post

0 Karma

niketn
Legend

@fabrizioalleva what is the criteria for moving rows to column?

____________________________________________
| makeresults | eval message= "Happy Splunking!!!"
0 Karma

fabrizioalleva
Path Finder

@niketnilay : I have to order a small table with 12 lines, dividing them, for reasons of space in the dashboard, 6 in the first two columns and 6 in the second

0 Karma

dmarling
Builder

You cannot have the same field name in a table have multiple columns when making a table in Splunk. You would need to do a hack to get it to work by appending a space to the name so it appears to be the same but is actually different:

| makeresults count=1
| fields - _time
| eval data="Apples 2
Banana 1
Strawberry 3
Ananas 3 
Blueberry 4
Pear 2"
| rex field=data max_match=0 "(?<data>[^\n\e]+)"
| eval data=trim(data)
| mvexpand data
| rex field=data "(?<Fruits>[^\s]+) (?<Euro>\d+)"
| table Fruits Euro
| streamstats reset_after="count=2" count
| streamstats values(Fruits) as joiner window=1 current=f
| eval "Fruits "=if(count=2, Fruits, null())
| eval "Euro "=if(count=2, Euro, null())
| eval joiner=if(count=1, Fruits, joiner)
| eval Fruits=if(count=1, Fruits, null())
| eval Euro=if(count=1, Euro, null())
| stats values(Fruits) as Fruits values(Euro) as Euro values("Fruits ") as "Fruits " values("Euro ") as "Euro " by joiner
| fields - joiner
If this comment/answer was helpful, please up vote it. Thank you.
0 Karma

fabrizioalleva
Path Finder

PERFECT!!!!! I've to adjust it for my data!!
Thanks a lot

0 Karma

dmarling
Builder

Glad it worked for you. I'm converting my comment to an answer. If you could please accept it once it updates, I would appreciate it. Thank you!

If this comment/answer was helpful, please up vote it. Thank you.
0 Karma
Get Updates on the Splunk Community!

Discover Powerful New Features in Splunk Cloud Platform: Enhanced Analytics, ...

Hey Splunky people! We are excited to share the latest updates in Splunk Cloud Platform 9.3.2408. In this ...

Splunk Classroom Chronicles: Training Tales and Testimonials

Welcome to the "Splunk Classroom Chronicles" series, created to help curious, career-minded learners get ...

Access Tokens Page - New & Improved

Splunk Observability Cloud recently launched an improved design for the access tokens page for better ...