APM Transactions by Status Code per Day

I recently received a question about generating CSVs for APM Transactions by Status Code, and I thought it would be a good thing to share with the community:

I have been looking around for a while in the Insights dashboard and APM, and trying to see if there is an intuitive way to pull the following report into CSV format. I would like the number of 404 Transactions (from APM) per day, that occurred for as far back as I have data retention. Similar to an SLA report exported as a csv for a key transaction, but with more history.

I will frequently refer people to @cwhite’s excellent Relic Solution: Using Insights to create the Ultimate SLA report as a good starting point for recreating SLA data. Additionally, always check out Useful Insights NRQL queries library for ideas.

For a query like this, I might suggest:

SELECT count(*) as '404 Responses' FROM Transaction WHERE  httpResponseCode like '404' since 1 month ago FACET dateOf(timestamp) limit 31

You can easily search for much longer time ranges with something like SINCE 12 months ago, specific UTC timestamps like 'SINCE ‘2018-11-01’, or Unix timestamps like SINCE 1541059261.

The LIMIT clause will support a maxmium of 1000 results, but that should get you through most queries by day. It’s a little shy of three years in one CSV.

Finally, you can Export Insights data to CSV file.

How would you approach this query?