Archive

How to transpose some rows to columns?

Explorer

The original data :

_time reg exp raw

2019-09-20 A 1 100
2019-09-20 B 2 200
2019-09-20 C 3 300
2019-09-20 D 1 100
2019-09-20 E 2 200
2019-09-20 F 3 300
2019-09-19 A 1 100
2019-09-19 B 2 200
2019-09-19 C 3 300
2019-09-19 D 1 100
2019-09-19 E 2 200
2019-09-19 F 3 300

How to make a table as following: only show exp values with fields name as reg and have percentages:

_time per A B C D E F
2019-09-20 0.01 1 2 3 1 2 3 //exp values
2019-09-19 0.01 1 2 3 1 2 3 //exp values

per = sum(exp values)/sum(raw values) example: (1+2+3+1+2+3)/(100+200+300+100+200+300)

Tags (4)
1 Solution
SplunkTrust

hope i understand your requirement,

try this anywhere:

``````| makeresults count=1
| eval data = "2019-09-20 A 1 100;;;2019-09-20 B 2 200;;;2019-09-20 C 3 300;;;2019-09-20 D 1 100;;;2019-09-20 E 2 200;;;2019-09-20 F 3 300;;;2019-09-19 A 1 100;;;2019-09-19 B 2 200;;;2019-09-19 C 3 300;;;2019-09-19 D 1 100;;;2019-09-19 E 2 200;;;2019-09-19 F 3 300"
| makemv delim=";;;" data
| mvexpand data
| rex field=data "(?<time>[^\s]+)\s+(?<reg>[^\s]+)\s+(?<exp>[^\s]+)\s+(?<raw>[^\s]+)"
| table time reg exp raw
| rename COMMENT as "the above generates data below is the solution"
| eventstats sum(raw) as total_raw sum(exp) as total_exp by time
| eval percent = round(total_exp / total_raw, 2)
| eval {reg} = exp
| stats values(*) as * by time
``````

hope it helps

SplunkTrust

hope i understand your requirement,

try this anywhere:

``````| makeresults count=1
| eval data = "2019-09-20 A 1 100;;;2019-09-20 B 2 200;;;2019-09-20 C 3 300;;;2019-09-20 D 1 100;;;2019-09-20 E 2 200;;;2019-09-20 F 3 300;;;2019-09-19 A 1 100;;;2019-09-19 B 2 200;;;2019-09-19 C 3 300;;;2019-09-19 D 1 100;;;2019-09-19 E 2 200;;;2019-09-19 F 3 300"
| makemv delim=";;;" data
| mvexpand data
| rex field=data "(?<time>[^\s]+)\s+(?<reg>[^\s]+)\s+(?<exp>[^\s]+)\s+(?<raw>[^\s]+)"
| table time reg exp raw
| rename COMMENT as "the above generates data below is the solution"
| eventstats sum(raw) as total_raw sum(exp) as total_exp by time
| eval percent = round(total_exp / total_raw, 2)
| eval {reg} = exp
| stats values(*) as * by time
``````

hope it helps

Explorer

Thanks.

SplunkTrust

@jenniferhao try `... | table _time A B C D E F ...`

Explorer

Thanks, Adonio. It works well now.

Explorer

Thanks, Adonio. It works. If I only want to show _time A B C D E F percent. How Can I do this?

.conf21 CFS Extended through 5/20!

Don't miss your chance to share your Splunk wisdom in-person or virtually at .conf21!Call for Speakers hasbeen extended throughThursday, 5/20! Submit Now! >

Get Updates on the Splunk Community!