Transpose question

[emilep]

Hi,

I have a query like:

index=federated:ccs_rmail sourcetype="rmail:KIC:reports"
| dedup _time
| timechart span=1mon sum(cisco_*) as cisco_*
| addtotals
| eval rep_perc = round(cisco_stoppedbyreputation/Total*100,2),
spam_perc =round(cisco_spam/Total*100,2),
virus_perc=round(cisco_virus/Total*100,6)
| table cisco_stoppedbyreputation,rep_perc,cisco_spam,spam_perc,cisco_virus,virus_perc
| rename cisco_spam as spam, cisco_virus as virus,cisco_stoppedbyreputation as reputation
| transpose

The result look like:

column	row 1
reputation	740284221
rep_perc	82.46
spam	9695175
spam_perc	1.08
virus	700
virus_perc	0.000078

Is it possible to have something like this?

Name	#	%
reputation	740284221	82.46
spam	9695175	1.08
virus	700	0.000078

Thanks,
Emile

[ITWhisperer · Accepted solution]

| timechart span=1mon sum(cisco_*) as cisco_*
| rename cisco_* as *
| rename stoppedbyreputation as reputation
| untable _time name count
| fields - _time
| eventstats sum(count) as total
| eval percentage=round(100*count/total,2)
| fields - total

[emilep]

Hi @ITWhisperer ,
Here it seems that transpose was not the good approach.
Your solution is working as expected.
Many thanks,
Emile

[emilep]

The result without the transpose looks like:

reputation	rep_perc	spam	spam_perc	virus	virus_perc
740284221	82.46	9695175	1.08	700	0.000078

I would like to include this table in a glass table, but as it is formatted here it taking to much place.

[gcusello]

HI @emilep,

what's the resul without transpose?

did you read the command description at https://docs.splunk.com/Documentation/Splunk/9.1.1/SearchReference/Transpose ?

in addition, there's this useful link https://www.splunk.com/en_us/blog/customers/splunk-clara-fication-transpose-xyseries-untable-and-mor.... 

Ciao.

Giuseppe