Average URI hits per minute, graphed.

[nocostk]

Scraping my Apache access log I want to find the average request per minute for each of four URI's. Here is my access log (keep in mind there is more in the log than just the four URIs):

192.168.0.1 - - [18/Jan/2011:10:10:10 -0700] "POST /GuiSapi/partner/manageOrders.seam HTTP/1.1" 200 526 "https://mysite.foo.com/GuiSapi/partner/manageOrders.seam" "Mozilla/4.0 (compatible; MSIE 7.0; Windows NT 6.0; SLCC1; .NET CLR 2.0.50727; Media Center PC 5.0; .NET CLR 3.5.30729; .NET CLR 3.0.30618)" "JSESSIONID=8AA;"
192.168.0.1 - - [18/Jan/2011:10:11:10 -0700] "POST /GuiSapi/home.seam HTTP/1.1" 200 526 "https://mysite.foo.com/GuiSapi/home.seam" "Mozilla/4.0 (compatible; MSIE 7.0; Windows NT 6.0; SLCC1; .NET CLR 2.0.50727; Media Center PC 5.0; .NET CLR 3.5.30729; .NET CLR 3.0.30618)" "JSESSIONID=8AA;"
192.168.0.1 - - [18/Jan/2011:10:13:10 -0700] "POST /GuiSapi/partner/viewProducts.seam HTTP/1.1" 200 526 "https://mysite.foo.com/GuiSapi/partner/viewProducts.seam" "Mozilla/4.0 (compatible; MSIE 7.0; Windows NT 6.0; SLCC1; .NET CLR 2.0.50727; Media Center PC 5.0; .NET CLR 3.5.30729; .NET CLR 3.0.30618)" "JSESSIONID=8AA;"
192.168.0.1 - - [18/Jan/2011:10:35:10 -0700] "POST /GuiSapi/cs/returns.seam HTTP/1.1" 200 526 "https://mysite.foo.com/GuiSapi/cs/returns.seam" "Mozilla/4.0 (compatible; MSIE 7.0; Windows NT 6.0; SLCC1; .NET CLR 2.0.50727; Media Center PC 5.0; .NET CLR 3.5.30729; .NET CLR 3.0.30618)" "JSESSIONID=8AA;"

I'm able to get the graph generated just fine using:

host="ppa*" (uri="/GuiSapi/home.seam" OR uri="/GuiSapi/partner/viewProducts.seam" OR uri="/GuiSapi/partner/manageOrders.seam" OR uri="/GuiSapi/cs/returns.seam") | timechart count by uri

But I'm not sure what I need to do to get the average count based on a string. I see examples for averages on numbers - but not what I need.

[nocostk · Accepted solution]

Working with Splunk support this query is exactly what I need.

host="ppa*" (uri="/GuiSapi/home.seam" OR uri="/GuiSapi/partner/viewProducts.seam" OR uri="/GuiSapi/partner/manageOrders.seam" OR uri="/GuiSapi/cs/returns.seam") | bucket _time span=1m | stats count AS PerMinCount by _time uri | timechart span=10m per_minute(PerMinCount) by uri

[tedder]

I think you need to build up a count per minute, then average it. Here's what I used:

host="ppa*" (uri="/GuiSapi/home.seam" OR uri="/GuiSapi/partner/viewProducts.seam" OR uri="/GuiSapi/partner/manageOrders.seam" OR uri="/GuiSapi/cs/returns.seam") | bucket span=1m _raw | stats count as peruri_count by uri | stats avg(peruri_count) by uri

You can build that up incrementally to see if it's working:

host="ppa*" (uri="/GuiSapi/home.seam" OR uri="/GuiSapi/partner/viewProducts.seam" OR uri="/GuiSapi/partner/manageOrders.seam" OR uri="/GuiSapi/cs/returns.seam") | bucket span=1m _raw | stats count as peruri_count by uri

In this case, you should see one row per minute and one column per URI.

[nocostk]

One line for each url. And instead of graphing the sum of the number of requests between each _bucket() I get the average of the number of requests between each _bucket().

[tedder]

You want the average of what? Four lines, each one is the average of what? Or one line that is the average of the four URIs?

[nocostk]

I uploaded an image to ImageShack so I can clarify what I'm after. http://img838.imageshack.us/i/splunk.png/ The top image is what I want except in that image the results are generated by the sum of events. I'm looking more for the average. The second image is what is generated from the second query. Sorry about making this so difficult.

[tedder]

Sorry if I'm assuming incorrectly, but change it to line chart so you can see the four URIs listed as lines, rather than discrete bars.

[nocostk]

Well at first I was doing the standard report view but I just tried advanced charting and the results were the same. The resulting charts are only showing one column for each URI with the values of (I assume) the count() function. That's fine for summarising the data over the specified search range but not for trending. Maybe that's what is intended and I'm just not grasping it correctly?

[tedder]

Your second query should be correct, then. Are you looking at "advanced charting" to display this?

[nocostk]

Hmm, not quite. With the second example it's just printing out four rows (one with each URI) and two columns (uri and peruri_count).

I'd like to be able to send these to timechart() if possible and have each URI be a line on the graph with the avg between two intervals (perhaps defined in timechart() with span=1m?) plotted.