Dashboards & Visualizations

Transpose without aggregate column

matimat
Explorer

Hi,

I want to show the elapsed time of each event return by my query .

The elapsed time is on field name execTime, the event name on field Title.

I used a bar chart stacked to show the result.

My query is : 

index=blabla | table title, execTime | transpose 0 header_field=title include_empty=true

The issue is transpose command aggregate all title with the same value. I won't that.

Before transpose I have this : 

TitleDuration
T12
T25
T31
T26
T412

 

After transpose I have this (T2 is agregate with sum) : 

T1T3T2T4
211112

 

But I want this : 

T1T2T3T2T4
251612

 

Regards

Labels (3)
0 Karma
1 Solution

niketn
Legend

[UPDATED ANSWER]

@matimat Since you do not need to change the order of sorting of results. You may have use different streamstats. Try the following which will not require sorting (I have included by clause in streamstats to detect a change in Title):

index=blabla
| tableTitle,Duration
| streamstats count as padSpace by Title
| eval Title=replace(mvjoin(mvrange(1,padSpace+1,1)," "),"\d","")."".Title
| fields Title Duration
| transpose 0 header_field=title include_empty=true

Without Sorting

| makeresults 
| fields - _time 
| eval data="T1,2;T2,5;T3,1;T2,6;T4,12" 
| makemv data delim=";" 
| mvexpand data 
| makemv data delim="," 
| eval Title=mvindex(data,0),Duration=mvindex(data,1) 
| fields - _data 
| table Title Duration 
| streamstats count as padSpace by Title
| eval Title=replace(mvjoin(mvrange(1,padSpace+1,1)," "),"\d","")."".Title
| fields Title Duration
| transpose 0 header_field=Title column_name=Title

 

____________________________________________________________

With Sorting

The above search sorts title and count of repetition of the same title using streamstats.
The eval prefixes space character for each repetition of Title so that although Table Title look the same they are actually prefixed with one of more spaces depending on repetition.

Please try out and confirm. Following is a run anywhere example as per the data/field in your question:

 

| makeresults 
| fields - _time 
| eval data="T1,2;T2,5;T3,1;T2,6;T4,12" 
| makemv data delim=";" 
| mvexpand data 
| makemv data delim="," 
| eval Title=mvindex(data,0),Duration=mvindex(data,1) 
| fields - _data 
| table Title Duration 
| sort Title 
| streamstats count as padSpace by Title reset_on_change=true
| eval Title=replace(mvjoin(mvrange(1,padSpace+1,1)," "),"\d","")."".Title
| fields - padSpace
| transpose 0 header_field=Title column_name=Title

 

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

View solution in original post

thambisetty
SplunkTrust
SplunkTrust

@niketn  may be you can remove sort to give expected result.

————————————
If this helps, give a like below.
0 Karma

niketn
Legend

@thambisetty streamstats works in streaming manner, it requires sorting to be in place for reset_on_change to work.

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

thambisetty
SplunkTrust
SplunkTrust

@niketn 
even by clause is not required. 🙂

| makeresults | eval _raw="Title,Duration
T1,2
T2,5
T3,1
T2,6
T4,12"
| multikv forceheader=1
| table Title,Duration

| streamstats count as padSpace
| eval Title=replace(mvjoin(mvrange(1,padSpace+1,1)," "),"\d","")."".Title
| fields - padSpace
| transpose 0 header_field=Title column_name=Title

————————————
If this helps, give a like below.

niketn
Legend

@thambisetty this will end up padding all rows endlessly. If you have 100 rows... 100th row will have 100 spaces padded.

My Solution pads space only if there is a duplicate. If the same title repeats 3 times it pads three spaces. But if there are 100 rows with 99 rows and 1 duplicate your solution will pad each column with an incremental space.

I replaced space " " with hyphen "-", and you can see for yourself what your query is doing vs. mine 😉

 

