• Post category:BI Publisher
  • Post comments:0 Comments
  • Post last modified:July 18, 2023
  • Reading time:2 mins read
You are currently viewing How to fetch the current logged-in Users list?

In this article, let’s try to look into extracting the logged in users list.

This list will be of importance to the IT admin and security admin to see if there are any suspicious activities going in non-business hours or if anyone is gaining control of the suspended users.

SQL Query to fetch current logged in users:

SELECT pu.username "LOGIN_USERNAME"
    ,papf.person_number
	,ppnf.full_name
	,pu.SUSPENDED
	,pu.creation_date
	,max(fs.LAST_CONNECT) "LAST_CONNECT"
	,max(fs.LAST_UPDATE_DATE) "LAST_UPDATE_DATE"
FROM per_users pu
	,FND_SESSIONS fs
	,per_person_names_f ppnf
	,per_all_people_f papf
WHERE 1 = 1
	AND pu.user_guid = fs.user_guid
	AND trunc(fs.LAST_CONNECT) = trunc(SYSDATE)
	and pu.person_id = ppnf.person_id(+)
	and pu.person_id = papf.person_id(+)
	and ppnf.name_type(+) = 'GLOBAL'
	and sysdate between ppnf.effective_start_date(+) and ppnf.effective_end_date(+)
	and sysdate between papf.effective_start_date(+) and papf.effective_end_date(+)
GROUP BY pu.username
	,pu.SUSPENDED
	,pu.creation_date
	,papf.person_number
	,ppnf.full_name
ORDER BY pu.username DESC