Send list of entitlements to users manager in email

[ruchika]

I need to send this list of entitlements whose owner just got inactive to the users manager through an email.

Is there any query I can use in analytics that would fetch me this data. I have configured the email template for it but getting stuck on fetching this logic.

We have this out of the box query but this fetches all the entitlement with inactive owners present in Saviynt and I need it for one specific user. Active Entitlements found with Inactive Owners

SELECT u.username as 'Entitlement_owner', u.displayname, u.employeeclass, u.departmentname as 'DEPARTMENT', CASE u.statuskey WHEN 1 THEN 'Active' WHEN 0 THEN 'Inactive' END AS 'USERSTATUS', ev.entitlement_value, et.entitlementname, ep.endpointname, ep.endpointKey AS endpointKey FROM users u INNER JOIN entitlement_owners eo ON u.userkey = eo.userkey INNER JOIN entitlement_values ev ON eo.entitlement_valuekey = ev.entitlement_valuekey INNER JOIN entitlement_types et ON ev.entitlementtypekey = et.entitlementtypekey INNER JOIN endpoints ep ON et.endpointKey = ep.endpointKey WHERE u.statuskey = 0;

[This post has been edited by a Moderator to move to its own thread.]

[rushikeshvartak]

SELECT u.username as 'Entitlement_owner', u.displayname, u.employeeclass, u.departmentname as 'DEPARTMENT', CASE u.statuskey WHEN 1 THEN 'Active' WHEN 0 THEN 'Inactive' END AS 'USERSTATUS', ev.entitlement_value, et.entitlementname, ep.endpointname, ep.endpointKey AS endpointKey FROM users u INNER JOIN entitlement_owners eo ON u.userkey = eo.userkey INNER JOIN entitlement_values ev ON eo.entitlement_valuekey = ev.entitlement_valuekey INNER JOIN entitlement_types et ON ev.entitlementtypekey = et.entitlementtypekey INNER JOIN endpoints ep ON et.endpointKey = ep.endpointKey INNER JOIN users m ON u.manager=m.userkey WHERE u.statuskey = 0 and u.username='Rushi' 

[ruchika]

I can't hardcode the username as when one user gets deactivated, analytics is supposed to trigger on email on runtime. Rest logic looks good can we skip adding username='Rushi' in the query? would that work?

[rushikeshvartak]

SELECT m.username,u.username as 'Entitlement_owner', u.displayname, u.employeeclass, u.departmentname as 'DEPARTMENT', CASE u.statuskey WHEN 1 THEN 'Active' WHEN 0 THEN 'Inactive' END AS 'USERSTATUS', ev.entitlement_value, et.entitlementname, ep.endpointname, ep.endpointKey AS endpointKey FROM users u INNER JOIN entitlement_owners eo ON u.userkey = eo.userkey INNER JOIN entitlement_values ev ON eo.entitlement_valuekey = ev.entitlement_valuekey INNER JOIN entitlement_types et ON ev.entitlementtypekey = et.entitlementtypekey INNER JOIN endpoints ep ON et.endpointKey = ep.endpointKey INNER JOIN users m ON u.manager=m.userkey WHERE u.statuskey = 0 

• Attach email to analytics report
• Email Template 

To: ${userEmail}

Subject: New Report available

Body:

Report - ${analyticsConfig.analyticsName} has been generated. Log in to EIC and navigate to the Analytics tab to access the report.

${ANALYTICSDATA}

https://docs.saviyntcloud.com/bundle/EIC-Admin-v24x/page/Content/Chapter06-EIC-Configurations/Creati...