Announcing the Saviynt Knowledge Exchange unifying the Saviynt forums, documentation, training,
and more in a single search tool across platforms. Read the announcement here.

Unable to create a Simple Bar Chart dashboard with Endpoint filters.

amanmalakar007
New Contributor II
New Contributor II

We are trying to create a Simple Bar Chart dashboard with Endpoint filter where we would like to fetch the list of completed tasks for each endpoint for past 30 days and display the endpoints in a drop-down menu fashion. We are pulling the data from 'arstasks' table.

Below is the Analytics Query to fetch the count of new & update accounts from completed tasks list :

SELECT 
    (SELECT COUNT(*) FROM arstasks WHERE UPDATEDATE > DATE_SUB(CURDATE(), INTERVAL 30 DAY) AND endpoint = 2 AND TASKTYPE = 3) AS AD_1, 
    (SELECT COUNT(*) FROM arstasks WHERE UPDATEDATE > DATE_SUB(CURDATE(), INTERVAL 30 DAY) AND endpoint = 7 AND TASKTYPE = 3) AS LDAP_1,
    (SELECT COUNT(*) FROM arstasks WHERE UPDATEDATE > DATE_SUB(CURDATE(), INTERVAL 30 DAY) AND endpoint = 2 AND TASKTYPE = 12) AS AD_2,
    (SELECT COUNT(*) FROM arstasks WHERE UPDATEDATE > DATE_SUB(CURDATE(), INTERVAL 30 DAY) AND endpoint = 7 AND TASKTYPE = 12) AS LDAP_2;

5 REPLIES 5

rushikeshvartak
All-Star
All-Star

Please share configuration screenshot.

 

Refer https://docs.saviyntcloud.com/bundle/SSM-Admin-v55x/page/Content/Chapter18-Dashboards/Dashboards.htm


Regards,
Rushikesh Vartak
If you find the response useful, kindly consider selecting Accept As Solution and clicking on the kudos button.

 

This is the current configuration for our 'Simple Bar Chart' (see below) :

Configuration Screenshot.png

This is what we currently see on the Dashboard :

Current State of Bar Chart.PNG

And this is what we would like things to be (the image below is edited to represent how we would like our bar chart to look like):

Our requirementOur requirement

As of now, we believe that we have the correct "Local Filter Query", which is able to list all the endpoints in the drop-down menu. We would appreciate if you could help us with the query for 'Dashboard Query' & 'Local Query'. 

Thanks in advance.

Regards,

Aman Malakar.

Dashboard Query should not have dynamic variables 


Regards,
Rushikesh Vartak
If you find the response useful, kindly consider selecting Accept As Solution and clicking on the kudos button.

PremMahadikar
Valued Contributor II
Valued Contributor II

Hi @amanmalakar007 ,

This is working for me. Try below:

Dashboard Queryselect 'New Accounts' as 'label',(select count(*) from arstasks where updatedate > date_sub(curdate(),interval 30 day) and tasktype=3) as 'data' from arstasks union select 'Total Accounts' as 'label',(select count(*) from arstasks where updatedate > date_sub(curdate(),interval 30 day) and tasktype=12) as 'data' from arstasks
Chart Properties{}
Local Queryselect 'New Accounts' as 'label', count(*) as 'data' from arstasks where updatedate > date_sub(curdate(),interval 500 day) and tasktype=3 and endpoint=${endpointNum} union select 'Total Accounts' as 'label', count(*) as 'data' from arstasks where updatedate > date_sub(curdate(),interval 500 day) and tasktype=12 and endpoint=${endpointNum}
Local Filter Query{"parameters":[{"paramName":"endpointNum","parentParam":"","label":"Endpoint Name","type":"dropdown","dataType":"query","value":"Select distinct at.endpoint as 'key',ep.endpointName as 'displayValue' from endpoints ep inner join arstasks at on ep.endpointkey=at.endpoint where endpointkey is not null"}]}

Dashboard preview:

PremMahadikar_0-1712689784704.png

If this answers your questions, kindly consider selecting Accept As Solution and hit Kudos

Hi @PremMahadikar ,

Thank you for suggesting this solution. This works very well for our use case. We appreciate your help.

I am accepting your solution as the answer to my question. Once again, thank you very much!

Regards,

Aman Malakar