Use of regular expression in the Kibana Visualization

[AshishC (Ashish)]

Hi,

I am trying to create a dashboard to display top 10 URL hits using access log. In order to do this, I have created a visualization of the type 'Data Table'.

Data Table Setting:
Metrics : Count
Buckets : Split Rows
Aggregation : Terms
Field: URL
Oder By: metric : Count

Resulted visualization

URL Count
GET /services/cdc/data/92143/json HTTP/1.1 1,475
GET /services/cdc/dataTopics/3492/json HTTP/1.1 1,462
GET /services/cdc/dataTopics/100962/details/json HTTP/1.1 1,223
GET /services/cdc/dataTopics/100773/details/json HTTP/1.1 1,220
GET /services/cdc/dataTopics/94019/details/json HTTP/1.1 1,198
GET /services/cdc/dataTopics/96093/details/json HTTP/1.1 1,196
GET /services/cdc/dataTopics/100787/details/json HTTP/1.1 1,191
GET /services/cdc/dataTopics/94068/details/json HTTP/1.1 1,189
GET /services/cdc/dataTopics/94311/details/json HTTP/1.1 1,189
GET /services/cdc/dataTopics/92937/details/json HTTP/1.1 1,188

But, I would like to aggregate highlighted URLs into single data line, something like below:

URL Count
GET /services/cdc/data/92143/json HTTP/1.1 1,475
GET /services/cdc/dataTopics/3492/json HTTP/1.1 1,462
GET /services/cdc/dataTopics/xxxxxx/details/json HTTP/1.1 9,594

Other two things I like to do is:

2. Split the URL into two columns namely verb and endpoints

Verb URI Count
GET /services/cdc/data/92143/json 1,475
GET /services/cdc/dataTopics/3492/json 1,462
GET /services/cdc/dataTopics/xxxxxx/details/json 9,594

3. Divide the Count value by Time Range, for example, if select range is last 1 hour and divide the column value by 3600

Verb URI Count
GET /services/cdc/data/92143/json 0.41
GET /services/cdc/dataTopics/3492/json 0.41
GET /services/cdc/dataTopics/xxxxxx/details/json 2.67

[wylie (Wylie Conlon)]

The Terms aggregation only works on Keyword fields, which are exact strings. However, you may be able to use a scripted term to rewrite the values. You can do this in the Advanced setting for your visualization:

https://www.elastic.co/guide/en/elasticsearch/reference/current/search-aggregations-bucket-terms-aggregation.html#search-aggregations-bucket-terms-aggregation-script