service account campaign data

[Sakshi2806]

Hi All,

We are looking to export campaign report for service accounts which are not responded. It should also contain the account name and CP4 and CP5 value from accounts

select c.id as 'CERT_ID',csa.certification_account,c.campaign_name,cn.cert_name as 'CERT_NAME',u.username,cn.createdate,cn.ENDDATE from campaign c join certification cn on c.id=cn.CAMPAIGNKEY join cert_service_account_status csa on csa.certkey=cn.certkey join users u on u.userkey=cn.CERTIFIER
where cn.STATUS =0 and c.id =183

We are able to get to get the line items which are not responded using this query, but we want to go one more level down and find the SA name and retrieve CP4 and CP5 values. However not sure how can i map accounts table with certifications

[naveenss]

Hi @Sakshi2806 please use the below query. Let me know if this helps!

SELECT 
    c.id AS 'CERT_ID',
    csa.certification_account,
    c.campaign_name,
    cn.cert_name AS 'CERT_NAME',
    u.username,
    cn.createdate,
    cn.ENDDATE,
    a.name,
    a.accountid,
    a.customproperty4,
    a.customproperty5
FROM
    campaign c
        JOIN
    certification cn ON c.id = cn.CAMPAIGNKEY
        JOIN
    cert_service_account_status csa ON csa.certkey = cn.certkey
        JOIN
    users u ON u.userkey = cn.CERTIFIER
        JOIN
    certification_account ca ON ca.CERT_ACCOUNTKEY = csa.certification_account
        JOIN
    accounts a ON a.accountkey = ca.accountkey
WHERE
    cn.STATUS = 0 AND c.id = 183

[rushikeshvartak]

SELECT c.id                      AS CERT_ID,
       csa.certification_account AS CERT_ACCOUNT,
       c.campaign_name           AS CAMPAIGN_NAME,
       cn.cert_name              AS CERT_NAME,
       u.username                AS USERNAME,
       cn.createdate             AS CREATE_DATE,
       cn.enddate                AS END_DATE,
       a.NAME                    AS ACCOUNT_NAME,
       a.accountid               AS ACCOUNT_ID,
       a.customproperty4         AS CUSTOM_PROPERTY_4,
       a.customproperty5         AS CUSTOM_PROPERTY_5
FROM   certification cn
       JOIN campaign c
         ON c.id = cn.campaignkey
       JOIN cert_service_account_status csa
         ON csa.certkey = cn.certkey
       JOIN certification_account ca
         ON ca.cert_accountkey = csa.certification_account
       JOIN accounts a
         ON a.accountkey = ca.accountkey
       JOIN users u
         ON u.userkey = cn.certifier
WHERE  cn.status = 0
       AND cn.type = 6; 

[Sakshi2806]

Thankyou both , we used this below query. As the email where we want to send out the notification is stored in CP5 of accounts table

SELECT c.cert_name AS 'CERT_NAME', ca.campaign_name AS 'Campaign Name', ca.end_date AS 'CAMPAIGN_ENDDATE', users.username , c.progress AS 'Percentage', c.createdate AS 'Date_Created', c.enddate AS 'Due _Date_of_Review', caa.NAME AS AccountName, endpoints.endpointname AS Application, a.customproperty5 AS 'mailAddress', cs.certified, c.campaignkey as 'CERT_ID' FROM certification c INNER JOIN campaign ca ON c.campaignkey = ca.id INNER JOIN certification_user_account_status cs ON c.certkey = cs.certkey INNER JOIN certification_account caa ON caa.cert_accountkey = cs.cert_accountkey INNER JOIN certification_user cu ON cu.cert_userkey = cs.cert_userkey LEFT JOIN users ON users.userkey = c.certifier LEFT JOIN users u2 ON u2.username = cu.username LEFT JOIN endpoints ON endpoints.endpointkey = caa.endpointkey join accounts a ON a.accountkey=caa.accountkey WHERE caa.endpointkey =2 and c.campaignkey IN ('183') and cs.certified is NULL

However we are not able to trigger mail via analytics.

I am using ANALYTICSDATA{'mailAddress'[0]} in TO field but it shows error as groovy.lang.MissingPropertyException: No such property: ANALYTICSDATA

[rushikeshvartak]

Please validate supported variables.

Purpose :

To get what variables are present to use in the email template.

Email Template steps :

keep Advanced HTML CSS flag is checked

Keep your email Address in TO

TO /CC/BCC & Subject - Don't use any dynamic variables

Add only below code in Email Body
${this.binding.variables.each {k,v -> println "$k = $v" + "" + "br" + ">"}}

Output : using the below expression. By putting it in the email body, it will print all variable names with values, and then accordingly, you can use it: