Click HERE to see how Saviynt Intelligence is transforming the industry. |
06/05/2024 03:36 AM
Hello,
I am trying to trigger a mail which will go to a specific email-id where all the service account will be listed in email's body.
This is what I tried. I want it in a table format.
<table border="1">
<tr>
<th style="padding: 10px;">Accounts</th>
</tr>
<tr>
<td style="padding: 10px;">${ANALYTICSDATA.'Accountname'[i]}</td>
</tr>
</table>
Analytics query:
select u.USERNAME as 'user_name',a.name as Accountname,a.accounttype,u.userkey,ua.accountkey,e.endpointname from users u
JOIN
user_accounts ua ON ua.userkey = u.userkey
JOIN
Accounts a ON a.accountkey = ua.accountkey
JOIN
endpoints e ON e.endpointkey = a.endpointkey
WHERE a.accounttype='service account';
06/05/2024 06:11 AM
Your query is wrong. Service account are not mapped to user rather mapped to owner. Join with accountowners table and keep username as column in report
06/05/2024 06:12 AM
select u.USERNAME as 'username',a.name as Accountname,a.accounttype,u.userkey,ao.accountkey,e.endpointname from users u
JOIN
Accountowners ao ON ao.userkey = u.userkey
JOIN
Accounts a ON a.accountkey = ao.accountkey
JOIN
endpoints e ON e.endpointkey = a.endpointkey
WHERE a.accounttype='service account';
06/05/2024 06:32 AM
Error : Unknown column 'ao.userkey' in 'on clause'
Above query is giving this error.
06/09/2024 10:10 PM
select u.USERNAME as 'username',a.name as Accountname,a.accounttype,u.userkey,ao.accountkey,e.endpointname from users u JOIN Accountowners ao ON ao.OWNERUSERKEY= u.userkey JOIN Accounts a ON a.accountkey = ao.accountkey JOIN endpoints e ON e.endpointkey = a.endpointkey WHERE a.accounttype='service account'; |