Top 10 Countries and related Number of Users

Follow

Mario Sanchez

• May 27, 2017 00:32
• Edited

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!

_sourceCategory=Artifactory/Request 
| 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

0

• 

  Ryan Johnson

  • August 07, 2017 06:49

  UPDATED: With the new fillmissing operator, you can use a quasi-subquery to achieve the same end.

  _sourceCategory=nginx/access/json
  | parse "\"source_ip\": \"*\"" as c_ip
  | lookup country_name FROM geo://default ON ip=c_ip
  | timeslice 5m
  | count AS users BY _timeslice, country_name
  | filter country_name IN (sum(users) AS total_users_by_country BY country_name | sort total_users_by_country | limit 10 )
  | transpose row _timeslice column country_name

  0

  Comment actions Permalink

Please sign in to leave a comment.