| makeresults count=100
| eval Title="T"
| streamstats count as sno
| eval Title=Title.sno
| eval Duration=substr(tostring(random()),1,2)
| table Title,Duration 
| streamstats count as padSpace 
| eval Title=replace(mvjoin(mvrange(1,padSpace+1,1),"-"),"\d","")."".Title 
| fields - padSpace 
| transpose 0 header_field=Title column_name=Title

 

 

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

niketn
Legend

@thambisetty this will end up padding all rows endlessly. If you have 100 rows... 100th row will have 100 spaces padded.

My Solution pads space only if there is a duplicate. If the same title repeats 3 times it pads three spaces. But if there are 100 rows with 99 rows and 1 duplicate your solution will pad each column with an incremental space.

I replaced space " " with hyphen "-", and you can see for yourself what your query is doing vs. mine 😉

 

| makeresults count=100
| eval Title="T"
| streamstats count as sno
| eval Title=Title.sno
| eval Duration=substr(tostring(random()),1,2)
| table Title,Duration 
| streamstats count as padSpace 
| eval Title=replace(mvjoin(mvrange(1,padSpace+1,1),"-"),"\d","")."".Title 
| fields - padSpace 
| transpose 0 header_field=Title column_name=Title

 

Here is what my query will do for same scenario:

| makeresults count=100
| eval Title="T"
| streamstats count as sno
| eval Title=Title.sno
| eval Duration=substr(tostring(random()),1,2)
| table Title Duration 
| sort Title 
| streamstats count as padSpace by Title reset_on_change=true
| eval Title=replace(mvjoin(mvrange(1,padSpace+1,1),"-"),"\d","")."".Title
| fields - padSpace
| transpose 0 header_field=Title column_name=Title
____________________________________________
| makeresults | eval message= "Happy Splunking!!!"

niketn
Legend

[UPDATED ANSWER]

@matimat Since you do not need to change the order of sorting of results. You may have use different streamstats. Try the following which will not require sorting (I have included by clause in streamstats to detect a change in Title):

index=blabla
| tableTitle,Duration
| streamstats count as padSpace by Title
| eval Title=replace(mvjoin(mvrange(1,padSpace+1,1)," "),"\d","")."".Title
| fields Title Duration
| transpose 0 header_field=title include_empty=true

Without Sorting

| makeresults 
| fields - _time 
| eval data="T1,2;T2,5;T3,1;T2,6;T4,12" 
| makemv data delim=";" 
| mvexpand data 
| makemv data delim="," 
| eval Title=mvindex(data,0),Duration=mvindex(data,1) 
| fields - _data 
| table Title Duration 
| streamstats count as padSpace by Title
| eval Title=replace(mvjoin(mvrange(1,padSpace+1,1)," "),"\d","")."".Title
| fields Title Duration
| transpose 0 header_field=Title column_name=Title

 

____________________________________________________________

With Sorting

The above search sorts title and count of repetition of the same title using streamstats.
The eval prefixes space character for each repetition of Title so that although Table Title look the same they are actually prefixed with one of more spaces depending on repetition.

Please try out and confirm. Following is a run anywhere example as per the data/field in your question:

 

| makeresults 
| fields - _time 
| eval data="T1,2;T2,5;T3,1;T2,6;T4,12" 
| makemv data delim=";" 
| mvexpand data 
| makemv data delim="," 
| eval Title=mvindex(data,0),Duration=mvindex(data,1) 
| fields - _data 
| table Title Duration 
| sort Title 
| streamstats count as padSpace by Title reset_on_change=true
| eval Title=replace(mvjoin(mvrange(1,padSpace+1,1)," "),"\d","")."".Title
| fields - padSpace
| transpose 0 header_field=Title column_name=Title

 

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

matimat
Explorer

Thank you for your response, if I understand,  the solution is to change name identical with space.

My column must not be sort. For now only the solution of Karma match with my wish.

0 Karma

niketn
Legend

@matimat Try the updated answer without Sort if it fits your needs! 🙂

____________________________________________
| makeresults | eval message= "Happy Splunking!!!"
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 ...