Grouping Aggregate Queries Together

[slierninja]

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...

Page Views by User in Last xx Days

+--------+--------+---------+---------+----------+
|  User  | 30 Day | 60 Days | 90 Days | 120 Days |
+--------+--------+---------+---------+----------+
| James  |     40 |      55 |      85 |       90 |
| Janet  |     10 |      25 |      30 |       60 |
| Walter |      0 |      15 |      25 |       45 |
+--------+--------+---------+---------+----------+

[slierninja · Accepted solution]

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