Query to get the count of transaction as per URL

I want to check the count of each status code for specific URL as shown below

URL /StatusCode         200   400   404   401

abc/url_1    1     1     0     1
abc/url_2    2     2     0     1
abc/url_3    5     3     1     1

This is possible with subquery but subquery are not supported so how i achieve this?

How about:

SELECT count(*) 
FROM Transaction 
WHERE appName = 'Your Application'
FACET request.uri, httpResponseCode

Than you
Tried this already but output is different than i expected

Can you say more about? Hard for others to know what you mean by your response there.

Based on his initial post, I think he wants a table with response codes as columns and URLs as rows. I don’t know how to do that with NRQL.

Ah. You use filter().

Filter(count(*), where httpResponseCode = 500), Filter(count(*), where httpResponseCode = 501)...

Thanks! @Pavan.Ambhure here is the complete query, and a screenshot of the output:

SELECT 
  filter(count(*), WHERE httpResponseCode = '200') AS '200', 
  filter(count(*), WHERE httpResponseCode = '206') AS '206', 
  filter(count(*), WHERE httpResponseCode = '302') AS '302', 
  filter(count(*), WHERE httpResponseCode = '403') AS '403', 
  filter(count(*), WHERE httpResponseCode = '404') AS '404', 
  filter(count(*), WHERE httpResponseCode = '405') AS '405', 
  filter(count(*), WHERE httpResponseCode = '500') AS '500' 
FROM Transaction 
FACET request.uri 
SINCE 1 day ago
LIMIT MAX

3 Likes

Also FYI, subqueries are (sort of) supported: Nested aggregation: make ordered computations in a single query | New Relic Documentation.

Thank you for your reply @philweber @6MM

Let us know if you need anything else @Pavan.Ambhure!