Splunk Search

Reformat data into 2 dimensional table for charting

kmaron
Motivator

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: alt text

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?

0 Karma
1 Solution

DalJeanis
SplunkTrust
SplunkTrust

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

View solution in original post

DalJeanis
SplunkTrust
SplunkTrust

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

View solution in original post

kmaron
Motivator

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

niketnilay
Legend

@kmaron, what the current output and expected output?

____________________________________________
| makeresults | eval message= "Happy Splunking!!!"
0 Karma

kmaron
Motivator

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.

0 Karma
.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 has
been extended through
Thursday, 5/20!