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!
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
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
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?
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.