Automic Workload Automation

  • Private Community
 View Only

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

    Posted Nov 23, 2017 11:31 AM
      |   view attached
    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.pnghttps://us.v-cdn.net/5019921/uploads/editor/5m/fnwoyhdjedc5.png" width="826">

    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

    Attachment(s)

    xml
    users.xml   5 KB 1 version


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

    Posted Jan 11, 2018 08:40 AM
    What's the password policy in Automic?

    SELECT USR_FIRSTNAME, USR_LASTNAME, USR_ACTIVE, TO_CHAR(USR_PWCHANGE, 'DD-MON-YYYY'), USR_EMAIL1, USR_LASTSESSION FROM UC4.USR WHERE USR_LASTSESSION IS NOT NULL AND USR_ACTIVE!=0 AND USR_FIRSTNAME IS NOT NULL ORDER BY USR_PWCHANGE DESC

    I tried the above query and the USR_PWCHANGE is showing as 01-Jan-2099 for some users and 01-Jan-1960 for some users.


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

    Posted Feb 11, 2018 06:56 AM
    Hi Antoine,

    I tried the above sample by triggering  notification object type alone for sending the mail which picks the data from an VARA.STATIC object type.

    But i get the below error when it gets triggered:

    U00050033 The SMTP client cannot receive data from the SMTP server 'smtp.user,sample.com', error code '104', error description: 'unknown error code'.

    Logging:
    uxp065zdz54h.pnghttps://us.v-cdn.net/5019921/uploads/editor/fd/uxp065zdz54h.png" width="1226">

    How can this be rectified?



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

    Posted Feb 11, 2018 09:36 AM
    Here the SMTP_* Settings within UC_CLIENT_SETTINGS VARA are not set correctly.

    Pls refer to:
    https://docs.automic.com/documentation/WEBHELP/English/AWA/11.2/AE/11.2/All%20Guides/help.htm#ucaabs.htm%3FTocPath%3DAdministration%2520Guide%7CConfiguration%7CSettings%2520in%2520Variables%7C_____6

    cheers, Wolfgang


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

    Posted Feb 12, 2018 01:32 PM
    If the SMTP* setting is not configured properly then i should not receive any mail triggered by an CALL object.

    But in my case i receive mails from other CALL object .I also simply checked by writing "Hello" in Notification Tab and in the recipient i added my user id and when the CALL object was triggered i get an mail.

    Any suggestion where i went wrong in achieving this?



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

    Posted Feb 12, 2018 01:41 PM
    SMTP error 104 stands for:


    104 Server error; response not understood. For example, the message is poorly formatted, or the server is not SMTP.



    Can you post the content of notification object?



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

    Posted Feb 12, 2018 10:46 PM
    Hi Trenker,

    Please find the below details,
    I am using Vara.Static from which expecting the table details to generate in mail .

    Call Object details :

    Notification Tab:
    <!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>JOB STATUS REPORT :</strong>
    <br><br>
    <table>
    <tr>
    <th>RUNID</th><th>PFNAME</th><th>JOBNAME #</th><th>STARTTIME</th><th>ENDTIME</th><th>JOBSTATUS</th>
    </tr>

    Process Tab:


    :SET &TRACKVARA3# = "VARA.JOBREPORT"
    : SET &HND# = PREP_PROCESS_VAR(&TRACKVARA3#)
    : PROCESS &HND#
    :   SET &RUNID# = GET_PROCESS_LINE(&HND#,1)
    :   SET &PFNAME# = GET_PROCESS_LINE(&HND#,2)
    :   SET &JOBNAME# = GET_PROCESS_LINE(&HND#,3)
    :   SET &STARTTIME# = GET_PROCESS_LINE(&HND#,4)
    :   SET &ENDTIME# = GET_PROCESS_LINE(&HND#,5)
    :   SET &JOBSTATUS# = GET_PROCESS_LINE(&HND#,6)
    :   SET &TABLEROW# = '<tr><td>&RUNID#</td><td>&PFNAME#</td><td>&JOBNAME#</td><td>&STARTTIME#</td><td>&ENDTIME#</td><td>&JOBSTATUS#</td></tr>'
    :   PUT_ATT_APPEND CALL_TEXT ='&TABLEROW#'
    : ENDPROCESS
    :CLOSE_PROCESS &HND#

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

    Screenshots:
    bjfy5m28dark.pnghttps://us.v-cdn.net/5019921/uploads/editor/7q/bjfy5m28dark.png" width="817">xz1581t4zfw0.pnghttps://us.v-cdn.net/5019921/uploads/editor/md/xz1581t4zfw0.png" width="1321">


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

    Posted Feb 13, 2018 04:59 AM
    I still think there must be somewhere a config error :-)

    I put your code into a CALL.HTML object (only changed the vara name) and it went fine.

    Just observed the user in the error message: smtp.user,sample.com - can this , be a typo?

    if not pls post the SMTP settings in your Client.

    cheers, Wolfgang