ssmacwilliams
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?
https://www.experts-exchange.com/questions/21198843/List-of-users-currently-logged-on.html
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=x xx,DC=xx''
WHERE objectCategory = ''Person'' AND objectClass = ''User''')
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
, userAccountcontrol
FROM ''LDAP://DC=xxx,DC=xx,DC=x
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(telephonenumbe
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(distinguishedn
CAST(ISNULL(department, '') AS VARCHAR(50)) AS Department,
CAST(ISNULL(facsimileTelep
CAST(ISNULL(extensionAttri
FROM OPENQUERY(ADSI,
'
SELECT objectGUID,
CN,
SN,
mail,
telephonenumber,
SAMAccountName,
givenname,
distinguishedname,
department,
facsimileTelephoneNumber,
extensionAttribute1
FROM ''LDAP://servername/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
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?
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
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
I was able to create joins on this. Thanks.