Antoine_Sauteron_1266

Get a notification when a user's password is about to expire

Discussion created by Antoine_Sauteron_1266 on Nov 23, 2017
Latest reply on Feb 13, 2018 by Wolfgang_Brueckler_1288
I recently got this question in a support inquiry

Can we define a notification to a specific team that user passwords are about to expire?
Do we have any notification mechanism to notify the owner of service users?

There is no built-in function that would do that, as far as I know (if anybody reading this post knows the answer, feel free to correct me).

Anyway here is a proposition based on this post from Christine_Chavez_6412.

Disclaimer : please be cautious and ensure to test this before using it in a prod system. Also keep in mind it's just a proposition and cannot be considered a definitive solution.


VARA.SQLI


You can store the SQL statement below in a VARA.SQLI object to query the Automic DB. It will list users whose password has not been changed in the last 30 days:


MS SQL :
SELECT Usergroup.OH_Name "Usergoup", UserID.OH_Name "User", UserID.OH_Client, USR_FIRSTNAME, USR_LASTNAME, USR_PWCHANGE
FROM OH Usergroup, OH UserID, USR, USRG
WHERE USRG_USR_Idnr=UserID.OH_Idnr
AND USRG_USG_Idnr=Usergroup.OH_Idnr
AND UserID.OH_Idnr=USRG_USR_Idnr
AND UserID.OH_Idnr=USR_OH_Idnr
AND UserID.OH_Deleteflag='0'
AND USR_PWCHANGE < getdate() - 30
ORDER BY 1, 2, 3


Oracle :
SELECT Usergroup.OH_Name "Usergoup", UserID.OH_Name "User", UserID.OH_Client, USR_FIRSTNAME, USR_LASTNAME, USR_PWCHANGE
FROM OH Usergroup, OH UserID, USR, USRG
WHERE USRG_USR_Idnr=UserID.OH_Idnr
AND USRG_USG_Idnr=Usergroup.OH_Idnr
AND UserID.OH_Idnr=USRG_USR_Idnr
AND UserID.OH_Idnr=USR_OH_Idnr
AND UserID.OH_Deleteflag='0'
AND USR_PWCHANGE < sysdate - 30
ORDER BY 1, 2, 3
 
Notes :
  • There is no ';' at the end of the queries because it causes the SQL statement to fail when used in a VARA.SQL object.
  • Users who do not belong to a user group will not be listed by this query.


Then create a notification object and change it as below :


NOTIFICATION


Notification Tab

  • Adjust the recipient(s) : who should get the notification ? Are there any calendar restrictions ?
  • Change the subject : in this example it will display "List of Users" and the current date

2i9xnpcgfsfi.png

Change the html like below (you can use some css formatting) :
<!DOCTYPE html PUBLIC "-//W3C//DTD HTML 4.01 Transitional//EN">
<html>
<head>
<style type="text/css">
table, th, td
{
    border: 1px solid black;
    border-collapse: collapse;
    border-spacing: 5px; 
}
td, th {
    border: 1px solid #dddddd;
    text-align: left;
    padding: 8px;
}
</style>
<title>Users</title>
</head>
<body>
<strong>These users will have to change their password :</strong>
<br><br>
<table>
<tr>
<th>UserGroup</th><th>User</th><th>Client #</th><th>FirstName</th><th>SecondName</th><th>LastPasswordChange</th>
</tr>

Process tab

Use this code to parse results from the SQL statement contained in the VARA.SQLI and create a html table based on them:

: SET &HND# = PREP_PROCESS_VAR(VARA.SQLI.USER.INFO)
: PROCESS &HND#
:   SET &USRG# = GET_PROCESS_LINE(&HND#,2)
:   SET &USR# = GET_PROCESS_LINE(&HND#,3)
:   SET &CLNT# = GET_PROCESS_LINE(&HND#,4)
:   SET &USR_FIRSTNAME# = GET_PROCESS_LINE(&HND#,5)
:   SET &USR_LASTNAME# = GET_PROCESS_LINE(&HND#,6)
:   SET &USR_PW_CHNG# = GET_PROCESS_LINE(&HND#,7)
:   SET &TABLEROW# = '<tr><td>&USRG#</td><td>&USR#</td><td>&CLNT#</td><td>&USR_FIRSTNAME#</td><td>&USR_LASTNAME#</td><td>&USR_PW_CHNG#</td></tr>'
:   PUT_ATT_APPEND CALL_TEXT ='&TABLEROW#'
: ENDPROCESS

:PUT_ATT_APPEND CALL_TEXT = "</table></body></html>"

Upon executing the notification you will get something like this :

fnwoyhdjedc5.png

You can then set the notification to execute daily via a third object (JSCH for instance).

I'm attaching an xml export of the objects used in the example.

Best regards,
Antoine

Attachments

Outcomes