More than 1000 rows in canwas

Try to count call from CISCO CDR. I have QUERY that grouping all call by globalCallID_callId and I need to COUNT unique globalCallID_callId - but I have much more records than 1000. My SQL is:

SELECT globalCallID_callId FROM "cucm-cdr-full" GROUP BY globalCallID_callId

and I need to count it based on timestamp.

the essql operator in Kibana Canvas also takes a count argument (here's its doc section) please up it. You may bump into another limitation at 10 000 rows though.

Also, if possible, do the COUNT aggregation with the SQL query, instead of doing it with the Canvas expression language, it has various benefits. Reference to our SQL SELECT is here

I know that I can just count the rows, for example
SELECT COUNT (*) AS TOTAL FROM cucm-cdr-full, but I need to group them by the globalCallID_callId field before counting, since one phone call can contain several records with the same globalCallID_callId (call transfer from subscriber to subscriber and so on). If it was possible to use a nested query, calculate what is already aggregated, but I don't understand how.

Could you use the SELECT ... GROUP BY for your case?
Also, there's a limited form of subqueries (SELECT within a SELECT) now, though I think what you need is the GROUP BY. Apologies if you considered but rejected SELECT ... GROUP BY

Did you try this ?:

SELECT COUNT(DISTINCT globalCallID_callId) AS CT FROM "cucm-cdr-full"

This topic was automatically closed 28 days after the last reply. New replies are no longer allowed.