• 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?
0
Cha1tu
Asked:
Cha1tu
  • 3
  • 2
1 Solution
 
mrjoltcolaCommented:
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?
0
 
PilouteCommented:
Hi,

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)

Cheers,
P
0
 
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
0
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.

 
mrjoltcolaCommented:
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
0
 
Cha1tuAuthor Commented:
Can you give the query?
0
 
mrjoltcolaCommented:
Just be aware that the TERMCOUNT is the overall count of terminals.

select distinct a.username, userhost, terminal, termcount from dba_audit_session a
  join  
  (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


0
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