We help IT Professionals succeed at work.

Check out our new AWS podcast with Certified Expert, Phil Phillips! Listen to "How to Execute a Seamless AWS Migration" on EE or on your favorite podcast platform. Listen Now

x

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

Cha1tu
Cha1tu asked
on
Medium Priority
461 Views
Last Modified: 2013-12-18
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?
Comment
Watch Question

Top Expert 2009

Commented:
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?

Commented:
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

Author

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
Top Expert 2009

Commented:
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

Author

Commented:
Can you give the query?
Top Expert 2009
Commented:
Unlock this solution and get a sample of our free trial.
(No credit card required)
UNLOCK SOLUTION
Unlock the solution to this question.
Thanks for using Experts Exchange.

Please provide your email to receive a sample view!

*This site is protected by reCAPTCHA and the Google Privacy Policy and Terms of Service apply.

OR

Please enter a first name

Please enter a last name

8+ characters (letters, numbers, and a symbol)

By clicking, you agree to the Terms of Use and Privacy Policy.