• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 448
  • Last Modified:

Show users accessing database with same username from multiple locations and their count

SQL> select count(distinct(terminal)),username
  2  from dba_audit_session
  3  having count(distinct(terminal))>1
  4  group by username;

COUNT(DISTINCT(TERMINAL))          USERNAME                                              
-------------------------                           --------------                                        
                        2                                    AADM      

Need to find out from where (userhost,teminal,,,etc) its coming?
  • 3
  • 2
1 Solution
Well you have the basic query done. What part is not working? DBA_AUDIT_SESSION (TERMINAL and USERHOST) has most of that info. So what is the question?

If you are looking for multiple locations, you must use 'userhost' instead of 'terminal'... Since you can use multiple terminals on the same host (= same location).

If you want to know how to track down a host on a network, you should ask the question in the right zone... (there's commands like treaceroute, nslookup, etc. that allow tracing a network)

Cha1tuAuthor Commented:
@mrjoltcola: here its giving only username and COUNT(DISTINCT(TERMINAL))  right

I want to display some other cloums like USERHOST,TERMINAL,OS_USERNAME....

i want to know from which place(userhost) that user is connecting and wat is the terminal name
Industry Leaders: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

You are asking how to show count of distinct terminals, along with a row for each terminal (or host). That is combining a group / aggregate with a detail list. Sort of an odd query requirement. Just remove the count(distinct()) and add userhost and other needed columns, that are right there in dba_audit_session
Cha1tuAuthor Commented:
Can you give the query?
Just be aware that the TERMCOUNT is the overall count of terminals.

select distinct a.username, userhost, terminal, termcount from dba_audit_session a
  (select count(distinct(terminal)) termcount, username
   from dba_audit_session
   having count(distinct(terminal))>1
   group by username) b
 on a.username = b.username

Open in new window


Featured Post

What does it mean to be "Always On"?

Is your cloud always on? With an Always On cloud you won't have to worry about downtime for maintenance or software application code updates, ensuring that your bottom line isn't affected.

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