Hello, we wanted to get the sum of the latest values of a custom event, our current NRQL is the following:
SELECT latest(amount) FROM ActiveUsers FACET sitename SINCE 1 WEEK AGO
What we wanted is something like this:
SELECT sum( latest(amount) ) FROM ActiveUsers FACET sitename SINCE 1 WEEK AGO
Is there a way to do that in NRQL?
@MAURCIOR I am not sure that exact query is possible. In order to better understand would you provide a use case in which you would need the sum of your latest custom event values? I really want to understand your goals here—looks interesting!
With a little more context there could be a solution. Let me know!
The Collection ActiveUsers holds registers of the number of active users from several different sites and we can have a few dozen sites here. Those values are not necessarily reported in a regular interval of time like every minute but, the last value reported by a site is the one to be considered the current one.
We need to have a quick overview of the total of users that are currently active on all our sites. The best workaround that we found for this issue was use an area chart to have an idea of this total but, we still without a way to visualize the exact value. This is our area chart query:
SELECT latest(amount) FROM ActiveUsers FACET sitename SINCE 1 WEEK AGO TIMESERIES
We just get the result of this query and choose to show it as a area chart and it gives us an idea of total.
Do you have a better solution for us?
Hi @MAURCIOR
Currently, assuming your amount is an order value, you are requesting the latest order value for each site If you are after the current total number of users on a site you can use:
SELECT uniquecount(session) FROM PageView SINCE 5 seconds ago FACET appName
For a breakdown of the total number of users on each site over a week you can use:
SELECT uniquecount(session) FROM PageView SINCE 1 Week ago FACET appName TIMESERIES
Hi Stefan,
Sorry, the usage of the term “Site” might have caused some confusion here: This ActiveUsers is a custom event with data about our backend activities and the term Site here refers to our environments, it does not have to do directly with any frontend activity so, we can’t use the PageView here.
Ok so the amount is the a running count of the total number of users on an environment? If so then you wouldn’t need the sum and it would not be possible to perform mathematical functions on that attribute over a period of time.
The attribute amount from ActiveUsers is the number of active user in a specific site in a given moment. We need to sum the values of all the sites.
@MAURCIOR - If I’m understanding you, unfortunately I’m not sure it’s going to be possible to get the information you’re looking for directly in Insights, largely due to the irregular nature of your stats reporting.
If you instead had predefined regular intervals that you were reporting from Insights, it may be possible to set your timeseries interval such that you only had one such event reporting in the timeseries period in question. The frequency of the reporting events would need to be tweaked depending on the number of sitename types you are wanting to sum, basically to keep the total number of buckets below the 360 buckets for the time period in question. If you were reporting these stats every 30 minutes (which is the time interval for 1 week of data) you could do the visualization as a stacked chart to enable you to view your total users broken down by sitename. That’s the best idea I can come up with to try and display the data you are looking for. That really doesn’t sound much different from what you currently are running but I’m not quite sure of your data structure to quite understand why this isn’t working out. Are you trying to get an exact numeric data value for the sum or something else?
Yeah, the original idea was to get the exact data value for the sum but, as I didn’t receive any feedback from who is consuming this information, I guess that my solution is kind of solving the problem for now. In any case, thanks for your answer.
I would not consider this an irregular request. It’s asking for one extra layer of aggregation on top of granular snapshot data.
If each data point represents a point in time snapshot of 1 data center and its user count, then the sum of the most recent snapshot of each datacenter is the total users.
I would consider these types of datapoints to be “gauges.” We’re not talking about events here but state of the system. The most recent measure and only the most recent measure of each facet is the current state of that facet. Is this type of metric not intended for this system?
sum(latest) should not be a difficult computation. Temporarily store the latest values and then sum them using the same facet grouping. This doesn’t seem out of the ordinary compared to some of the other compound aggregations possible.
Thanks for the extra feedback - I’ll pass that on to the product team for you.
I think the methodology you note makes lots of sense here. So that’ll be passed over to the team as well! 
Unfortunately granular data doesn’t play well with only 1 aggregation level in general when it comes to “point-in-time” gauge measures. That need to use the “latest” aggregator on these types of metrics will mean you’re always spending 1 aggregation just getting to the list of all most recent events. Maybe if there were a special view for types denoted as a Gauge? With this view you denote what your key is. In other words, what makes a gauge measure distinct (what you facet on). Then this view would only contain the latest record of each. Thus you’re always working with your current state at all times.
Take this example: { Region=“US-West” DataCenter=“San-Diego” UserCount=“100” }
Say you have even one more level of granularity. If I want the count of users in a region then I’d need latest usercount facet by Region and DataCenter. That gives me the latest count from each center. But I would then have to SUM faceted by Region after that to get a user count per region.
My only option right now would be to have the system create separate metrics at the region level, or just use a completely different reporting system for region data.
