With this query, identify top 10 countries with most overall hits, then identify the number of users by timeslice, and lastly count the number of users by timeslice for those countries. Hint: To better understand this query, comment out all lines except the first 4. Run the query and review results, then uncomment the next line repeating the same process. This will help you better understand the intermediate results that lead to the final results. Enjoy!
| parse "|REQUEST|*|*|GET|" as src_ip, users
| lookup country_name from geo://default on ip=src_ip
| where !(isNull(country_name))
| timeslice by 5m
| count(users) as users by _timeslice, country_name | sort by users, country_name
| total users by country_name
| sort by _total
| 1 as rownum
| accum rownum as index by country_name
| if(index=1, 1,0) as isFirst
| accum isFirst as rankOrder
| where rankOrder <= 10
| fields _timeslice, country_name, users
| transpose row _timeslice column country_name
Please sign in to leave a comment.