Splunk Search
Highlighted

Combining Row Results Into Columns

Builder

Here is my search:

| dbinspect index=netflow
| stats sum(sizeOnDiskMB) as StateSize by state, splunk_server
| eval StateSizeGB = StateSize / 1024

Which produces this:

state   splunk_server   StateSize   StateSizeGB
cold    spli1   299868.203115   292.840042104
cold    spli2   299702.707027   292.678424831
cold    spli3   299967.269538   292.936786658
hot spli1   2687.171875 2.624191284
hot spli2   8268.820312 8.075019836
hot spli3   5440.226563 5.312721253
warm    spli1   94026.433592    91.822689055
warm    spli2   90233.117186    88.118278502
warm    spli3   92676.191410    90.504093174

What I would like to see is this:

splunk_server     hot                                  warm                           cold
spli1                     92676.191410                299967.269538    2687.171875  
spli2                     92676.191410               299967.269538     2687.171875  
spli3                    92676.191410               299967.269538              2687.171875  

Disregard the actual numbers. How would I go about formatting the results?

Thanks!

0 Karma
Highlighted

Re: Combining Row Results Into Columns

Champion

You want to use the chart command

| dbinspect index=netflow
| chart eval(sum(sizeOnDiskMB)/1024) by splunk_server state
| table splunk_server hot warm cold

View solution in original post

Highlighted

Re: Combining Row Results Into Columns

Builder

Thanks for the response! The formatting is correct, but I only have values for "hot".

"Warm" and "Cold" are blank.

0 Karma
Highlighted

Re: Combining Row Results Into Columns

Champion

What do you see if you change the last command to | table splunk_server *

0 Karma
Highlighted

Re: Combining Row Results Into Columns

Builder

PICNIC error on this one. 🙂

I didn't have the time range set correctly. As soon as I switched it to "All-Time", it works perfectly.

Thank you!!

0 Karma
Highlighted

Re: Combining Row Results Into Columns

SplunkTrust
SplunkTrust

Here's the brute force method -

This just produces test data

|makeresults | eval mydata=mvappend("cold!!!!spli1!!!!299868.203115!!!!292.840042104",
"cold!!!!spli2!!!!299702.707027!!!!292.678424831",
"cold!!!!spli3!!!!299967.269538!!!!292.936786658",
"hot!!!!spli1!!!!2687.171875!!!!2.624191284",
"hot!!!!spli2!!!!8268.820312!!!!8.075019836",
"hot!!!!spli3!!!!5440.226563!!!!5.312721253",
"warm!!!!spli1!!!!94026.433592!!!!91.822689055",
"warm!!!!spli2!!!!90233.117186!!!!88.118278502",
"warm!!!!spli3!!!!92676.191410!!!!90.504093174")
| mvexpand mydata
| rex field=mydata "(?<state>[^!]*)!!!!(?<splunk_server>[^!]*)!!!!(?<statesize>[^!]*)!!!!(?<statesizeGB>[^!]*)"
| fields splunk_server state statesizeGB

this parses it out

| chart sum(eval(if(state="cold",1,0)*statesizeGB)) AS COLD, sum(eval(if(state="warm",1,0)*statesizeGB)) AS WARM sum(eval(if(state="hot",1,0)*statesizeGB)) AS HOT by splunk_server

producing this

splunk_server   COLD            WARM            HOT             
spli1           292.840042104   91.822689055    2.624191284     
spli2           292.678424831   88.118278502    8.075019836     
spli3           292.936786658   90.504093174    5.312721253     

There is a way to do this without all that hard code, using xyseries and/or untable, but I couldn't find it in 5 min of looking.


In SIX minutes, however, I could,

| chart sum(statesizeGB) OVER splunk_server BY state

Reference - section 10 of this page - https://docs.splunk.com/Documentation/Splunk/6.5.2/SearchReference/Chart

Results are basically identical to the above, but with lower-case states.

0 Karma