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).
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:
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
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.