How to edit my search to turn table results into table headers?

[mistydennis]

I'm running a search that combines download counts of external and internal viewers. To chart the different internal and external views, I'm using:

| stats count, dc(src_ip) as distinctip, dc(foo) as bar by agentType, IPLocation

This produces a table similar to this:

IPLocation  |   Count   |   DistinctIP
External    |     8     |      4
Internal    |     5     |      3

What I would like is to have the External and Internal views as column headers instead of results in a row. I'm looking for:

IPLocation-External  |   IPLocation - Internal  |   Count DistinctIP

I'm relatively new to Splunk, hopefully this makes sense!

[niketn · Accepted solution]

Based on your needs you can either use transpose or xyseries

Transpose to generate column for count distinctip agentType and IPLocation and an integer number x to create x number of rows. If x is not present 5 rows get created i.e. row 1, row 2... row 5 by default.

 | stats count, dc(src_ip) as distinctip by agentType, IPLocation | transpose x

There could be two variations of xyseries as give below:

 | stats count, dc(src_ip) as distinctip by agentType, IPLocation | xyseries agentType IPLocation count distinctip

OR

 | stats count, dc(src_ip) as distinctip by agentType, IPLocation | xyseries IPLocation agentType count distinctip  

[mistydennis]

Thank you - this worked perfectly! I used:

| stats count, dc(src_ip) as distinctip by agentType, IPLocation | xyseries agentType IPLocation count distinctip

One more question: now I have column headers agentType, count:External, count:Internal, etc... How do I change these to something more user friendly after using xyseries?

[niketn]

Option 1
You can you format or sep command as per your need to rename column header. Refer to xyseries documentation: http://docs.splunk.com/Documentation/Splunk/6.5.1/SearchReference/Xyseries

xyseries column headers will be in AGG:VALUE format.

Option 2
You can also rename columns before piping with xyseries for desired output column name.

Option 3
You can rename columns afterwards using rename command or eval command. But this will be tedious.