Link to home
Start Free TrialLog in
Avatar of ssmacwilliams
ssmacwilliamsFlag for United States of America

asked on

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?
Avatar of YZlat
YZlat
Flag of United States of America image

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''')
 

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 =''****''  
 
')
select client_net_address from sys.dm_exec_connections - all currently logged in users to SQL server
Avatar of ssmacwilliams

ASKER

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?
ASKER CERTIFIED SOLUTION
Avatar of sameer2010
sameer2010
Flag of India image

Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
I was able to create joins on this. Thanks.