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
 SQL Server 2008 Forums
 Transact-SQL (2008)
 Limit Sql AD query

Author  Topic 

kdeutsch
Yak Posting Veteran

83 Posts

Posted - 2013-09-13 : 14:07:21
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;
   

- Advertisement -