Splunk Search

How can I format my search to convert month/day column values into column headers?

ShaneNewman
Motivator

I don't know how to word this request very effectivly so I will just show some examples... If anyone knows a better way to word the title of this post, please do.

I have my search setup to return the fields and values I want (see below).
Before Transpose

I need the Month field to be column headers so transpose seems to be the best option for this... Except for 1 little wrinkle. It looks like this:

column               row 1           row 2
Month               08 Aug 2014      09 Sep 2014
New               53            228658
Reconnect           0                59913
Ratio              0                3.816501
Total Sessions     53              288571

This is close, I am looking to have this though:
alt text

How do I make this happen?

Here is the actual search:

 earliest=-1mon@mon latest=@mon `xd_index` sourcetype=xendesktop:*:session SiteName="*" SiteName!=*ST UserName!="" StartTime!="" BrokeringTime!="" | stats latest(EstablishmentDuration) as EstablishmentDuration, latest(BrokeringDuration) as BrokeringDuration by BrokeringTime, SessionKey, SiteName | eventstats min(BrokeringTime) as min_BrokeringTime by SessionKey | eval connection_type=if(BrokeringTime=min_BrokeringTime, "New", "Reconnect") | eval timestamp=strptime(min_BrokeringTime, "%m/%d/%Y %H:%M:%S") | eval Month=strftime(timestamp, "%m %b %Y") | chart count by Month, connection_type | eval Ratio=New/Reconnect | eval "Total Sessions"=New+Reconnect | transpose 12
1 Solution

martin_mueller
SplunkTrust
SplunkTrust

Instead of using yucky transpose, append this to your search:

...  | untable Month Metric value | xyseries Metric Month value

Note how the two fields are swapped in the second command.

View solution in original post

martin_mueller
SplunkTrust
SplunkTrust

Instead of using yucky transpose, append this to your search:

...  | untable Month Metric value | xyseries Metric Month value

Note how the two fields are swapped in the second command.

ShaneNewman
Motivator

Perfect!!!!! Thank you!

0 Karma
Get Updates on the Splunk Community!

Index This | I am a number, but when you add ‘G’ to me, I go away. What number am I?

March 2024 Edition Hayyy Splunk Education Enthusiasts and the Eternally Curious!  We’re back with another ...

What’s New in Splunk App for PCI Compliance 5.3.1?

The Splunk App for PCI Compliance allows customers to extend the power of their existing Splunk solution with ...

Extending Observability Content to Splunk Cloud

Register to join us !   In this Extending Observability Content to Splunk Cloud Tech Talk, you'll see how to ...