Cha1tu
asked on
Show users accessing database with same username from multiple locations and their count
SQL> select count(distinct(terminal)),
2 from dba_audit_session
3 having count(distinct(terminal))>
4 group by username;
COUNT(DISTINCT(TERMINAL)) USERNAME
------------------------- --------------
2 AADM
Need to find out from where (userhost,teminal,,,etc) its coming?
2 from dba_audit_session
3 having count(distinct(terminal))>
4 group by username;
COUNT(DISTINCT(TERMINAL)) USERNAME
------------------------- --------------
2 AADM
Need to find out from where (userhost,teminal,,,etc) its coming?
mrjoltcola
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?
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
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
ASKER
@mrjoltcola: here its giving only username and COUNT(DISTINCT(TERMINAL)) right
I want to display some other cloums like USERHOST,TERMINAL,OS_USERN
i want to know from which place(userhost) that user is connecting and wat is the terminal name
I want to display some other cloums like USERHOST,TERMINAL,OS_USERN
i want to know from which place(userhost) that user is connecting and wat is the terminal name
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
ASKER
Can you give the query?
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.