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!

Splunk Answers Content Calendar, June Edition

Get ready for this week’s post dedicated to Splunk Dashboards! We're celebrating the power of community by ...

What You Read The Most: Splunk Lantern’s Most Popular Articles!

Splunk Lantern is a Splunk customer success center that provides advice from Splunk experts on valuable data ...

See your relevant APM services, dashboards, and alerts in one place with the updated ...

As a Splunk Observability user, you have a lot of data you have to manage, prioritize, and troubleshoot on a ...