Response time - log table

Hi, could anyone give me an idea how to extract the response time of my routes from the “REQUEST” column with reference to the “Request Time” column?

That is, I would like the response time, for example, in the ‘REQUEST’ column I have a route, with the path

/commerce/store02/ would like to get the Request Time for this route as well as others.

For example the following routes, in the column of (REQUEST), and I would like to create a query that I could return the response time of each route below.
/commerce/store02
/commerce/store01
/commerce/image/production/…/…/…/…/…
/staging/img/mickey/…/…/…/…/…

SELECT 
average(request_time) 
FROM Log 
WHERE request = 'GET /commerce/store02' 
TIMESERIES

tried doing it that way, but it returns "no value"

SELECT 
rate(sum(request_time), 1 minute) 
from Log where request = 'commerce/store02'/' since 60 minutes ago

I did it that way too, and nothing returns me.

Hi, @ronald.silva: Execute the following query:

SELECT keyset() FROM Log

Look at the result, and make sure request_time is a numeric key, not a string. If it’s a string, you will not be able to calculate the average or sum.

1 Like


the attribute (request_time) is inside stringKeys

Yes, so you will not be able to perform calculations (average, sum) on response time unless you change how your logs are parsed.

I am curious why you are using logs to get transaction response times, rather than instrumenting your application with New Relic APM.

It’s because what I’m collecting is not APM or application data. I’m getting data from AWS tier infrastructure (S3, Cloud Front, Load Balancer and DNS ROUTE 53).

these transactions I’m getting are logs of queries to my S3, routes from my bucket. For example, the client is looking for an image of an IPHONE, and this image is inside the S3 such as /client/image/494944/iphone_black_11.jpg
then I would like to get the response time from the route /client/image*…

Ah, OK. Well as I said, if you want to perform calculations on response time, you will need to parse it as a number, rather than a string.

Before getting too deep into this you may want to first see if you can get close to the metrics you want by integrating your aws account with New Relic and then fetching the s3 bucket metrics from datastoresample or Metric.

https://docs.newrelic.com/docs/integrations/amazon-integrations/aws-integrations-list/aws-s3-monitoring-integration/

1 Like

It’s already integrated friend, I took the integration data, but it’s basic data, and then I’m building something aimed at a deeper analysis of the data, in addition to the integration data.

Thank you very much, I will perform the procedures. Thank you so much for your time, thanks with all the best for your support.

Let us know how that works out for you and if you need anything else!

Hi @ronald.silva, I have some good news for you. There is a simple option for this, and it doesn’t need any changes to log parsing. You can simply cast the string to a numeric before doing a calculation!

e.g.

SELECT 
average(numeric(request_time)) 
FROM Log 
WHERE request = 'GET /commerce/store02' 
TIMESERIES

I highly recommend that you check out the NRQL Lessons app (available for free in the App Catalog in New Relic One) which runs through a lot of NRQL tips and tricks.

Hope this helps!

1 Like

Thanks for your feedback, I tried to try to get this information, but no information is returned. The query works, but nothing is displayed on the screen.

Returning, to inform you that I can perform the calculation I needed in the LOG table. Thanks to everyone and especially to our friend @Nlidbury for his help with the “string to numeric” convention. It was exactly what I needed.

SELECT
average (numeric(request_time)) as 'Media',
median (numeric(request_time)) as 'Median',
percentile (numeric(request_time)) as 'p95'
FROM log
WHERE label.app = 'proxy'
And request != 'GET / server-status HTTP / 1.1'
And `request` LIKE '% example/store02/test/teste%'
LIMIT MAX
2 Likes

Great to hear @ronald.silva!