Issue with creating ES Simple bar chart

[ravitejainje]

Hi All,

I am working on creating an ES Simple bar chart to display the number of tasks for various endpoints.
I need to obtain the data as attached format:

I created an analytical report for one endpoint and started building a dashboard using the analytical report.

/////////////////////////////Original Query///////////////////////////////////////////
SELECT
e.endpointname,
SUM(CASE WHEN t.tasktype = 2 THEN 1 ELSE 0 END) AS 'Revoke',
SUM(CASE WHEN t.tasktype = 14 THEN 1 ELSE 0 END) AS 'Disable',
SUM(CASE WHEN t.tasktype = 12 THEN 1 ELSE 0 END) AS 'Update',
SUM(CASE WHEN t.tasktype = 3 THEN 1 ELSE 0 END) AS 'Create',
SUM(CASE WHEN t.tasktype NOT IN ('2', '3', '12', '14') THEN 1 ELSE 0 END) AS 'Other',
FROM
arstasks t
JOIN
endpoints e
ON t.endpoint = e.endpointkey
WHERE
t.status = 1
AND t.endpoint IN ('9','10')

//////////////////////////Analytical Report Query for one endpoint//////////////////////////

SELECT 

    e.endpointname,

    SUM(CASE WHEN t.tasktype = 2 THEN 1 ELSE 0 END) AS 'Revoke',

    SUM(CASE WHEN t.tasktype = 14 THEN 1 ELSE 0 END) AS 'Disable',

    SUM(CASE WHEN t.tasktype = 12 THEN 1 ELSE 0 END) AS 'Update',

    SUM(CASE WHEN t.tasktype = 3 THEN 1 ELSE 0 END) AS 'Create',

    SUM(CASE WHEN t.tasktype NOT IN ('2', '3', '12', '14') THEN 1 ELSE 0 END) AS 'Other',

'Revoke' as 'Task'

FROM 

    arstasks t

JOIN 

    endpoints e 

    ON t.endpoint = e.endpointkey 

WHERE

    t.status = 1 

    AND t.endpoint IN ('9')

UNION

SELECT 

    e.endpointname,

    SUM(CASE WHEN t.tasktype = 2 THEN 1 ELSE 0 END) AS 'Revoke',

    SUM(CASE WHEN t.tasktype = 14 THEN 1 ELSE 0 END) AS 'Disable',

    SUM(CASE WHEN t.tasktype = 12 THEN 1 ELSE 0 END) AS 'Update',

    SUM(CASE WHEN t.tasktype = 3 THEN 1 ELSE 0 END) AS 'Create',

    SUM(CASE WHEN t.tasktype NOT IN ('2', '3', '12', '14') THEN 1 ELSE 0 END) AS 'Other',

'Disable' as 'Task'

FROM 

    arstasks t

JOIN 

    endpoints e 

    ON t.endpoint = e.endpointkey 

WHERE

    t.status = 1 

    AND t.endpoint IN ('9')

UNION

SELECT 

    e.endpointname,

    SUM(CASE WHEN t.tasktype = 2 THEN 1 ELSE 0 END) AS 'Revoke',

    SUM(CASE WHEN t.tasktype = 14 THEN 1 ELSE 0 END) AS 'Disable',

    SUM(CASE WHEN t.tasktype = 12 THEN 1 ELSE 0 END) AS 'Update',

    SUM(CASE WHEN t.tasktype = 3 THEN 1 ELSE 0 END) AS 'Create',

    SUM(CASE WHEN t.tasktype NOT IN ('2', '3', '12', '14') THEN 1 ELSE 0 END) AS 'Other',

'Update' as 'Task'

FROM 

    arstasks t

JOIN 

    endpoints e 

    ON t.endpoint = e.endpointkey 

WHERE

    t.status = 1 

    AND t.endpoint IN ('9')

UNION

