Notify user and manager prior to disabling AD account due to inactivity

[krecpond]

We have a use case to monitor login activity of users' AD accounts and disable the account if there has been no activity for 30 or more days. This is being monitored by importing the lastlogondate from AD as part of accounts import job. The requirement is to notify the user and the user's manager 5 days prior to disabling.

As part of the user import from Workday, the modifyuserdata JSON has pre-processor queries to fetch the correlated account for each user and check if the lastlogon date + 25 days = curdate() and populate a value of 5 on CP51. When CP51 is populated, a user update rule is triggered to notify users using an email template. In the email template, the To is set to user.email and cc is set to manager.email.

During testing, one of the managers was set up with a delegate and then the user import was run.

However, when the email was triggered it directly went to the user's manager instead of the delegate although the delegate was active.

What would be the best way to address this notification requirement?

Thanks,

VJ.

[rushikeshvartak]

What is code added in email to. Mostly delegate logic by product team not configured there

[krecpond]

To is configured as ${user.email}. cc is configured as ${manager.email}.

What do you mean by "Mostly delegate logic by product team not configured there".

Regards,

VJ.

[rushikeshvartak]

Email sent to Delegation are internally managed by code there is no separate variable available hence its defect raise freshdesk ticket

[krecpond]

As an alternative, I can get the existing active delegations during the user import using the preprocessor queries with the below query in the ADDITIONALTABLES:

"DELEGATES":"SELECT STARTDATE, ENDDATE, USERKEY, DELEGATEUSERKEY FROM DELEGATES WHERE STARTDATE <= NOW() AND ENDDATE > NOW()"

However, when I try to set the secondary manager on NEWUSERSDATA, in the preprocessor queries , as CD.DELEGATEKEY using the below query, it is not getting set.

"UPDATE NEWUSERDATA NU INNER JOIN CURRENTUSERS CU ON NU.USERNAME = CU.USERNAME INNER JOIN CURRENTDELEGATES CD ON CU.USERKEY = CD.USERKEY SET NU.SECONDARYMANAGER = CD.DELEGATEUSERKEY WHERE NU.CUSTOMPROPERTY1 != 'Terminated' AND CU.STATUSKEY=1 AND CU.CUSTOMPROPERTY47 = 'HR'"

Does Secondary manager need the username of the user object? If so, is there a way to do a join between the DELEGATES table and the USERS table to get the USERNAME of the DELEGATEUSERKEY in the MODIFYUSERDATA json configuration?

[rushikeshvartak]

When are you planning to run this job before every job

[krecpond]

This is part of the workday user import job that runs on a regular schedule.

[sk]

Try below query once and see if that works

UPDATE NEWUSERDATA NU, CURRENTUSERS CU, CURRENTDELEGATES CD SET NU.SECONDARYMANAGER = CD.DELEGATEUSERKEY WHERE NU.USERNAME = CU.USERNAME AND CU.USERKEY = CD.USERKEY AND NU.CUSTOMPROPERTY1 != 'Terminated' AND CU.STATUSKEY=1 AND CU.CUSTOMPROPERTY47 = 'HR'

As per below thread updates didn't work with joins instead it worked in above format so try above query and see if that works

https://forums.saviynt.com/t5/identity-governance/user-pre-processor-config-json-is-not-working-duri...

[krecpond]

I got it to work this way:

1. In ADDITIONALTABLES, I have the below query:

DELEGATES":"SELECT D.STARTDATE, D.ENDDATE, D.USERKEY, (SELECT USERS.USERNAME FROM USERS WHERE USERS.USERKEY = D.DELEGATEUSERKEY) AS DELEGATEUSER FROM DELEGATES D WHERE D.STARTDATE <= NOW() AND D.ENDDATE > NOW()

2. In preprocessor queries, I have the below update

UPDATE NEWUSERDATA NU INNER JOIN CURRENTUSERS CU ON NU.USERNAME = CU.USERNAME INNER JOIN CURRENTDELEGATES CD ON CU.USERKEY = CD.USERKEY SET NU.SECONDARYMANAGER = CD.DELEGATEUSER WHERE NU.CUSTOMPROPERTY1 != 'Terminated' AND CU.STATUSKEY=1 AND CU.CUSTOMPROPERTY47 = 'HR'

This worked when I did a unit test with one user. I am testing by running the full import against Workday.

If this does not work, then I will try with the suggestion above.

[sk]

since you are already joining users table with delegate to get delegate username, i would suggest to directly get email and update delegate email in cp of users table so that in email template you can directly use user.cpxx to get email of delegate. Otherwise it would be tricky to get secondary manager email. I am not sure if there is a direct parameter to get secondary manager email

[krecpond]

The binding variable ${user.secondaryEmail} works to get the email id of the secondary manager user object in the email template triggered through update rule.

[sk]

