Apache access_log duration aggregation by request URL

InfluxDB 2 Dashboards
1

Hi,

I started to migrate my companies metrics from InfluxDB V1 to V2, but there is this one query I’m totally lost and hopefully anyone can give me hint.

The query:

SELECT mean("duration") FROM "autogen"."apache_access.log" WHERE ("path" = '/var/log/apache_access.log' AND "calledurl" =~ /\.php/) AND $timeFilter GROUP BY time(5m) fill(null)

We are using this query in Grafana and it works well, I can’t find a why to filter by the “calledurl” and then mean by the duration.

I tried the following query, but it’s just not the expected view.

from(bucket: "test")

  |> range(start: v.timeRangeStart, stop: v.timeRangeStop)

  |> filter(fn: (r) => r["_measurement"] == "apache_access_log")

  |> filter(fn: (r) => r["path"] == "/var/log/apache_access.log")

  |> filter(fn: (r) => r["host"] == "web1" or r["host"] == "web2")

  |> filter(fn: (r) => r["_field"] == "calledurl")

  |> filter(fn: (r) => r["_value"] =~ /php/)

  |> aggregateWindow(every: v.windowPeriod, fn: last, createEmpty: false)

  |> yield(name: "duration")

Thanks in advance for any help!

2 
from(bucket: "test")
  |> range(start: -1m)
  |> filter(fn: (r) => r._field == "calledurl" or r._field == "duration")
  |> pivot(
    rowKey:["_time"],
    columnKey: ["_field"],
    valueColumn: "_value"
  )
  |> filter(fn: (r) => r["calledurl"] =~ /\.php/)
  |> map(fn: (r) => ({ r with _value: r.duration }))
  |> aggregateWindow(every: v.windowPeriod, fn: mean, createEmpty: false)

This looks like it’s working.

3

Hello @Voodoom,
I might offer the following suggestion:
To specify a different column to aggregateWindow() on rather than using map()

  |> aggregateWindow(every: v.windowPeriod, fn: mean, createEmpty: false, column: "duration")

Thanks for sharing our solution!

4

@Anaisdg

Thanks for your reply, sorry I missed it out.

I tried to follow your instruction:

from(bucket: "test")
  |> range(start: v.timeRangeStart, stop: v.timeRangeStop)
  |> filter(fn: (r) => r._field == "calledurl" or r._field == "duration")
  |> pivot(
    rowKey:["_time"],
    columnKey: ["_field"],
    valueColumn: "_value"
  )
  |> filter(fn: (r) => r["calledurl"] =~ /\.php/)
  |> aggregateWindow(every: v.windowPeriod, fn: mean, createEmpty: false, column: "duration")
  |> yield(name: "duration")

But the graph is flat, am I missing anything important?

5

Hello @Voodoom,
I’m not sure I don’t know what your data looks like to know if a flat line makes sense. Maybe you can share some input data please?

6

How are you redirecting the access logs into influx? I think I would like to still keep access logs, but for some virtual hosts I like to experiment with some real time data, before implementing server side logging.
Did you do some research between using time series db or graph dbs?