Individual intervals for each custom query or possibility to only send custom metrics

[LLHogia]

Description

We have a huge amount of data within our SQL Server. Some queries can only be executed on a specific time interval. We cannot let the integration perform the same query each minute all the time. Some queries should only be executed on the below intervals as an example:

• 60s
• 120s (2m)
• 300s (5m)
• 3600s (1h)

Reason would be that some queries takes a lot of time to execute so we need to be able to specify the interval for each query. Otherwise it's like we're DDoS attacking our own servers with heavy queries.

The only workaround we have right now is to setup different integrations like below:

integrations:
  - name: nri-mssql
    env: 
      HOSTNAME: ST-AG-TEST
      USERNAME: HIDDEN
      PASSWORD: "HIDDEN"
      PORT: 1433
      CUSTOM_METRICS_CONFIG: 'C:\Program Files\New Relic\newrelic-infra\integrations.d\mssql-custom-query-2min.yml'
      ENABLE_BUFFER_METRICS: false
      ENABLE_DATABASE_RESERVE_METRICS: false
      METRICS: false
      INVENTORY: false
    inventory_source: config/mssql
    labels:
      purpose: MssqlCustomQuery
    interval: 120s

  - name: nri-mssql
    env: 
      HOSTNAME: ST-AG-TEST
      USERNAME: HIDDEN
      PASSWORD: "HIDDEN"
      PORT: 1433
      CUSTOM_METRICS_CONFIG: 'C:\Program Files\New Relic\newrelic-infra\integrations.d\mssql-custom-query-5min.yml'
      ENABLE_BUFFER_METRICS: false
      ENABLE_DATABASE_RESERVE_METRICS: false
      METRICS: false
      INVENTORY: false
    inventory_source: config/mssql
    labels:
      purpose: MssqlCustomQuery
    interval: 300s

To avoid duplicate data ingested by New Relic we have also created a drop rule by selecting our label like below:
SELECT * FROM MssqlInstanceSample, MssqlDatabaseSample WHERE label.purpose = 'MssqlCustomQuery'

So we have 3 integrations in total in our example. 1 main integration where we want the data sent to MssqlInstanceSample and MssqlDatabaseSample. The other two are sending duplicate data but we're dropping everything which isn't MssqlCustomQuerySample.

Acceptance Criteria

Possible to set individual interval for each custom query from the file specified within CUSTOM_METRICS_CONFIG.

Describe Alternatives

Another solution would be to have the possibility to disable data sent to MssqlInstanceSample and MssqlDatabaseSample or just have a setting to only enable MssqlCustomQuerySample for the specific integration used for custom queries. This would also help us a lot. We would like to do this on the server side to avoid unnecessary network traffic and unnecessary queries on our SQL Servers.

Dependencies

N/A

Additional context

N/A

Priority

[Nice to Have, Really Want, Must Have, Blocker]

[workato-integration]

https://issues.newrelic.com/browse/NR-167990

[workato-integration]

https://new-relic.atlassian.net/browse/NR-167990