How to create a timeseries line chart from the results of a nested query?

Hello! I have a nested query similar to this…

SELECT requestPath, errors FROM (SELECT percentage(count(), WHERE statusCode = 403) as errors FROM MobileRequestError, MobileRequest FACET CASES(WHERE requestPath LIKE ‘/iphone5x-web/proxy/customers/~/profiles/%/alert-subscription%’ AS '/iphone5x-web/proxy/customers/~/profiles//alert-subscription’, …) OR requestPath TIMESERIES) WHERE errors > 3 WITH TIMEZONE ‘America/New_York’ SINCE 30 minutes ago

The inner query returns the failure rates for each of the requestPath’s, faceted by the requestPath’s.
The outer query is intended to filter the results from the inner query to only show the requestPath’s with a failure rate > 3%.

Based on the outcome, I can tell the query is returning the correct requestPath’s that have greater than 3% failure rates. But the data is being displayed as a table separated into columns(timestamp, requestPath, errors(rate)), with the requestPath column being blank. I would like to have this query returned as a timeseries line chart labeled by requestPath, but the all the options for chart type are greyed out except table. Is there any way to accomplish this?

This is the outcome…

Add timeseries to the outer.

Hey @6MM , thanks for taking a look at this! So I added TIMESERIES to the outer query like so…

SELECT requestPath, errors FROM (SELECT percentage(count(), WHERE statusCode = 403) as errors FROM MobileRequestError, MobileRequest FACET CASES(WHERE requestPath LIKE ‘/iphone5x-web/proxy/customers/~/profiles/%/alert-subscription%’ AS '/iphone5x-web/proxy/customers/~/profiles/ /alert-subscription’, …) OR requestPath) WHERE errors > 3 TIMESERIES WITH TIMEZONE ‘America/New_York’ SINCE 30 minutes ago

And now I am getting this error in response…

I also tried having timeseries on both the inner and outer queries , but still get the same error in response.

You need to use a function, such as count or sum, to produce a chart in TIMESERIES format. This may work or need a bit more tweaking.

SELECT sum(errors) FROM (SELECT percentage(count(*), WHERE statusCode = 403) as errors FROM MobileRequestError, MobileRequest FACET CASES(WHERE requestPath LIKE ‘/iphone5x-web/proxy/customers/~/profiles/%/alert-subscription%’ AS '/iphone5x-web/proxy/customers/~/profiles/* /alert-subscription’, …) OR requestPath) WHERE errors > 3 **TIMESERIES** WITH TIMEZONE ‘America/New_York’ SINCE 30 minutes ago FACET requestPath

2 Likes

At times you need the timeseries inside and outside. Depends on your needs. That the value of the sub aggregation. You can get the max of the timeseries points from the inside if you do not include a timeseries on the outside… for example.