SQL Server Forums
Profile | Register | Active Topics | Members | Search | Forum FAQ
 
Register Now and get your question answered!
Username:
Password:
Save Password
Forgot your Password?

 All Forums
 SQL Server 2008 Forums
 Transact-SQL (2008)
 Limit Sql AD query
 New Topic  Reply to Topic
 Printer Friendly
Author Previous Topic Topic Next Topic  

kdeutsch
Yak Posting Veteran

USA
83 Posts

Posted - 09/13/2013 :  14:07:21  Show Profile  Reply with Quote
I am querying the Users OU to get all my users whom are over 45 day login but i have sub OU's of the User ou and it grabs these as well, I don't want that to happen, how can I limit the query to jus tthe OU users with no looking at the sub OU's.

;WITH cte AS (
Select sAMAccountName Name,
distinguishedName Dname,
CASE WHEN CAST(lastLogontimeStamp AS BIGINT) = 0 THEN NULL ELSE CONVERT(varchar(19),(DATEADD(mi,(CAST(lastLogontimeStamp AS BIGINT) / 600000000) + DATEDIFF(Minute,GetUTCDate(),GetDate()),CAST('1/1/1601' AS DATETIME2))), 120) END lastLogontimeStamp
From openquery(ADSI,'<LDAP://MYLdap/OU=Users,RestLADAP>;(&(objectClass=User)(objectCategory=Person));sAMAccountName,lastLogontimeStamp,distinguishedName;Subtree')
)

select cte.Name, cte.lastLogontimeStamp, cte.Dname from cte where datediff(dd, lastLogontimeStamp, GETDATE()) >= 45;
  Previous Topic Topic Next Topic  
 New Topic  Reply to Topic
 Printer Friendly
Jump To:
SQL Server Forums © 2000-2009 SQLTeam Publishing, LLC Go To Top Of Page
This page was generated in 0.2 seconds. Powered By: Snitz Forums 2000