• State Verified Answer
  • Replies 1 reply
  • Subscribers 94 subscribers
  • Views 192 views
  • Users 0 members are here
Options
Related

Mail notification for bulk accounts expiration

el romeo
3 months ago

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

  • +1 3 months ago

    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