Group by seems to be not working properly

grafana
influxdb
#1

Hi, it looks like I have unexpected and “horrible” duplicates in my Grafana dashboard and I cannot figure out why. For each collected metric I am expecting a single grafana entry and I get multiples instead… e.g. one for Avg, Counts, Min and median. Another one for 90st and 95th percentile and yet another one for 99th percentile(see the attached example)

I use the following query to fill the grafana dashboard

SELECT count(responseTime) as Count, mean(responseTime) as Avg, min(responseTime) as Min, median(responseTime) as Median, percentile(responseTime, 90) as “90%”,percentile(responseTime, 95) as “95%”,percentile(responseTime, 99) as “99%”, max(responseTime) as Max, (sum(errorCount)/count(responseTime)) as “Error Rate” FROM “requestsRaw” WHERE $timeFilter AND ( requestName = ‘LendingPage’ OR requestName = ‘CustomerSearchAndExistingLendingApplicationsPage’ OR requestName = ‘NewLendingApplicationPage’ OR requestName = ‘CustomerStructurePage’ OR requestName = ‘FacilitySummaryPage’ OR requestName = ‘FacilityDetailsPage’ OR requestName = ‘CreditDecisionPage’ OR requestName = ‘LoadLimitPage’ ) GROUP BY requestName

Any help will be much appreciated
Thanks
Fab

#2

@Fab_S Can you please share the schema of the data in InfluxDB or a couple of sample points? Its hard for me to figure out what’s going on there without that.

#3

Hi @jackzampolin , thanks for you reply. I am not sure what you mean by “schema” but here it is a couple of screenshots related to SHOW series. Hope this help… Thanks again

As a new user i cannot post more than one picture but if I run a query such as the following I only get one record and this does not explain the multiple record in grafana…

SHOW series WHERE requestName=‘FacilitySummaryPage’

#4

@Fab_S I need to see the output of SHOW TAG KEYS, SHOW FIELD KEYS, and SHOW MEASUREMENTS.

#5

@jackzampolin sorry for spamming but I am not allowed to post all screenshots in a single reply/post

#6

@jackzampolin

#7

@jackzampolin

#8

Have you tried adding a GROUP BY time(10s) in the query?

#9

the group by time request works but i need to group by requestName as i want to see the Pages stats… if I group by both I get an error

#10

@Fab_S What error does that give you?

#11

{
“error”: “error parsing query: only time and tag dimensions allowed”,
“message”: “error parsing query: only time and tag dimensions allowed”
}

#12

@Fab_S Well it sounds like you have a syntax error there somewhere. Are you sure you added that at the bottom?

Just a note, you can imbed code blocks in your responses with Github Markdown format in this forum. It makes it much easier to read the code blocks. For example your query at the top is much more readable when formatted. I’ve also added the GROUP BY time(10s) in the proper place

SELECT 
    count(responseTime) as Count, 
    mean(responseTime) as Avg, 
    min(responseTime) as Min, 
    median(responseTime) as Median, 
    percentile(responseTime, 90) as "90%",
    percentile(responseTime, 95) as "95%",
    percentile(responseTime, 99) as "99%", 
    max(responseTime) as Max, 
    (sum(errorCount)/count(responseTime)) as "Error Rate" 
FROM "requestsRaw" 
WHERE 
    $timeFilter AND 
    (
        requestName = 'LendingPage' OR 
        requestName = 'CustomerSearchAndExistingLendingApplicationsPage' OR 
        requestName = 'NewLendingApplicationPage' OR 
        requestName = 'CustomerStructurePage' OR 
        requestName = 'FacilitySummaryPage' OR 
        requestName = 'FacilityDetailsPage' OR 
        requestName = 'CreditDecisionPage' OR 
        requestName = 'LoadLimitPage' 
    ) 
GROUP BY requestName, time(10s)