ADSI Queries - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

General SQL Server Forums

New to SQL Server Programming

ADSI Queries

Author

Topic

SQLlost
Starting Member

2 Posts

Posted - 2011-01-26 : 15:18:44

I am trying to access information within AD and having a difficult time. I need to query information for when a computer last logged onto my network. Last logon comes is received in nanoSeconds not DateTime format, and I am trying to verify which user logged onto the computers listed and am receiving an error of " Msg 7321, Level 16, State 2, Line 1". The following queries work, but I would like to join them and display what system belongs to what user. Can anyone help me figure this one out?

SELECT *
FROM OPENQUERY (ADSI,
'SELECT Name,LastLogon
FROM ''LDAP://OU=xxxxx,OU=xxxx,DC=xxxx,DC=xxx''
WHERE objectCategory=''Person'' AND objectClass=''user''
ORDER BY name')

SELECT *
FROM OPENQUERY (ADSI,
'SELECT Name,LastLogon
FROM ''LDAP://OU=xxxx,OU=xxxx,DC=xxxx,DC=xxx''
WHERE objectCategory=''computer'' AND objectClass=''computer''
ORDER BY LastLogon')

I am on OS 2K3 with SQL 2K5

Thanks in advance
Gary

jackv
Master Smack Fu Yak Hacker

2179 Posts

Posted - 2011-01-31 : 12:26:18

Try : 1)place results in temp table and then process accordingly or 2)UNION (or UNION ALL) , assuming the columns are the same .Th second approach might be clearer if you post the column names

Jack Vamvas
--------------------
http://www.sqlserver-dba.com

SQLlost
Starting Member

2 Posts

Posted - 2011-04-01 : 12:26:47

Jack, I know it has been a while since I last posted this problem, but I am now back to work on it. The following query pulls all my necessary information, with the exception of the "LastLogon" which comes in nanosecond format. Is there a way that I might be able to convert this into DateTime format?

SELECT *
FROM OPENQUERY(ADSI, '
SELECT samAccountName, sn, givenName, title, displayName
,department,mail, telephonenumber,LastLogon
,physicalDeliveryOfficeName
FROM ''LDAP://OU=Standard Mailbox,OU=MU Users,DC=domain,DC=com''
WHERE objectClass=''user'' OR objectClass<>''computer''
ORDER BY displayname')

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources