How do I edit my search to create a timechart with the mean per day for the top 5 pages in the last 7 days?

[zineer]

I'm sure this is probably easier than I'm making it, but I can't quite get what I want.

In our hit logs we track form ID (af) and request time in ms (et). I want to chart the mean(et) per day for each of the top 5 pages (by hit count) in the last 7 days. I have this so far:

source="apacheaccess.log" ac = "customer" | timechart span=1d mean(et) AS mt count AS cnt by af limit=5 useother=f

which is close, but it also charts the cnt field which I don't need to see. How do I exclude the count from the timechart?

Thanks!

[renjith_nair · Accepted solution]

If you just want to remove the count just use |fields - *cnt* after timechart

[zineer]

A wildcard when removing the field! Of course! I had tried it without the wildcard since I called it "cnt" but of course that didn't work. I didn't think to use a wildcard there. If you make this into an "answer" I can accept it! Thanks!!

[renjith_nair]

Glad that it's worked for you!

[jkat54]

 source="apacheaccess.log" ac = "customer" | timechart span=1d mean(et) AS mt by af limit=5 useother=f

[zineer]

Thanks! I had tried that, but it gives the top 5 mean times whereas what I'm looking for is the mean time of the top 5 most visited pages. I've edited the question to clarify that.

[jkat54]

Ok so you want a timechart that shows the top 5 pages by mean(et) over time?

Try this:
...| stats mean(et) as mt by af, _time | top 5 mt by af, _time | timechart mt by af

[zineer]

Close. I want to find the the top 5 pages by count(af) for the last 7 days, then for each day find the mean(et) for each of those 5 pages.
So if the top 5 pages as sorted by "count by af" are A, B, C, D, E, F, then I want to see:

Jan 22: A - mean(et) = 5, B - mean(et) = 10, C - mean(et) = 8, etc 
Jan 23: A - mean(et) = 6, B - mean(et) = 9, C - mean(et) = 8, etc
etc

[jkat54]

Ok thats doable but I'm mobile now and can't test my searches. I'll convert my answer to comment and maybe someone else will answer. I will check back ASAP too.