How to get average page size from access logs

[xvxt006]

Hi, From the access logs, i am getting the commands (part of URI) and their execution count in a tabular format. I want to add average page size to it. I am able to extract the page size individually by adding a field. But how can i include it in the same query so that i can get all stats with same query.

Current query

sourcetype=accesscombinedwcookie host=prgwc* | rex field=uri "((?:/wrewwee/)|(?:/werwerww/eww/))(?[a-zA-Z0-9]+)" | top limit=5000 command

For example, right now i have the output looks like this.

Command Count Percentage
Search 14000 8%
Home 7000 4%

I would like to have the output like this

Command Count Percentage AvgPageSize
Search 14000 8% 60k
Home 7000 4% 50k

How can i do this? Any help is appreciated 🙂

[alacercogitatus · Accepted solution]

I think you will be wanting appendcols command. The problem is that you have to different operations you want (top and stats). It makes the search longer, and runs 2 searches, but with different styles of data stats. Try this:

sourcetype=accesscombinedwcookie host=prgwc* | rex field=uri "((?:/wrewwee/)|(?:/werwerww/eww/))(?[a-zA-Z0-9]+)" | top limit=5000 command | appendcols [search sourcetype=accesscombinedwcookie host=prgwc* | rex field=uri "((?:/wrewwee/)|(?:/werwerww/eww/))(?[a-zA-Z0-9]+)" | stats avg(PageSize) as AvgPageSize by command]

http://docs.splunk.com/Documentation/Splunk/5.0/SearchReference/Appendcols

[xvxt006]

Hi, when i try to drill down, i am getting this message "PARSER: Applying intentions failed Unable to drilldown because of post-reporting 'appendcols' command". any idea why we would get this?

[xvxt006]

Awesome. I am getting the results on the first look they are looking good. Thanks for your help.

[Ayn]

If you use the stats command, you can just add this:

... | stats count,avg(PageSize) as AvgPageSize by command

(assuming you have the page size extracted to a field called "PageSize")

[Ayn]

Yeah you can't use both. The caveat with not using the top command is that you won't get the percent field. There are ways of calculating it with stats as well, though it's a bit more work than with top. Do you want the percent field?

[xvxt006]

Thanks Ayn. if i have both stats and top, results are not looking right. If i took out top, i don't see percentage for the commands. Any suggestions?

| stats count, avg(PageSize) as AvgPageSize by command | top limit=5000 command