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?
ssmacwilliamsAsked:
Who is Participating?
 
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
Ultimate Tool Kit for Technology Solution Provider

Broken down into practical pointers and step-by-step instructions, the IT Service Excellence Tool Kit delivers expert advice for technology solution providers. Get your free copy now.

 
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
 
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
 
ssmacwilliamsAuthor Commented:
I was able to create joins on this. Thanks.
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.

All Courses

From novice to tech pro — start learning today.