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!

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 ...

Announcing the General Availability of Splunk Enterprise Security 8.1!

We are pleased to announce the general availability of Splunk Enterprise Security 8.1. Splunk becomes the only ...

Developer Spotlight with William Searle

The Splunk Guy: A Developer’s Path from Web to Cloud William is a Splunk Professional Services Consultant with ...