Get specific timestamp data from log

Tell us what’s happening.
I am trying to search log for a specific timestamp e.g between 12:00 to 13:00 on December 16th. I would also like to know how to increase this timestamp from one hour to specific period

  • Describe the problem, include screenshots and error messages where applicable
    I can only see data for 12:58 timestamp not before that. I know that there are data available in log before 12:58
  • Provide the steps to replicate the issue
    1> Run below query in Query Builder and see result.
    SELECT * FROM Log WHERE servicecomponent =‘abc-xyz’ AND hourOf(timestamp) = ‘12:00’ SINCE ‘2020-12-16 00:00:00’ until ‘2020-12-17 00:00:00’ LIMIT MAX
  • Which versions of affected components are you using?
    I am using NewRelic One
  • Provide Code/Query snippet (with hints about how to format)

Share some additional context.

  • Steps taken to resolve the issue. What docs etc. have you looked at? What did you try?
    I found query from this article
    Get count of sessions at specific time period during a month
  • What should we know about what you see with New Relic that may be impacting the problem?
  • What should we know about your run time environment that may be impacting the problem?

HI, @saurabh.kumar9: You might try adding a WITH TIMEZONE clause to your query, to make sure you are querying the correct time zone.

1 Like

Hello @philweber, Thanks for your response. I tried timezone but it is still not working.
SELECT * FROM Log WHERE servicecomponent =‘abc-xyz’ AND hourOf(timestamp) = ‘12:00’ SINCE ‘2020-12-16 00:00:00’ until ‘2020-12-17 00:00:00’ with TIMEZONE ‘Europe/London’ LIMIT MAX

What if you use the Logs UI: https://docs.newrelic.com/docs/logs/log-management/ui-data/explore-your-data-log-analytics. Can you find it there?

yes If I use log UI then I could find those logs.

Is that sufficient for you, or do you need to use a NRQL query to find the log?

I would like to use NRQL as it gives me flexibility to change code on fly and also see data in tabular format

In that case, try removing the WHERE servicecomponent = 'abc-xyz' part of your query. Perhaps it is filtering out the log messages you want.

Not really as I use same filter in Log UI i.e. servicecomponent:“abc-xyz”. I also tried removing filter but still no luck.
@philweber I just added one more filter and it shows data from 12:28 and 12:38. However, I would like to see whole log during this time period.
SELECT * FROM Log WHERE servicecomponent like ‘%abc-xyz%’ AND logLevel = ‘ERROR’ AND hourOf(timestamp) = ‘12:00’ SINCE ‘2020-12-16 00:00:00’ until ‘2020-12-17 00:00:00’ LIMIT MAX

Sorry, I do not have any more suggestions. Hopefully a support engineer will join the discussion soon to help you troubleshoot.

@saurabh.kumar9 Your query is fine…I think the problem is the size of the “limit MAX”…I think that command will return only 2000 lines…so if you have more entries in the log…that data will be “chopped”…I would like someone from New Relic support team to confirm my theory…but that is what I get on my env…(only 3 minutes worth of data)…even the window is for 1 hour

3 Likes

Thank you for the great explanation @gjajka!