topic How to transform row to column unique? in Dashboards & Visualizations

How to transform row to column unique?

sjkalai — Tue, 07 Jun 2022 13:14:39 GMT

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

Re: How to transform row to column unique?

gcusello — Tue, 07 Jun 2022 06:29:48 GMT

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

Re: How to transform row to column unique?

sjkalai — Tue, 07 Jun 2022 07:13:23 GMT

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 %

Re: How to transform row to column unique?

gcusello — Tue, 07 Jun 2022 07:28:49 GMT

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

Re: How to transform row to column unique?

sjkalai — Tue, 07 Jun 2022 12:38:45 GMT

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.

Re: How to transform row to column unique?

gcusello — Tue, 07 Jun 2022 12:50:07 GMT

Hi @sjkalai,

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

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

Ciao.

Giuseppe

P.S. Karma Points are appreciated 😉