Divide result from two selects

[klaudia.dziwon]

Hi,
I have selects looking like this:
SELECT count() FROM CustomEvent WHERE type=‘customEventType’
SELECT count() from Transaction where appName=‘myAppNAme’ and name LIKE ‘%/my-endpoint%’

And I would like to get result as firstSelectResult/secondSelectResult. How can I achieve that

[philweber]

Hello, @klaudia.dziwon: You may be able to use filter():

SELECT 
  filter(count(*), WHERE type = 'customEventType') / 
  filter(count(*), WHERE appName = 'myAppName' AND name LIKE '%/my-endpoint%')
FROM CustomEvent, Transaction

[nate-gather]

Hey guys, I’m trying to use this example but it doesn’t seem to work for this pair of sources (PageAction and Metric):

I have

SELECT
  filter(count(*), WHERE eventtype() = 'PageAction') /
  filter(average(newrelic.timeslice.value), where metricTimesliceName = 'Custom/gameserver.health_users')
FROM PageAction, Metric WHERE appName LIKE '%' TIMESERIES auto

Which doesn’t work (with no error), while

SELECT
  filter(count(*), WHERE eventtype() = 'PageAction')
FROM PageAction, Metric WHERE appName LIKE '%' TIMESERIES auto

and

SELECT
  filter(average(newrelic.timeslice.value), where metricTimesliceName = 'Custom/gameserver.health_users')
FROM PageAction, Metric WHERE appName LIKE '%' TIMESERIES auto

Do work, as does dividing by something else:

SELECT
  filter(count(*), WHERE eventtype() = 'PageAction') /
  filter(count(*), WHERE eventtype() = 'Metric')
FROM PageAction, Metric WHERE appName LIKE '%' TIMESERIES auto

Interestingly, if I replace the / with a , in the original query, only the second value (users) shows up, despite it working alone.

I’ve combed the forum for every example of division and can’t figure out why this doesn’t work

[philweber]

Hi, @nate-gather: I don’t think it is possible to query events and metrics in a single query. There is another question about it here:

Data is not properly collected when 2 tables are used in FROM

SELECT filter(count(*), where request.uri like ‘/%/health’), average(apm.service.transaction.duration) FROM Metric, Transaction WHERE appName like ‘%’ SINCE 30 MINUTES AGO Hi, @lakshmi.pabbisetti: I don’t think it is possible to query metrics and events in a single query. If you want to display throughput and latency, you do not need to query metrics; you may simply do this: SELECT filter(count(*), WHERE request.uri LIKE '/%/health'), average(duration) FROM Transaction

[nate-gather]

Ah, well that explains it haha . Thanks for the quick response @philweber!

I’m trying to get the ratio of a certain PageAction to the number of users connected to the backend (a gauge Metric posted periodically from all the hosts). Is there any way to do this? I can measure each individually just fine, just can’t measure (and crucially, alert on) the ratio

[philweber]

Can you use uniqueCount(yourUniqueUserId) FROM Transaction to get the number of backend users, rather than a metric?

[nate-gather]

I’m not familiar with the Transaction table but it seems like it’s geared towards http apps, but we’re using websockets. Also, our app is such that you’re not always connected to the backend when you’re on the page (so I can’t just use page views or active sessions), and what I’m trying to measure is the fraction of people who are disconnected for >5s (as observed by the client) for given window (5 mins)

Sounds like a workaround is to just publish a separate PageAction every 5 mins for ‘intending to be connected’ and divide the disconnects by that total, but that’s the best I can come up with

[hross]

Hey @nate-gather -

Just following up on this to see if that workaround worked out for you?

[nate-gather]

I ended up doing this

Sounds like a workaround is to just publish a separate PageAction every 5 mins for ‘intending to be connected’ and divide the disconnects by that total, but that’s the best I can come up with

[hross]

Got it - glad to hear that you got something workable!