I have a table:
PageID, UserName, Date, count of hits to that page,
I would like to find the average daily page hits
, per article
at a UserID
level. (for the top 100 most frequently viewed pages)
So for example,
Person xyz
, on average views page x
, n
number of times per day over the last week.
This is the start of the query...
... | bucket span=1d _time| stats count by PageID, UserName , _time | sort - count |head 100
Any help much appreciated.
Try this:
... | bucket span=1d _time
| stats count BY PageID, UserName , _time
| rename COMMENT AS "Calculate and keep the 100 most popular pages"
| eventstats sum(count) AS grandTotal BY PageID
| sort 0 - grandTotal
| streamstats dc(PageID) AS PageRank
| search PageRank <= 100
| rename COMMENT AS "Find avg daily hits per PageID per UserID"
| stats avg(count) AS DailyPageHits BY PageID UserID
Or, more simply this:
... | bin _time span=1h
| top limit=100 PageID BY UserID _time
| stats avg(count) AS DailyPageHits BY PageID UserID
Try this:
... | bucket span=1d _time
| stats count BY PageID, UserName , _time
| rename COMMENT AS "Calculate and keep the 100 most popular pages"
| eventstats sum(count) AS grandTotal BY PageID
| sort 0 - grandTotal
| streamstats dc(PageID) AS PageRank
| search PageRank <= 100
| rename COMMENT AS "Find avg daily hits per PageID per UserID"
| stats avg(count) AS DailyPageHits BY PageID UserID
Or, more simply this:
... | bin _time span=1h
| top limit=100 PageID BY UserID _time
| stats avg(count) AS DailyPageHits BY PageID UserID
Amazing thank you sir.