How to transform row to column unique?

[sjkalai]

I am new to Splunk, search query and return table values , I want change below table into second table format. 

convert to table into below format. percentage calculation is sum of 0-5% - Q1 row value/ sum of column total. How can achieve this. please help me . Thanks in advance

[gcusello]

Hi @sjkalai,

you should use the chart command (https://docs.splunk.com/Documentation/Splunk/8.2.6/SearchReference/Chart)

Only one question: how does the Serial field is in the calculation?

for each usage_range and quarter, do you want the count of the values or the distinct values of Serials?

if the count, please try something like this:

your_search
| chart count OVER usage_range BY quarter

if distinct_count of Serials

your_search
| chart dc(Serial) OVER usage_range BY quarter

Ciao.

Giuseppe

[sjkalai]

Thank you for answer. @gcusello  I am already used chart i got table. but I want add a percentage calculation (Serial count *100 )/ sum (column -Q1) total. I want like this Serial count 10 (50%) is in column total 20. Ex (10*100)/20 = 50 %

[gcusello]

Hi @sjkalai,

you have to use a more complicated search that I cannot test, see my approach and adapt to your need:

your_search
| stats count BY usage_range quarter
| evenstats sum(count) AS quarter_total BY quarter
| eval perc=count*100/quarter_total 
| eval value_to_display=count." (".perc."%)"
| chart values(value_to_display) AS value_to_display OVER usage_range BY quarter

Ciao.

Giuseppe

[sjkalai]

Hi @gcusello ,

Thanks its work. But I cannot add a column total in the last row because chart values are string. so we cannot use addtotals or addcoltotals commend.
 

[gcusello]

Hi @sjkalai,

you could try to use the append command at the end of your search:

your_search
| stats count BY usage_range quarter
| evenstats sum(count) AS quarter_total BY quarter
| eval perc=count*100/quarter_total 
| eval value_to_display=count." (".perc."%)"
| chart values(value_to_display) AS value_to_display OVER usage_range BY quarter
| append [ search your_search | evenstats sum(count) AS quarter_total BY quarter ]

If this answer solves your need, please accept it for the other people of Community.

Ciao.

Giuseppe

P.S. Karma Points are appreciated 😉