• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 453
  • 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
Free Tool: Subnet Calculator

The subnet calculator helps you design networks by taking an IP address and network mask and returning information such as network, broadcast address, and host range.

One of a set of tools we're offering as a way of saying thank you for being a part of the community.

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

Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

Join & Write a Comment

Featured Post

Upgrade your Question Security!

Your question, your audience. Choose who sees your identity—and your question—with question security.

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