SELECT 

    e.endpointname,

    SUM(CASE WHEN t.tasktype = 2 THEN 1 ELSE 0 END) AS 'Revoke',

    SUM(CASE WHEN t.tasktype = 14 THEN 1 ELSE 0 END) AS 'Disable',

    SUM(CASE WHEN t.tasktype = 12 THEN 1 ELSE 0 END) AS 'Update',

    SUM(CASE WHEN t.tasktype = 3 THEN 1 ELSE 0 END) AS 'Create',

    SUM(CASE WHEN t.tasktype NOT IN ('2', '3', '12', '14') THEN 1 ELSE 0 END) AS 'Other',

'Create' as 'Task'

FROM 

    arstasks t

JOIN 

    endpoints e 

    ON t.endpoint = e.endpointkey 

WHERE

    t.status = 1 

    AND t.endpoint IN ('9')

UNION

SELECT 

    e.endpointname,

    SUM(CASE WHEN t.tasktype = 2 THEN 1 ELSE 0 END) AS 'Revoke',

    SUM(CASE WHEN t.tasktype = 14 THEN 1 ELSE 0 END) AS 'Disable',

    SUM(CASE WHEN t.tasktype = 12 THEN 1 ELSE 0 END) AS 'Update',

    SUM(CASE WHEN t.tasktype = 3 THEN 1 ELSE 0 END) AS 'Create',

    SUM(CASE WHEN t.tasktype NOT IN ('2', '3', '12', '14') THEN 1 ELSE 0 END) AS 'Other',

'Other' as 'Task'

FROM 

    arstasks t

JOIN 

    endpoints e 

    ON t.endpoint = e.endpointkey 

WHERE

    t.status = 1 

    AND t.endpoint IN ('9')

///////////////////Dashboard Query///////////////
{"analyticsConfig":"1785","fieldToGroup":"Task","filterMap": {"Task": ["Revoke","Disable","Update","Create","Other"]}}

Dashboard Type: ES SIMPLE BAR CHART

Chart Properties: {}

The query runs successfully in the data analyzer, but when attempting to execute an analytical report, we receive the following alert:

"Alert: Control execution is taking longer than expected. You will be notified when the result is ready."

As a result, the report fails to execute, and we are unable to retrieve the data. 

Need help on this.

Thank you,
Raviteja Inje

[naveenss]

Hi @ravitejainje may I know why you're doing a UNION for the same endpoint multiple times?

[ravitejainje]

Hi @naveenss 

As attached, I need to pull the tasks as 

Revoke, Disable, Update, Create & Other tasks 

so, I divided each task type as 'Revoke' as 'Task', 'Disable' as 'Task' and so on.

Thank you,
Raviteja Inje

[rushikeshvartak]

SELECT
e.endpointname,
SUM(CASE WHEN t.tasktype = 2 THEN 1 ELSE 0 END) AS 'Revoke Task',
SUM(CASE WHEN t.tasktype = 14 THEN 1 ELSE 0 END) AS 'Disable Task',
SUM(CASE WHEN t.tasktype = 12 THEN 1 ELSE 0 END) AS 'Update Task',
SUM(CASE WHEN t.tasktype = 3 THEN 1 ELSE 0 END) AS 'Create Task',
SUM(CASE WHEN t.tasktype NOT IN ('2', '3', '12', '14') THEN 1 ELSE 0 END) AS 'Other Task',
FROM
arstasks t,endpoints e where t.endpoint = e.endpointkey and t.status = 1
AND t.endpoint IN ('9','10')

[ravitejainje]

Hi @rushikeshvartak 


I tried using your query and made modifications to fit one specific endpoint.

///////////////////Modified QUERY//////////////////

SELECT
e.endpointname,
SUM(CASE WHEN t.tasktype = 2 THEN 1 ELSE 0 END) AS 'Revoke Task',
SUM(CASE WHEN t.tasktype = 14 THEN 1 ELSE 0 END) AS 'Disable Task',
SUM(CASE WHEN t.tasktype = 12 THEN 1 ELSE 0 END) AS 'Update Task',
SUM(CASE WHEN t.tasktype = 3 THEN 1 ELSE 0 END) AS 'Create Task',
SUM(CASE WHEN t.tasktype NOT IN ('2', '3', '12', '14') THEN 1 ELSE 0 END) AS 'Other Task',
'Revoke Task' as 'Task'
FROM
arstasks t,endpoints e where t.endpoint = e.endpointkey and t.status = 1
AND t.endpoint IN ('9')

