How to find out who is currently logged into DLP

book

Article ID: 160553

calendar_today

Updated On:

Products

Data Loss Prevention Enforce

Issue/Introduction

Occasionally there is need to see who is currently logged into DLP

Resolution

The following SQL script will provide a short report as to who has been logged into DLP, but has not logged out.

Unfortnuately, some people don't log out from DLP properly.  They simply close their browser and go on their merry way leaving behind an open entry in the USER SESSION table.  As such the query is constrained to those who have logged in within the last 2 days, 48 hours before the point int time that the query is run (see the "sysdate-2").  To change this, simply replace the 2 with however many days in the past you wish to include.

To use, log into SQLPlus as protect (or the DLP schema owner), and type the following:

set pagesize 100
column logged_on format a24
column userid format 99999999
column username format a30

select Distinct u.userid userid,
       pu.name username,
       max(u.logontime) logged_on
  from usersession u,
       protectuser pu
 where u.logofftime is NULL
   and u.logontime >(sysdate-2)
   and u.userid = pu.userid
   and u.userid<>1
 group by u.userid, pu.name;

Some notes:

The "distinct" clause is used incase a user logged in withotu logging out multiple times during the time period.

The userid of 1 is excluded as this is the "Adminstrator" account. 

The script simply displays a list of who has logged in, but has not logged out.