Subtract number of custom events with facet

Hi all,

I have some custom events with this format that groups and count by ‘topic’. This is one of the queries.

SELECT count(newrelic.timeslice.value) FROM Metric WHERE appName = 'service' WITH METRIC_FORMAT 'Custom/SERVICE/**write**/{topic}' FACET topic TIMESERIES 10 minutes SINCE 1 day ago

And what I would like to do is subtract that counter with another query pretty similar.

SELECT count(newrelic.timeslice.value) FROM Metric WHERE appName = 'service' WITH METRIC_FORMAT 'Custom/SERVICE/**read**/{topic}' FACET topic TIMESERIES 10 minutes SINCE 1 day ago

Is there any way I can get the difference between those queries (count of writes - count on reads) for each ‘topic’ ? Thanks in advance

Hi, @spintos: It looks like you are creating custom metrics, not events, but in any case: You might try using the filter() function:

SELECT filter(..., WHERE metricName LIKE 'Custom/SERVICE/write/%') - filter(..., WHERE metricName LIKE 'Custom/SERVICE/read/%')

I tried with the filter function, but its returning always 0. Also, I see just one metric named “Metric” and not one for each ‘topic’

SELECT filter(count(newrelic.timeslice.value), WHERE metricTimesliceName LIKE 'Custom/SERVICE/write/{topic}') - filter(count(newrelic.timeslice.value), WHERE metricTimesliceName LIKE 'Custom/SERVICE/read/{topic}') FROM Metric WHERE appName LIKE 'service' FACET topic TIMESERIES 10 minutes SINCE 1 DAYS AGO

However, if I do the same query but writing a specific topic, at least I get the right number from count (but still shows “Metric” and not the name of the topic)

select filter(count(newrelic.timeslice.value), WHERE metricTimesliceName LIKE 'Custom/SERVICE/write/topic-example') - filter(count(newrelic.timeslice.value), WHERE metricTimesliceName LIKE 'Custom/SERVICE/write/topic-example') FROM Metric WHERE appName LIKE 'service' TIMESERIES 10 minutes SINCE 1 DAYS AGO 

You may have more success if you send events rather than metrics: https://docs.newrelic.com/docs/data-apis/custom-data/custom-events/report-custom-event-data/#ways