topic Re: Generate Matrix from Table Data in Splunk Search

Generate Matrix from Table Data

jean_boulanger — Thu, 18 Jan 2018 13:12:45 GMT

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?

Re: Generate Matrix from Table Data

davpx — Thu, 18 Jan 2018 23:14:56 GMT

untable might help

Re: Generate Matrix from Table Data

nryabykh — Fri, 19 Jan 2018 08:23:12 GMT

Hi, Jean.

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

Re: Generate Matrix from Table Data

cmerriman — Fri, 19 Jan 2018 12:51:47 GMT

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

Re: Generate Matrix from Table Data

jean_boulanger — Fri, 19 Jan 2018 13:35:28 GMT

This worked like a charm, thank you