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

How do I find out all the users, with there IP address that are currently logged into ActiveDirectory

I have aASPx page that is basically a phone listing of all the users in Active Directory.  I would like to be able list if they are active logged in and what their IP address is. I found a SQL function that would convert the lastLogon. Is this even possible?
0
ssmacwilliams
Asked:
ssmacwilliams
  • 4
  • 2
  • 2
1 Solution
 
YZlatCommented:
I have implemented something similar using a linked server to AD

Step 1: Create a linked server to your Active Directory:

sp_addlinkedserver 'ADSI'
, 'Active Directory Service Interfaces'
, 'ADSDSOObject'
, 'adsdatasource'
Step 2: Create a view in SQL server using OPENQUERY to select from Active Directory. For example (replace the 'x's etc with suitable values for your AD)

SELECT * FROM OpenQuery(ADSI,  
'SELECT givenName
, sn
, department
, description
, telephoneNumber
, mail
, userAccountcontrol  
FROM ''LDAP://DC=xxx,DC=xx,DC=xxx,DC=xx''  
WHERE objectCategory = ''Person'' AND objectClass = ''User''')
 
0
Technology Partners: 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!

 
YZlatCommented:

SELECT      
        CAST(objectGUID AS BINARY(16)) AS ActiveDirID,  
        CAST(SAMAccountName AS VARCHAR(20)) AS UserID,  
        CAST(ISNULL(mail, '') AS VARCHAR(50)) AS Email,  
        CAST(ISNULL(telephonenumber, '') AS CHAR(4)) AS PhoneExt,  
        CAST(ISNULL(CN, '') AS VARCHAR(50)) AS FullName,  
        CAST(ISNULL(givenname, '') AS VARCHAR(50)) AS FirstName,
        CAST(ISNULL(SN, '') AS VARCHAR(50)) AS LastName,  
        CAST(ISNULL(distinguishedname, '') AS VARCHAR(500)) AS Organizations,  
        CAST(ISNULL(department, '') AS VARCHAR(50)) AS Department,  
        CAST(ISNULL(facsimileTelephoneNumber, '') AS VARCHAR(12)) AS Fax,
        CAST(ISNULL(extensionAttribute1,'') AS VARCHAR(25)) AS SalesClass
FROM         OPENQUERY(ADSI,  
                      '
SELECT  objectGUID,  
        CN,
        SN,
        mail,
        telephonenumber,
        SAMAccountName,
        givenname,
        distinguishedname,
        department,
    facsimileTelephoneNumber,
        extensionAttribute1
 
FROM    ''LDAP://servername/DC=****,DC=***''  
 
WHERE   userPrincipalName=''*''
AND     objectclass= ''person''
 
AND NOT CN=''#*''  
AND     NOT SN=''@*''
AND NOT givenname =''****''  
 
')
0
 
sameer2010Commented:
select client_net_address from sys.dm_exec_connections - all currently logged in users to SQL server
0
 
ssmacwilliamsAuthor Commented:
YZlat,
    I had already created my Linked server and was able to get the data the basic data you listed, but not the IP address or if they are logged in.. However, I attempted to run your query to see if it listed something I didn't have; but got the error: "Cannot fetch a row from OLE DB provider ADSDOOBJECT for linked server ADSI"

sameer 2010,
   I tried you query, which was pretty neat, but this is server based...I think. How do I relate that to active directory user?
0
 
sameer2010Commented:
Check select * from sys.dm_exec_sessions...I do not have AD installed and hence, cannot check out what does it return for these users.
Also, is it a possibility to do the following?
1. Start the trace event
2. Get data using sp_trace_generateevent events 14, 17?
0
 
ssmacwilliamsAuthor Commented:
I was able to create joins on this. Thanks.
0

Featured Post

Has Powershell sent you back into the Stone Age?

If managing Active Directory using Windows Powershell® is making you feel like you stepped back in time, you are not alone.  For nearly 20 years, AD admins around the world have used one tool for day-to-day AD management: Hyena. Discover why.

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