Your data. Anywhere you go.

New Relic for iOS or Android


Download on the App Store    Android App on Google play


New Relic Insights App for iOS


Download on the App Store


Learn more

Close icon

NRQL Inconsistent Query Result


#1

I wanted to count unique sessions of the ones that users accessed our admin pageUrl vs total unique sessions. Somehow the result isn’t consistent for the total unique sessions using Facet Cases vs. just simple select uniqueCount(session).

SELECT uniqueCount(session) FROM PageView FACET CASES (Where appName = ‘MyApp’ and pageUrl like ‘%home/adminurl%’ as ‘Admin Sessions’, WHERE appName = ‘MyApp’ AS ‘Total Sessions’) SINCE ‘2018-11-16’ WITH TIMEZONE ‘America/New_York’

Total Sessions: 33.3K
Admin Sessions: 4.38K

SELECT uniqueCount(session) FROM PageView WHERE appName = ‘MyApp’ SINCE ‘2018-11-16’ WITH TIMEZONE ‘America/New_York’

35.2K

There are almost 2,000 more for total session in the 2nd query even though they both ran at same time. What am I missing?

Thank you for your help!


#2

Hi @chen_guangyu

I tried simulating the same query on my side and found that the data returned in both the query was the same number of total sessions. Can you try running the query for shorter time duration, say last 30 mins and see if sheds more light on whats going on.

thanks


#3

Hey @chen_guangyu, you would probably have better luck using a filter() function rather than facet cases. To quote an older post of mine:

I’ve got a sticky note on my monitor that says “filter, not facet cases” as a reminder because I often find myself in situations in which I try to use FACET or FACET cases to write a query when what I really needed was the filter function.

The filter function allows you to select on subsets of data, similar to using FACET cases. However, unlike FACET cases, the filter function can have faceting applied to all filtered sections, can take multiple facets, and is not “greedy”. FACET cases are considered “greedy” in that any results that match on a specific case will not be available to be matched on for any subsequent cases.

I would try a query like this:

SELECT filter(uniqueCount(session), WHERE appName=‘MyApp’) AS ‘Total Sessions’, filter(uniqueCount(session), WHERE appName = ‘MyApp’ and pageUrl like ‘%home/adminurl%’) AS ‘Admin Sessions’ FROM PageView SINCE ‘2018-11-16’ WITH TIMEZONE ‘America/New_York’

Another good option would be to use a funnel query. Funnels only pass matched events from one step into the WHERE clause of the following step, narrowing in on data one step at a time. My favorite thing about funnel charts is that they provide information “at a glance”, reducing time to resolution. Here’s an example of a funnel that may meet your needs:

SELECT funnel(session,
WHERE appName=‘MyApp’ AS ‘Total Sessions’,
WHERE appName=‘MyApp’ AND pageUrl LIKE ‘%home/adminurl%’ as ‘Admin Sessions’)
FROM PageView
SINCE ‘2018-11-16’ WITH TIMEZONE ‘America/New_York’


#4

Thank you for your reply. Even with 30 mins, there are still differences - 3.


#5

To optimize query performance, the uniqueCount() function returns approximate results for queries that inspect more than 256 unique values.

You can find Documentation on this here: https://docs.newrelic.com/docs/insights/nrql-new-relic-query-language/nrql-resources/nrql-syntax-components-functions#func-uniqueCount


#6

also you can always see the difference by removing unquie count thay way you will exactly know what else is coming into the query.


#7

Hey @chen_guangyu - Was @babbott & @MKhanna’s information helpful?
Let us know if this hasn’t helped to clear things up for you - or if you have any further questions.


#8

Thanks!

With funnel, I can’t get unique sessions. It’ll return all sessions.

With Filter, it does return matching results. I can only display them as multiple lines of numbers on the dashboard. I wanted to use the bar chart for presentation purpose. But looks like I will have to use filter to get consistent reports.

Thank you for you help!


#9

@chen_guangyu The funnel() function returns unique values of an attribute, so this should be a good fit for your use case. If you run a uniqueCount() query, the results should be consistent with the first step of a similar funnel query.