user overview after 3days of exit date

Vijay, can you please elaborate on what you're looking for?

Trevor,
I need to send a report to HR team and this report will kick after 3 days of the employee's last day. This report should mainly contain the user access in active state as some applications deals with manual work which is recorded in UnsAccountB and then triggers a ticket in service desk.
UnsAccountB and Target system are in daily sync.

Regards,
Vijay

Hi Vijay,

Are you looking to find active UNSAccountB for InActive linked identities only, or do you also need UNSGroupB data?

Hi Kin,

I want the UNSGroupB data as well. The report should show the complete access of identity which is in active and disable state.

Thanks,
Vijay

I think the SQL query should be simple enough for this, then you can use it to form your report. Something like:

select * from Person p
join UNSAccountB ub on ub.UID_Person = p.UID_Person
join UNSAccountBInUNSGroupB uag on uag.UID_UNSAccountB = ub.UID_UNSAccountB
join UNSGroupB ub on ub.UID_UNSGroupB = uag.UID_UNSGroupB
where p.IsInActive=1

Thanks a lot Kin.

Regards,
Vijay

Hi,

I think to use the end dateOR IsInActive, and have flags indicating if there's a risk, the code might be something like this (I haven't checked the code exactly but have formatted it to make it more readable):

select
   p.*,
   case when
   (
      p.IsInActive=1 
      OR 
      ( 
         YEAR(p.ExitDate) > 1901 AND p.ExitDate < DATEADD(DAY, –3, GETDATE()) 
      ) 
   )
   AND ub.AccountDisabled=0 THEN 1 else 0 END as AccountActiveRisk

from Person p

join UNSAccountB ub on ub.UID_Person = p.UID_Person
join UNSAccountBInUNSGroupB uag on uag.UID_UNSAccountB = ub.UID_UNSAccountB
join UNSGroupB ub on ub.UID_UNSGroupB = uag.UID_UNSGroupB
 
where p.IsInActive=1 OR ( YEAR(p.ExitDate) > 1901 AND p.ExitDate < GETDATE())

 

This will display an additional field which, if set to 1, indicates the account is live despite the person's end date being 3 days in the past (if set) OR the person being disabled.

The WHERE clause covers the possibility of the exit date being populated without the person being deactivated.

Thanks a ton Sayed.