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 trigger email with binding variables from analytics report

Bhargavi3
New Contributor
New Contributor

Hi Team,

We have a requirement where pending task gets any error,email should trigger to the requestor with error message. To perform this we have created an analytics report with basic query and configured one email template with general information. The below one is triggering but if I give binding variables it is not getting triggered. Please find the attached analytics query and email template which I was using.

Bhargavi3_0-1713265295310.png

 

Bhargavi3_1-1713265414233.png

 

Bhargavi3_2-1713265469448.png

could someone help me out with the query and email template (In email I also need to mention requestor name, requestor email,endpointname).

[This message has been edited by moderator to mask sensitive information]

11 REPLIES 11

PremMahadikar
Valued Contributor
Valued Contributor

Hi @Bhargavi3 ,

1. Query: The below query should fetch failed task from past 30 days: Please refer to it and use it as per your use case:

Select 
	taskkey as WorkItem_ID,
	source as 'Task source',
	Case 
		when ASSIGNEDFROMRULE is not null then (select ha.name from hanarule ha where ha.hanarulekey=ASSIGNEDFROMRULE) 
	end as 'Triggered by rule',
	Case
		When ASSIGNEDFROMROLE is not null then (select r.ROLE_NAME from roles r where r.ROLEKEY=ASSIGNEDFROMROLE)
	End as 'Assigned by role',
	(select username from users where userkey = ars.userkey) as Requestee, 
	(select concat(firstname,' ',lastname) from users where userkey = ars.userkey) as 'Requestee Name', 	TASKDATE as Creation_Date, 
	UPDATEDATE as "Completetion Date", 
	(select endpointname from endpoints where endpointkey = ars.endpoint) as ENDPOINT, 
	CASE 
		when tasktype = 1 then "Add access" 
		when tasktype = 2 then "Remove access / Account" 
		when tasktype = 3 then "New account" 
		when tasktype = 4 then "Role request"
		when tasktype = 5 then "Change password"
		when tasktype = 6 then "Enable account"
		when tasktype = 8 then "Delete account"
		when tasktype = 9 then "Update user"
		when tasktype = 12 then "Update account" 
		when tasktype = 14 then "Disable account"
		when tasktype = 18 then "Create user (From create user tile)"
		when tasktype = 19 then "Update user (From update user tile)"
		when tasktype = 24 then "Create entitlement"
		when tasktype = 25 then "Add access entitlement"
		when tasktype = 26 then "Remove access entitlement"
		when tasktype = 27 then "Update entitlement"
		when tasktype = 28 then "Delete entitlement"
	END as Operation, 
	AccountName, 
	CASE 
		when status = 3 then 'COMPLETE' 
		when status = 1 then 'OPEN' 
		when status = 6 then 'PENDINGPROV' 
		when ars.status = 4 then 'DISCONTINUE' 
		when ars.status = 2 then 'IN PROGRESS' 
		when ars.status = 8 then 'ERROR' 
	end TASKSTATUS, 
	COMMENTS as 'Task comments',
	PROVISIONINGMETADATA, 
	PROVISIONINGTRIES as 'Provisioning tries', 
	PROVISIONINGCOMMENTS 
from 
	arstasks ars 
where 
	status in ('8','1')
	and (PROVISIONINGCOMMENTS like '%Error%' OR PROVISIONINGTRIES > 1)
	and UPDATEDATE BETWEEN DATE_SUB(NOW(),INTERVAL 30 DAY) AND NOW();

 

If you find the above response useful, Kindly Mark it as Accept As Solution and hit Kudos

Saathvik
All-Star
All-Star

@Bhargavi3 : Can you enable Advanced HTML CSS and see if you are still facing issue. If you still see same issue can you please share the respective logs?


Regards,
Saathvik
If this reply answered your question, please Accept As Solution and give Kudos to help others facing similar issue.

rushikeshvartak
All-Star
All-Star

Report does not have username column please add username column which is mandatory


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

Hi @PremMahadikar , Thanks for the query. I updated the query according to my requirement. Please have a look working as expected.
Select AccountName,
(select username from users where userkey = ars.userkey) as Requestee,
(select concat(firstname,' ',lastname) from users where userkey = ars.userkey) as 'Requestee Name',
(select endpointname from endpoints where endpointkey = 😎 as ENDPOINT,
COMMENTS as 'Task comments',
PROVISIONINGTRIES as 'Provisioning tries',
PROVISIONINGCOMMENTS
from
arstasks ars
where PROVISIONINGCOMMENTS like '%Error%' and ars.securitysystem = 10.

Hi @rushikeshvartak , I have added username field in the query.

Hi @Saathvik, Yes I tried enable of HTML CSS. But email is not getting triggered.

Email template which I am using now: (not working)

Hi ${requestedBy.username},

This email is sent to notify you that the ${ANALYTICSDATA.'ENDPOINT'[0]} request for ${requestedBy.username} is unsucessful.

Error Log: ${ANALYTICSDATA.'PROVISIONINGCOMMENS'[0]}

Regards,
IGA Platform Team.

Please let me know what changes needs to be done to get email triggered.

Thanks,

Bhargavi Padaraju.

 

Select AccountName,
(select username from users where userkey = ars.userkey) as username,
(select concat(firstname,' ',lastname) from users where userkey = ars.userkey) as 'Requestee Name',
(select endpointname from endpoints where endpointkey =1)

 as ENDPOINT,
COMMENTS as 'Task comments',
PROVISIONINGTRIES as 'Provisioning tries',
PROVISIONINGCOMMENTS
from
arstasks ars
where PROVISIONINGCOMMENTS like '%Error%' and ars.securitysystem = 10.


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

Hi @rushikeshvartak ,

I have used the binding variable which you have shared. I got email triggered with the below screenshot.

Bhargavi3_0-1713759255224.png

Could you please let me know which variables can be used to get the account details task details in the email.

thanks,

Bhargavi Padaraju.

 

 

Validate  "Group emails by username" is enabled 

rushikeshvartak_0-1713760424624.png

 


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

Yes, it is enabled.

Could you please let me know which variables I can use in the body to trigger email with account details, task details.

Thanks,

Bhargavi Padaraju.

Does user query contains username column


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

Yes @rushikeshvartak.

Select AccountName,
(select username from users where userkey = ars.userkey) as username,
(select concat(firstname,' ',lastname) from users where userkey = ars.userkey) as 'Requestee Name',
(select endpointname from endpoints where endpointkey =8)

as ENDPOINT,
COMMENTS as 'Task comments',
PROVISIONINGTRIES as 'Provisioning tries',
PROVISIONINGCOMMENTS
from
arstasks ars
where PROVISIONINGCOMMENTS like '%Error%' and ars.securitysystem = 10

The below email template which I am trying to trigger.

Hi ${ANALYTICSDATA.'username'[0]},

This email is sent to notify you that the ${ANALYTICSDATA.'ENDPOINT'[0]} request for ${ANALYTICSDATA.'AccountName'[0]} is unsucessful.

Error Log: ${ANALYTICSDATA.'PROVISIONINGCOMMENTS'[0]}

Thanks,

Bhargavi Padaraju.

Create new analytics and test


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