eQL or SQL for Persons/User Report

Hi All

I need to run a report that is focused around Users.  Specifically I need:

  • Username, Status (Active/Not Active)
  • The date they were added (think I can get this in the Audit tables)
  • The date they became Not Active (almost like an end date for their access)

The last ones seems to be well hidden :-)  I am in 15.5.1

Any help would be appreciated.

Thanks, Alistair

Parents Reply Children
  • Thanks Manav. I look forward to running the SQL you provide :-)
  • Hi Alistair,

    Please check the below SQL, I hope it will work for you:

    select

         u.person_id,

         u.person_code LoginName,

         u.full_names FullName,

         u.status UserStatus,

         i.action_date Created,

         case u.status

         when 'N' then   (select top 1 action_date from object_audit_info i

                                 join audit_info_defs d on d.object_type = 4 and d.name = 'Changed' and d.action_type = i.action_type

                                 join audit_properties a on a.audit_entry_id = i. audit_entry_id and a.new_value='Not Active'

                                 join audit_property_defs p on p.property_id = a.property_id and p.object_type = 4 and p.field_name='Status'

                                 where

                                   i.object_type = 4

                                   and i.object_id = u.person_id

                                 order by i.action_date desc)

         else NULL

         end [Disabled]

    from persons u

    left join object_audit_info i on i.object_type = 4 and i.object_id = u.person_id and i.action_type = (select action_type from audit_info_defs where object_type = 4 and name = 'Created')

    order by 1

    Regards,
    Manav B.



    Answer Verified By: Deepti Patil