How to trigger a mail to get service accounts details from analytics.

[RutujaM]

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';

[rushikeshvartak]

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

[rushikeshvartak]

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';

[RutujaM]

Error : Unknown column 'ao.userkey' in 'on clause'

Above query is giving this error.

[rushikeshvartak]

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';