Trying to create a stacked bar graph of my Apache access logs.
Currently I'm using a timechart to get the avg time in seconds (%T) by host as seen below:
index=apache_logs sourcetype=access_combined | rex "\"\\s+(?<seconds>\d+)\/(?<microseconds>\d+) \d+ \d+" | fields + _time, host, seconds | fields - _raw | timechart span=30m avg(seconds) by host
I would like to add the top 5 uri's during each 30 minute span so I can see both the avg time each of my hosts took to serve a request as well as the top 5 most costly uri's.
Was hoping someone could help me modify my original search to add in the top 5 uri_paths or give me some direction to go in in order to build an entirely new one.
I tried using the following documentation to build my graph but could not get it to work.
http://docs.splunk.com/Documentation/Splunk/6.0.2/Search/Chartmultipledataseries
Any help would be appreciated.
See if this works/suits your requirement. Its basically taking your query (with fields _time, host, avgSecs) and appending top 5 uri for same _time.
index=apache_logs sourcetype=access_combined | rex "\"\\s+(?<seconds>\d+)\/(?<microseconds>\d+) \d+ \d+" | bucket span=30m _time | stats avg(seconds) as avgSecs by host,_time
| appendcols [search index=apache_logs sourcetype=access_combined | bucket span=30m _time | top limit=5 uri_path by _time,host | fields - count,percent | stats values(uri_path) as topURIs by _time, host]
| nomv topURIs | eval Host_TopURIs=host + ", Top URIs: " + topURIs
| timechart span=30m avg(avgSecs) by Host_TopURIs
chart avg(sec) over host by uri_path try this
Sorry for the delay in getting back with you. To clarify my issue I currently have 2 graphs, 1 for avg(secs) by uri_path and another for avg(secs) by host. I was hoping to combine these 2 into one graph/dashboard that shows each hosts avg time in seconds as well as the top 5 uri_paths based on avg(secs) during that same period. So on the graph you would have 2 columns every 30 minutes representing the avg(secs) by host and each of those columns would have 5 segments representing the top 5 uri_paths by avg(secs). Hopefully that makes some sense, appreciate all the help so far.
Just to be sure, you want top 5 uri_path based on Avg(seconds) and each uri_path should be a separate column with corresponding avg(seconds) value so that you can graph them in stacked manner?
e.g. output
_time uri_path1 uri_path2 uri_path3 uri_path4 uri_path5
xxx 1.2 2.4 3.9 4.5 6.3
... ... .... .... .... ....
This is definitely the direction I want to go in, my 2 biggest concerns right now are that the top uri's are by count and not avg time (in seconds), as well as I would like each uri to be a segment on the column instead of having 2 columns with with a list of uri's as the description. Essentially I don't want to combine the uri's into one field as I would like to use the stack option to graph them. I'm working on modifying the query to accomplish the above but would like to hear your thoughts.
It will be what I resort to if no one else can show me how I can combine the 2 together. I would prefer to have it combined as it will be cleaner and easier to quickly hover over and see what uri's were being served during slower periods.
You can have graph for your timechart for avg(seconds) and table to show top 5 uri_path for every 30 min period (in dashboard) will that work for you?
Ok, I assume this could be done using a dashboard which I would be fine with as well.
list of top 5 uri_paths will be text field and it won't show up in graphs.