A shiny app to explore nginx access logs and geolocate the connections
You can not select more than 25 topics Topics must start with a letter or number, can include dashes ('-') and can be up to 35 characters long.

44 lines
1.1KB

  1. library(tidyverse)
  2. library(RSQLite)
  3. library(ipapi)
  4. commandArgs(trailingOnly = T) -> cmdargs
  5. dbfile <- cmdargs[1]
  6. logfile <- cmdargs[2]
  7. dbConnect(SQLite(), dbfile) -> db
  8. logfile %>%
  9. read_delim(delim = " ", col_names = c("ip", "null1", "user", "timestamp", "zone", "req", "status", "size", "referer", "agent", "null2")) %>%
  10. mutate_all(na_if, "-") %>%
  11. filter(user %>% is.na) %>%
  12. filter(!ip %in% c("192.168.0.254", "164.2.255.244")) %>%
  13. mutate(timestamp = timestamp %>% as.POSIXct(format = "[%d/%b/%Y:%H:%M:%S")) %>%
  14. separate(req, into = c("method", "url", "version"), sep = " ") %>%
  15. select(ip, timestamp, url, status, referer, agent) %>%
  16. dbWriteTable(conn = db, name = "access", value = ., append = T)
  17. known_ips <- character(0)
  18. db %>%
  19. tbl("geoip") %>%
  20. select(ip) %>%
  21. collect %>%
  22. pull(ip) -> known_ips
  23. db %>%
  24. tbl("access") %>%
  25. select(ip) %>%
  26. collect %>%
  27. pull(ip) %>%
  28. unique %>%
  29. setdiff(known_ips) -> ips
  30. if (length(ips) > 0)
  31. {
  32. ips %>%
  33. geolocate %>%
  34. select(ip = query, city, country, lat, lon) %>%
  35. dbWriteTable(conn = db, name = "geoip", value = ., append = T)
  36. }