Splunk Search

Generate Matrix from Table Data

jean_boulanger
Explorer

Hi,

I am currently working with a table that looks like this:

col1 | col2 | value
xA  | yA | 1.0
xA | yB | 1.5
xB | yA | 2.0
xB | yB | 2.5

From this table, I would like to create a matrix, like so:

  /  | yA | yB
xA | 1.0 | 1.5
xB | 2.0 | 2.5

I tried to use the transpose command, but I have been rather unsuccessful so far. Is this possible with Splunk?

0 Karma
1 Solution

cmerriman
Super Champion

try adding this to your query:

|xyseries col1 col2 value

i used this runanywhere command for testing:

|makeresults|eval data="col1=xA,col2=yA,value=1.0 col1=xA,col2=yB,value=1.5 col1=xB,col2=yA,value=2.0 col1=xB,col2=yB,value=2.5"|makemv data|mvexpand data|rename data as _raw|kv|table col1 col2 value|xyseries col1 col2 value

https://docs.splunk.com/Documentation/SplunkCloud/6.6.3/SearchReference/Xyseries

View solution in original post

cmerriman
Super Champion

try adding this to your query:

|xyseries col1 col2 value

i used this runanywhere command for testing:

|makeresults|eval data="col1=xA,col2=yA,value=1.0 col1=xA,col2=yB,value=1.5 col1=xB,col2=yA,value=2.0 col1=xB,col2=yB,value=2.5"|makemv data|mvexpand data|rename data as _raw|kv|table col1 col2 value|xyseries col1 col2 value

https://docs.splunk.com/Documentation/SplunkCloud/6.6.3/SearchReference/Xyseries

jean_boulanger
Explorer

This worked like a charm, thank you

0 Karma

nryabykh
Path Finder

Hi, Jean.

Xyseries command implements exact what you need: https://docs.splunk.com/Documentation/SplunkCloud/6.6.3/SearchReference/Xyseries

davpx
Communicator

untable might help

0 Karma
Get Updates on the Splunk Community!

Enhance Security Visibility with Splunk Enterprise Security 7.1 through Threat ...

(view in My Videos)Struggling with alert fatigue, lack of context, and prioritization around security ...

Troubleshooting the OpenTelemetry Collector

  In this tech talk, you’ll learn how to troubleshoot the OpenTelemetry collector - from checking the ...

Adoption of Infrastructure Monitoring at Splunk

  Splunk's Growth Engineering team showcases one of their first Splunk product adoption-Splunk Infrastructure ...