Mail notification for bulk accounts expiration

Question

Hello everyone, 
 I'm looking to set up a monthly email notification system for managers, informing them about users whose accounts are set to expire in the upcoming month. 
 Currently, I have a process in place for individual email notifications, with one email being sent for each user nearing expiration. 
 However, I'm seeking guidance on how to streamline this for bulk users, consolidating all relevant information into a single monthly email. 
 Email Example: Dear Manager, 
 
 In the next moth (February) the following accounts will expire:
User1
User2
User3 ... 
 
 Any advice on how to efficiently manage this process and send just one comprehensive email per month would be greatly appreciated. 
 Thank you, 
 Elena

Steve.Knott · Accepted Answer

Hi 
 Probably a few ways of doing this and I am not sure if we are talking about identities expiring (leavers) or target system accounts, but I think the principle is basically the same. 
 You could create a condition on your process plan and use a query on the helperheadperson table to trigger a single email per manager. 
 A condition like this should work for identities but could be adapted to suit your needs. 
 UID_Person in (
 select max(p.UID_personhead) as UID_Person
 from helperheadperson hhp
 Join Person p on hhp.UID_Person = p.UID_Person 
 where 
 p.DateLastWorked < DATEADD(day, 30, GetUTCDate()) and p.DateLastWorked > GetUTCDate()
 group by p.UID_PersonHead
 )
 
 If you need a list of leavers/expiring accounts in your mail template, then you could configure a parameter value on the mail process step and use a script to return the details of the leavers/accounts. 
 Something like this would produce a list of identities leaving in the next 30 days in a similar format to your email example. 
 Public Function CCC_Person_Leavers_Mail(ByVal uidpersonhead As String) As String

 Dim f As ISqlFormatter = Session.SqlFormatter
	Dim Perobjects As IEntityCollection
	Dim strPersons As New StringBuilder()
	
	Dim qpers = Query.From("Person").
 Where(f.AndRelation(f.UidComparison("UID_Personhead", uidpersonhead), _
 f.Comparison("DateLastWorked", Date.UTCNow, ValType.Date, CompareOperator.GreaterThan, FormatterOptions.None), _
 f.Comparison("DateLastWorked", Date.UTCNow.AddDays(30), ValType.Date, CompareOperator.LowerThan, FormatterOptions.None))).
 SelectAll()
 
	Perobjects = Session.Source.GetCollection(qpers, EntityCollectionLoadType.Default)
	' Check if anything returned
	Dim perscount = qpers.SelectCount() 
	Dim iCount = Session.Source.GetCount(perscount)
 If iCount > 0 Then
 For Each perobject As IEntity In Perobjects
 Dim objperson As IEntity = Session.Source.Get("Person", perobject.GetValue("UID_Person"))
 Dim displayperson = objperson.Display
 strPersons.AppendLine(displayperson)
 Next
 End If
	Dim strpersonString = strPersons.ToString()
 
	Return strpersonString
End Function
 
 As I say, there could be other ways. 
 HTH

Mail notification for bulk accounts expiration

Top Replies