topic Re: Reformat data into 2 dimensional table for charting in Splunk Search

Reformat data into 2 dimensional table for charting

kmaron — Tue, 29 Sep 2020 15:11:41 GMT

I keep going around in circles with this and I'm getting nowhere so I'm asking for help.

My events look like this:

2017-08-02 12:43:25.707, CE_OPERATIONS_COMPONENT="1", AO_OPERATIONS_COMPONENT="5", WSREQUEST_COMPONENT="0", DELAY_COMPONENT="2"
source = CLAIMSTG-PROD

2017-08-02 12:43:25.689, CE_OPERATIONS_COMPONENT="0", AO_OPERATIONS_COMPONENT="2", WSREQUEST_COMPONENT="0", DELAY_COMPONENT="3"
source = LIFETG-PROD

2017-08-02 12:43:25.689, CE_OPERATIONS_COMPONENT="4", AO_OPERATIONS_COMPONENT="0", WSREQUEST_COMPONENT="1", DELAY_COMPONENT="0"
source = FSCDTG-PROD

When I do stats on them I can do Source on the left, then the component field names across the top and the values as the cells.
What I really would like would be a table like one of these:

But I can't seem to figure out how to move the field names into a field value or something to do this.
Maybe there's another way? Or a way to shift things around?

Re: Reformat data into 2 dimensional table for charting

niketn — Wed, 02 Aug 2017 18:05:07 GMT

@kmaron, what the current output and expected output?

Re: Reformat data into 2 dimensional table for charting

kmaron — Wed, 02 Aug 2017 18:10:08 GMT

The current output is a table with Source on the left, then the component field names across the top and the values as the cells.

Expected output is the image I shared with Source in the left column, component name in the second column and the values in the right hand column.

Re: Reformat data into 2 dimensional table for charting

DalJeanis — Wed, 02 Aug 2017 18:31:55 GMT

You are looking for the untable command. See https://docs.splunk.com/Documentation/SplunkCloud/6.6.1/SearchReference/Untable

This run anywhere sample creates a test table ...

| makeresults 
| eval mydata="foo,mike,1 foo,jim,3 foo,babs,7 bar,jim,4 bar,babs,3 bar,jimmy,4"
| makemv mydata 
| mvexpand mydata 
| rex field=mydata "(?<source>[^,]*),(?<salesman>[^,]*),(?<salescount>\d+)"
| chart sum(salescount) as count over salesman by source

...which looks like this...

salesman    bar   foo
babs        3      7  
jim         4      3  
jimmy       4    
mike               1

Now that we've doen all that work to get sample stuff, here's your command...

 | untable salesman source count

...which reformats the above table result to this...

salesman source count   
babs     bar     3  
babs     foo     7  
jim      bar     4  
jim      foo     3  
jimmy    bar     4  
mike     foo     1

Note that in the untable command, the first argument must be a real field name, and the next two are arbitrary. The second is the place the column field name will GO, and the third is the place that that column field's value will go.

Now, if you feed the output of untable straight back into the xyseries command with the exact same arguments, it will put your table back exactly the way it was. (unless there were size limits or other issues...)

 | xyseries salesman source count

See https://docs.splunk.com/Documentation/SplunkCloud/6.6.1/SearchReference/Xyseries

Re: Reformat data into 2 dimensional table for charting

kmaron — Wed, 02 Aug 2017 18:46:51 GMT

This is exactly what I was looking for! Thank you!!!