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

QUERY for getting Country Information

browser

#1

Hi,
I am running below query to get WEBSITES pages information and their performance, but I also need COUNTRY information like from which country the page is.

Please help me how i can get country information with the below query.

SELECT average(duration) as Avg Total Duration, average(duration)-average(backendDuration) as Avg Frontend Duration, average(backendDuration) from PageView where appName like ‘www.tena%’ and pageUrl like ‘%tena%’ facet pageUrl limit 1000 SINCE ‘2017-07-17 00:00:00 cet’ until ‘2017-10-24 00:00:00 cet’

Thanks
Nafees


#2

Hi @khan.nafees.ext - The attribute you require is countryCode. Unfortunately the country name is not stored as an attribute in the PageView event.


#3

Hi, @khan.nafees.ext: The countryCode attribute returns the geolocated country based on the user’s IP address. But since you are querying average performance over a three-month period, the requests will likely have come from a number of different countries; for which request do you want to know the country of origin?

If you wish, you may FACET by countryCode instead of pageUrl:

SELECT average(duration), average(backendDuration) 
FROM PageView 
WHERE pageUrl like '%tena%' 
FACET countryCode

It is not possible to FACET on multiple attributes within the same query; you will either have to use one query to group by URL and another to group by country, or use a linked widget to filter your dashboard by country.


#4

Thanks for quick help!
My requirement is, I want to know the performance of web pages and location of these pages.
So that I can know, which webpage is performing poorly from which location?
can you please provide me any such query?

Thanks in advance!

Regards,
Nafees


#5

The following query will show you the slowest individual page loads by URL and country:

SELECT pageUrl, countryCode, duration 
FROM PageView 
WHERE pageUrl LIKE '%tena%' 
SINCE 1 week ago LIMIT 100 
ORDER BY duration DESC

If you want to see average load time, you can group by URL:

SELECT average(duration) 
FROM PageView 
WHERE pageUrl LIKE '%tena%' 
SINCE 1 week ago LIMIT 100 
FACET pageUrl

…or country:

SELECT average(duration) 
FROM PageView 
WHERE pageUrl LIKE '%tena%' 
SINCE 1 week ago LIMIT 100 
FACET countryCode

But not both. The workaround is to create a dashboard with both of the above queries and link them to the dashboard, so that when you select a country, you see the slowest pages for that country:

…and when you select a page, you see the slowest countries for that page:


#6

You could also use some Filter() functions if you have a manageable set of applicable country codes.


#7

@philweber - Has a new feature been released? ORDER BY? :wink:


#8

It’s undocumented, but I tried it and it worked! :slight_smile:


#9

So it does! The editor complains about the ORDER and underlines it in red but it executes correctly :+1:


#10

ORDER BY will work for SELECT * queries and queries that are SELECT FROM an attribute.

It is not currently supported for queries using aggregates. The NRDB Team will be updating their documentation in the near future to reflect this.