Getting the number of visits of a specific route in nginx using elasticsearch

Elastic Stack Elasticsearch
1

I m using Nginx as a proxy to my website, I've configured Logstash to parse Nginx log files and save them to Elasticsearch... Now I want to analyse those data.
I need a query to get number of visits of visits of a specific route, like http://company/company_name and /joboffer/job_offer_title

nginx Logs are stored like this in Elasticsearch:
referrer: url of the resource
verb: HTTP method like GET, POST, ...

{
        "_index": "logstash-2016.07.27",
        "_type": "nginx-access",
        "_id": "AVZMOZHNl8SqLoCNz3nb",
        "_score": 1,
        "_source": {
          "message": "172.18.0.1 - - [27/Jul/2016:14:39:29 +0000] \"GET /favicon.ico HTTP/1.1\" 404 24 \"http://jobi.dev/\" \"-\" \"Mozilla/5.0 (X11; Linux x86_64) AppleWebKit/537.36 (KHTML, like Gecko) Chrome/52.0.2743.82 Safari/537.36\" \"-\"",
          "@version": "1",
          "@timestamp": "2016-07-27T14:39:29.000Z",
          "path": "/tunlogia/nginx/access.log",
          "host": "fa6cdfd75875",
          "type": "nginx-access",
          "clientip": "172.18.0.1",
          "ident": "-",
          "auth": "-",
          "verb": "GET",
          "request": "/favicon.ico",
          "httpversion": "1.1",
          "response": 404,
          "bytes": 24,
          "referrer": "\"http://jobi.dev/\"",
          "x_alt_referrer": "\"-\"",
          "agent": "\"Mozilla/5.0 (X11; Linux x86_64) AppleWebKit/537.36 (KHTML, like Gecko) Chrome/52.0.2743.82 Safari/537.36\"",
          "extra_fields": " \"-\"",
          "name": "Chrome",
          "os": "Linux",
          "os_name": "Linux",
          "device": "Other",
          "major": "52",
          "minor": "0",
          "patch": "2743"
        }
      }

My question is how can I get the number of visits of a specific route having 'GET' as a verb

2

Based on the example record provided, how would you determine the route?

3

The route is in the referrer field

4

I need two queries to count two kind of routes:

  • Company profile visits like .*/company/company_name

  • Company job offer visits like .*/joboffers/job_offer_title-at-company_name

and in both queries the verb field must be 'GET'
And both queries to be aggregated over time using '@timestamp' field

5

Depending on the complexity of the rules around extracting route from the referrer field, it may be worthwhile extracting this into a separate field prior to indexing it into Elasticsearch.

6

It's already in a separate field in ES, parsed from nginx access log uisng Logstash

7

I changed my query, and I m using the request field now, my use case is simple: I want to filter my docs using a string for field verb and a string for field request.
Something like

SELECT count(*) FROM table WHERE table.verb like 'GET' AND table.request like 'title-at-icagile' GROUP BY @timestamp;

Is there a query to do that using ES?

8

Yes, you should be able to do that through aggregations. What have you tried so far?

If you find it hard to get into aggregations, you can also explore your data using Kibana, in which you can see the aggregations being generated for each visualisation and use these as a starting point.