NRQL Querying Beyond Max Limit

Hi

I am using a NRQL query to get non prod synthetics usage percentage and duration but the issue is
we have around 3000+ monitors NRQL max limit is 2000 only
Query Used:

FROM SyntheticCheck SELECT percentage(count(*), WHERE result = ‘SUCCESS’) SINCE 52 weeks ago FACET monitorName,duration LIMIT 2000

how how could i get the remaining do we something like ‘Between 2000 and 3000’

we will be using this in nerdlet

Faceting on duration?

You are doing this in a nerdlet? You can use nerdgraph api and use the pagination when needed.

I have been looking for how NRQL can iterate through the dataset exceeding the MAX Limit, via the RESTful API, or directly in Query builder
A simple query like “SELECT * FROM SyntheticCheck SINCE 1 hour ago LIMIT MAX”

You can use offset

LIMIT count OFFSET count

@6MM Thx for prompt response! I am aware of this, and tried to couple of the combination in order to iterate through whole dataset.
For instance, “SELECT * FROM SyntheticCheck SINCE 1 day ago LIMIT MAX OFFSET 2000”. My intention is to leverage LIMIT 2000 results per request, and incrementally to go through all. However, the request got the error “External Service ‘NRDB’ execution resulted in 400 - cause: Max value for OFFSET+LIMIT is 2000”, so this appears that OFFSET is to offset within the size of the specified LIMIT size, which is also what is stated in the document if I understand correctly - https://docs.newrelic.com/docs/query-your-data/nrql-new-relic-query-language/get-started/nrql-syntax-clauses-functions#sel-offset.
I thought it is something basic for query language, just have not been able to figure out how NRQL works to support this. Is there any other way for iterating large dataset?

Nerdgraph and the api’s that paginate.

@6MM Thx for pointing out that! I have tried following the documentation for GraphQL, but am facing some blocking issues:

  • the GraphiQL UI does not work without API key
  • there is no option in account settings to create personal API key

Also regarding NRQL, is it possible to workaround with something like timestamp and/or other internal columns/keys as synthetic ID to allow iterating large dataset?

Hi, @joshua.hua: Sure, you can do something like this:

FROM SyntheticCheck 
SELECT percentage(count(*), WHERE result = 'SUCCESS') 
WHERE monitorName LIKE 'a%'
SINCE 52 weeks ago 
FACET monitorName, duration 
LIMIT MAX

Do a query for each letter of the alphabet, and you have 26 pages.

1 Like

@philweber

This solution is retrieving duplicate monitor names however to get all the Synthetic monitors we need to query 26 times which is not feasible please fix offset limit issue so that we can query what is required and we should also have the possibility to retrieve total no of synthetic monitors for that account.

1 Like

I’m curious about your need to surface this much data. If you don’t mind me asking what are you hoping to do with this much data? Are you trying to export it to PDF or some other need?

The reason I ask is that it often comes up that people want to use NR as a reporting tool vs a monitoring tool. I think we have all been in this seat.

For example, export to data warehouse for data mining / analysis, and also longer retention.

In that case I’m guessing you can automate using the api’s. If using the api’s I don’t think there will be a serious issue with multiple calls.

Out of curiosity are you both from the same company or are you hijacking the thread?

I have been using RESTful API call, but encountering the same issue with MAX Limit. For each request, the API only returns 2000 results, and there appears that NRQL has no easy way to continue to get the rest of results, something like db query’s offset or pagination.

We are from different company, I was searching the forum, happened to find this thread, and believe this is the same issue.

Ok, I asked a question in response to Sneti and you answered. That can get confusing.

I don’t see any real barriers for either of you when using multiple calls. Phil offered a simple approach, but a person can also use many other approaches. Some of the api’s have volume limits e.g. https://synthetics.newrelic.com/synthetics/api limits to 3 requests per minute.

@6MM With the LIMIT MAX, making multiple calls is certainly the only option. The key question is how the subsequent calls can be constructed for NRQL API to return deterministic results with no duplication/gap.

Since I don’t know what you are doing exactly its hard to say. If you are truly extracting for retention reasons I suggest you reach out to support or your rep. There have been at times some back end options to dump data.

I don’t think NRQL is meant to return large sets of data. I get that’s what you want it to do.

Perhaps you can open another thread wish your specific use case?

@6MM Thx for your reply! We have already opened a ticket in your support system last week. I was hoping there is already a working solution out there :slight_smile:

Still, rather than take other this thread if you have an opportunity you can open another thread that talks more about your use case. There people could help you with more details.

@6MM

We need to display the synthetic monitors related to our account(3000+) in our non pord region in a nerdlet which takes NRQL as an input as of now are unable to do that due to limit restriction

Please enable offset limit to be used in a appropriate way so that we can get all the synthetic monitors associated to the account with required data like duration, name, percentage.

The above is intended to find out which has least percentage of SUCCESS and perform required actions from managerial stand point

Hi, @SNeti: @6MM is not a New Relic employee, and I am not on the product team. We cannot do anything to change the way NRQL works; we can only suggest ways to work with the product as it currently exists.

1 Like