- Report changes
- Set base count in report =1
- Query as below
SELECT '100000' AS USERNAME,
u.displayname AS DISPLAYNAME,
u.email AS EMAIL,
a.name AS ACCOUNT_NAME,
e.displayname AS DOMAIN,
a.lastpasswordchange AS
LAST_PASSWORD_CHANGE_DAT,
CASE
WHEN a.status = 1 THEN 'Active'
ELSE a.status
END AS ACCOUNT_STATE,
Date_add(a.lastpasswordchange, interval 42 day) AS PASSWORD_EXPIRY_ON
FROM users u,
accounts a,
user_accounts ua,
endpoints e
WHERE u.userkey = ua.userkey
AND ua.accountkey = a.accountkey
AND a.endpointkey = e.endpointkey
AND a.endpointkey IN ( 28, 29, 30, 31 )
AND a.status NOT IN ( 'SUSPENDED FROM IMPORT SERVICE', 'Inactive' )
AND Datediff(Date_add(a.lastpasswordchange, interval 42 day), SYSDATE())
BETWEEN
0
AND 5
AND u.customproperty39 = 'Active'
AND u.regioncode IN ( 'Active', 'Ready for Hire', 'Leave of Absence' )
AND u.companyname = 'abc'
- in Email body use below code
<!DOCTYPE html>
<html>
<head>
<title>Rushilesh</title>
<style>
table {
border-collapse: collapse;
border: 1px solid black;
width: 200%;
}
th,
td {
border: 1px solid black;
padding: 10px;
}
th {
background-color: #87CEFA;
text-align: center;
color: black;
font-size: 18px
}
td {
text-align: center;
}
</style>
</head>
<body>
<table>
<tr>
<th>DISPLAYNAME</th>
<th>EMAIL</th>
<th>ACCOUNT_NAME</th>
<th>DOMAIN</th>
<th>LAST_PASSWORD_CHANGE_DAT</th>
<th>ACOUNT_STATE</th>
<th>PASSWORD_EXPIRY_ON</th>
</tr>
<%int cnt=Integer.parseInt("${ANALYTICSDATA.USERNAME.size()}"); for(int i=0;i<cnt;i=i+1){%>
<tr>
<td>${ANALYTICSDATA.'DISPLAYNAME'[i]}</td>
<td>${ANALYTICSDATA.'EMAIL'[i]}</td>
<td>${ANALYTICSDATA.'ACCOUNT_NAME'[i]}</td>
<td>${ANALYTICSDATA.'DOMAIN'[i]}</td>
<td>${ANALYTICSDATA.'LAST_PASSWORD_CHANGE_DAT'[i]}</td>
<td>${ANALYTICSDATA.'ACCOUNT_STATE'[i]}</td>
<td>${ANALYTICSDATA.'PASSWORD_EXPIRY_ON'[i]}</td>
</tr>
<%}%>
</table> <br>
<p>Regards,<br> Rushikesh Vartak</p>
</body>
</html>
Regards,
Rushikesh Vartak
If this helped you move forward, click 'Kudos'. If it solved your query, select 'Accept As Solution'.
