Splunk Search

How to transform groups of rows into columns

Hung_Nguyen
Path Finder

I am running a query that gives me various percentile metric in different row, and I would like to format them in an easily readable table.  For example, here is the current outcome after I run the below query

index=my_indexer
| stats
p50(startuptime) as "startuptime_p50",
p90(startuptime) as "startuptime_p90",
p99(startuptime) as "startuptime_p99",

p50(render_time) as "render_time_p50",
p90(render_time) as "render_time_p90",
p99(render_time) as "render_time_p99",

p50(foobar_time) as "foobar_time_p50",
p90(foobar_time) as "foobar_time_p90",
p99(foobar_time) as "foobar_time_p99",
| transpose

column                             row1
startuptime_p50         50
startuptime_p70         70
startuptime_p90         90
render_time_p50         51
render_time_p70         72
render_time_p90         93
foobar_time_p50         53
foobar_time_p70         74
foobar_time_p90         95

I would like to format the final table as follow (the column header is optional)

Marker                P50         P70         P90
startup                50            70            90
render                 51            72            93
foobar                 53            74            95

thank you very much for your help

Labels (2)
0 Karma
1 Solution

bowesmana
SplunkTrust
SplunkTrust

See this example of your data using the chart command to do what you want

| makeresults
| eval _raw="column row1
startuptime_p50 50
startuptime_p70 70
startuptime_p90 90
render_time_p50 51
render_time_p70 72
render_time_p90 93
foobar_time_p50 53
foobar_time_p70 74
foobar_time_p90 95"
| multikv forceheader=1
| table column row1
| rex field=column "_?time_(?<metric>\w+)"
| rex field=column "(?<Marker>[^_]*)_?time.*"
| chart values(row1) over Marker by metric

You can sort as required after this

View solution in original post

0 Karma

bowesmana
SplunkTrust
SplunkTrust

See this example of your data using the chart command to do what you want

| makeresults
| eval _raw="column row1
startuptime_p50 50
startuptime_p70 70
startuptime_p90 90
render_time_p50 51
render_time_p70 72
render_time_p90 93
foobar_time_p50 53
foobar_time_p70 74
foobar_time_p90 95"
| multikv forceheader=1
| table column row1
| rex field=column "_?time_(?<metric>\w+)"
| rex field=column "(?<Marker>[^_]*)_?time.*"
| chart values(row1) over Marker by metric

You can sort as required after this

0 Karma
Get Updates on the Splunk Community!

Dashboards: Hiding charts while search is being executed and other uses for tokens

There are a couple of features of SimpleXML / Classic dashboards that can be used to enhance the user ...

Splunk Observability Cloud's AI Assistant in Action Series: Explaining Metrics and ...

This is the fourth post in the Splunk Observability Cloud’s AI Assistant in Action series that digs into how ...

Brains, Bytes, and Boston: Learn from the Best at .conf25

When you think of Boston, you might picture colonial charm, world-class universities, or even the crack of a ...