That is for user's secondary email but not email of secondary manager.

You can try below 

${secondarymanager.email}

${user?.secondarymanager.email}

[krecpond]

that was a typo from my end.

I have it as {user?.secondaryManager.email}

Now I am trying to make it conditional but the manager email is not getting resolved.

I have the "CC" field with the condition:

<% if(null==user?.secondaryManager.email || user?.secondaryManager.email=='') print "${manager.email}" else print "${user?.secondaryManager.email}" %>

But when the secondary manager is not populated, then manager email is not getting resolved. But if I put ${manager.email} unconditionally, Saviynt is able to populate the manager email address.

Any thoughts on how I can toggle between secondary manager and manager email addresses based on whether secondary manager attribute is populated or null?

[sk]

Try below

<% if(null==user?.secondaryManager.email || user?.secondaryManager.email=='') print ${manager.email} else print ${user?.secondaryManager.email} %>

[krecpond]

This also did not work. Secondary manager email is not getting resolved.

[sk]

Just to understand better below two parameters working fine but not working as expected with if condition logic?

${manager.email}

{user?.secondaryManager.email}

If that is the case try below

<% if(null==user?.secondaryManager || user?.secondaryManager=='') print "${manager.email}" else print "${user?.secondaryManager.email}" %>

[krecpond]

The above condition also does not work

secondarManager.email always worked with the condition but the manager.email does not work with the condition.

[sk]

Try below, It should work validated the same from my end

<% if (!user.secondaryManager) print "${manager.email}" else print "${user.secondaryManager.email}" %>

[krecpond]

The recent suggestion seems to be working. I will test it further and let you know if the issue still persists.

Thanks.

[krecpond]

The condition provided to negate secondary manager object worked.

Need one more help. In the subject line of the email, I need to indicate when the account will be disabled / deleted based on last logon date.

The requirement is to disable an AD account after 30 days of inactivity and delete it after 90 days of inactivity.

I would like to use a single email template in which the subject line can be populated based on user's CP51 value (CP51 = DATEDIFF(ADDDATE(lastlongdate, interval 31 days), CONVERT_TZ(NOW(), 'UTC', 'US/Eastern') or DATEDIFF(ADDDATE(lastlongdate, interval 91 days), CONVERT_TZ(NOW(), 'UTC', 'US/Eastern')).

When CP51 gets updated to 5 or 10 through the Workday user import, it will trigger a user update rule to notify the recipient.

Based on the above condition CP51 will either have 5 or 10 and I would like to add this to current date in the subject line. What would be date function that I would have to use to get the required date for disabling / deleting the AD account?

Thanks,

Vijay.

[krecpond]

<% if(null!=user?.customproperty51 || user?.customproperty51!='') print "${java.text.SimpleDateFormat('dd MMM yyyy').format( java.text.SimpleDateFormat('yyyy-MM-dd').parse(java.time.LocalDate.now().plusDays(Integer.valueOf(user.customproperty51)).toString()))}" %>

The above condition does not work. With the above condition, the email is not getting triggered from the user update rule.

[sk]

Try this one

<% if(null!=user?.customproperty51 || user?.customproperty51!='') print "${new java.text.SimpleDateFormat('dd MMM yyyy').format(new Date().plus(Integer.valueOf(user.customproperty51)))}" %>

[krecpond]

I am trying to make the subject line generic with the below condition, but unable to save the email template with the below subject line conditional enumeration:

DEV - Action Required - Your Equifax account will be <% if(!user?.customproperty51 && user?.customproperty51=='5') print "'Disabled'" else print "'Deleted'" %> on <% if(null!=user?.customproperty51 || user?.customproperty51!='') print "${new java.text.SimpleDateFormat('dd MMM yyyy').format(new Date().plus(Integer.valueOf(user.customproperty51)))}" %>

Please let me know what is incorrect in this?

Thanks,

Vijay Narayanan.

[sk]

I don't see any issue with statement but looks like it is exceeding the column limit for subject. Subject column is of varchar(255) it you try to reduce the number of characters it may work

[sk]

Try to trim your statement as below so that it can work

DEV:Action Required - Your Equifax account will be ${user?.customproperty51=='5'?"Disabled":"Deleted"} on ${(!user?.customproperty51)?(new java.text.SimpleDateFormat('dd MMM yyyy').format(new Date().plus(user.customproperty51.toInteger())))}

https://forums.saviynt.com/t5/identity-governance/email-template-subject-error-operation-not-allowed...

[rushikeshvartak]

Your logic is 353 character, raise freshdesk ticket to increase column size

[krecpond]

And I figured out how to get this done to set the secondary manager based on active delegations for the parent user. In the user update rule, while notifying via email, the recipient can be populated based on whether secondary manager is null or not null.

[rushikeshvartak]

issue solved ?

[krecpond]

Yes. still testing the complete implementation....