Want to protect your cyber security and still get fast solutions? Ask a secure question today.Go Premium

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 27159
  • Last Modified:

Last logon for an Oracle user

hi,
I need to pull out a report for the all users in the database  with their last logoff time.
I already tried dba_audit_session....
There are some users who login and log out serveral times, I need only their last log off info...
Need help with the query from sys.aud $ table....
Oracle version - 11.2.0.1
thank you
gyans
0
gyans
Asked:
gyans
  • 3
  • 3
1 Solution
 
Guy Hengel [angelIII / a3]Billing EngineerCommented:
you want to start with this:
select userid, max(ntimestamp)
from sys.aud$
where action# in (100,101,102)
group by userid
0
 
slightwv (䄆 Netminder) Commented:
Assuming you are currently auditing logoffs, another way

select username, max(logoff_time) from dba_audit_trail group by username;


http://docs.oracle.com/cd/E11882_01/server.112/e40402/statviews_3081.htm#REFRN23023
0
 
gyansAuthor Commented:
hi Hengel,
It gives me an error with the timestamp being an invalid identifier.

Slightwv- cannot get the info from this view.
thanks
0
Get your Conversational Ransomware Defense e‑book

This e-book gives you an insight into the ransomware threat and reviews the fundamentals of top-notch ransomware preparedness and recovery. To help you protect yourself and your organization. The initial infection may be inevitable, so the best protection is to be fully prepared.

 
slightwv (䄆 Netminder) Commented:
>>Slightwv- cannot get the info from this view.

Log in as a privileged user.

If that doesn't work, what version of Oracle are you using (all 4 numbers please).
0
 
gyansAuthor Commented:
slightwv - we only store audit for 1 week and move the rest to the archive table.
The archive table is identical to aud$ table, so I can only query from this table.
thanks
0
 
slightwv (䄆 Netminder) Commented:
>>so I can only query from this table. thanks

Then you don't really want the LAST logoff?  Just the last logoff you can see?

I'm not an Expert on auditing but the archive table you have should have similar columns to what Guy posted above.

Just describe the table and then check the online docs for your version and see what columns and audit actions are what you need.
0
 
gyansAuthor Commented:
i wrote a query to get the last log off from the archive table .
0

Featured Post

Concerto's Cloud Advisory Services

Want to avoid the missteps to gaining all the benefits of the cloud? Learn more about the different assessment options from our Cloud Advisory team.

  • 3
  • 3
Tackle projects and never again get stuck behind a technical roadblock.
Join Now