We want a grouping query showing user activity (page views) using a sliding window. What is the best way to build the query?
We would like to join the results of the following queries, but present the results as one set.
index="myIndex" host=myHost daysago=30 | stats Count by username
index="myIndex" host=myHost daysago=60 | stats Count by username
index="myIndex" host=myHost daysago=90 | stats Count by username
index="myIndex" host=myHost daysago=120 | stats Count by username
The output would look like...
+--------+--------+---------+---------+----------+ | User | 30 Day | 60 Days | 90 Days | 120 Days | +--------+--------+---------+---------+----------+ | James | 40 | 55 | 85 | 90 | | Janet | 10 | 25 | 30 | 60 | | Walter | 0 | 15 | 25 | 45 | +--------+--------+---------+---------+----------+
This seems to work, but is there a better way? Would selfjoin
, timechart
, or buckets
be a better approach?
index="myIndex" host=myHost daysago=30 | stats Count as 30Days by username | join username [search index="myIndex" host=myHost daysago=60 | stats Count as 60Days by username] | join username [search index="myIndex" host=myHost daysago=90 | stats Count as 90Days by username] | join username [search index="myIndex" host=myHost daysago=120 | stats Count as 120Days by username]| fields username, 30Days, 60Days, 90Days, 120Days
This seems to work, but is there a better way? Would selfjoin
, timechart
, or buckets
be a better approach?
index="myIndex" host=myHost daysago=30 | stats Count as 30Days by username | join username [search index="myIndex" host=myHost daysago=60 | stats Count as 60Days by username] | join username [search index="myIndex" host=myHost daysago=90 | stats Count as 90Days by username] | join username [search index="myIndex" host=myHost daysago=120 | stats Count as 120Days by username]| fields username, 30Days, 60Days, 90Days, 120Days