UNION

SELECT
e.endpointname,
SUM(CASE WHEN t.tasktype = 2 THEN 1 ELSE 0 END) AS 'Revoke Task',
SUM(CASE WHEN t.tasktype = 14 THEN 1 ELSE 0 END) AS 'Disable Task',
SUM(CASE WHEN t.tasktype = 12 THEN 1 ELSE 0 END) AS 'Update Task',
SUM(CASE WHEN t.tasktype = 3 THEN 1 ELSE 0 END) AS 'Create Task',
SUM(CASE WHEN t.tasktype NOT IN ('2', '3', '12', '14') THEN 1 ELSE 0 END) AS 'Other Task',
'Disable Task' as 'Task'
FROM
arstasks t,endpoints e where t.endpoint = e.endpointkey and t.status = 1
AND t.endpoint IN ('9')

UNION

SELECT
e.endpointname,
SUM(CASE WHEN t.tasktype = 2 THEN 1 ELSE 0 END) AS 'Revoke Task',
SUM(CASE WHEN t.tasktype = 14 THEN 1 ELSE 0 END) AS 'Disable Task',
SUM(CASE WHEN t.tasktype = 12 THEN 1 ELSE 0 END) AS 'Update Task',
SUM(CASE WHEN t.tasktype = 3 THEN 1 ELSE 0 END) AS 'Create Task',
SUM(CASE WHEN t.tasktype NOT IN ('2', '3', '12', '14') THEN 1 ELSE 0 END) AS 'Other Task',
'Update Task' as 'Task'
FROM
arstasks t,endpoints e where t.endpoint = e.endpointkey and t.status = 1
AND t.endpoint IN ('9')

UNION

SELECT
e.endpointname,
SUM(CASE WHEN t.tasktype = 2 THEN 1 ELSE 0 END) AS 'Revoke Task',
SUM(CASE WHEN t.tasktype = 14 THEN 1 ELSE 0 END) AS 'Disable Task',
SUM(CASE WHEN t.tasktype = 12 THEN 1 ELSE 0 END) AS 'Update Task',
SUM(CASE WHEN t.tasktype = 3 THEN 1 ELSE 0 END) AS 'Create Task',
SUM(CASE WHEN t.tasktype NOT IN ('2', '3', '12', '14') THEN 1 ELSE 0 END) AS 'Other Task',
'Create Task' as 'Task'
FROM
arstasks t,endpoints e where t.endpoint = e.endpointkey and t.status = 1
AND t.endpoint IN ('9')

UNION

SELECT
e.endpointname,
SUM(CASE WHEN t.tasktype = 2 THEN 1 ELSE 0 END) AS 'Revoke Task',
SUM(CASE WHEN t.tasktype = 14 THEN 1 ELSE 0 END) AS 'Disable Task',
SUM(CASE WHEN t.tasktype = 12 THEN 1 ELSE 0 END) AS 'Update Task',
SUM(CASE WHEN t.tasktype = 3 THEN 1 ELSE 0 END) AS 'Create Task',
SUM(CASE WHEN t.tasktype NOT IN ('2', '3', '12', '14') THEN 1 ELSE 0 END) AS 'Other Task',
'Other Task' as 'Task'
FROM
arstasks t,endpoints e where t.endpoint = e.endpointkey and t.status = 1
AND t.endpoint IN ('9')

We are receiving the correct data in the data analyzer and analytical report, as shown in the attached screenshot. However, when we generate the dashboard, it does not provide accurate data.

[rushikeshvartak]

Did you updated filterMap ?

{"analyticsConfig":"1785","fieldToGroup":"Task","filterMap": {"Task": ["Revoke","Disable","Update","Create","Other"]}}

[ravitejainje]

I have updated 
Dashboard Query * 

{"analyticsConfig":"1786","fieldToGroup":"Task","filterMap": {"Task": ["Revoke Task","Disable Task","Update Task","Create Task","Other Task"]}}

Chart Properties : {}

Not sure about 'filterMap'

Thank you.

[rushikeshvartak]

In filter type add keyword datapoints