Sql Sp not working correctly

• Stubby Bunny

  SSCommitted

  Points: 1733

  More actions

  September 13, 2013 at 10:16 am

  #282525

  I am trying to fins all AD logons that have not logged in for over 45 days, the follwoing is my Query but when it return is returns all records instead of just a few and it should not.

  ALTER PROCEDURE usp_45Daylate

  -- Add the parameters for the stored procedure here

  AS

  BEGIN

  -- SET NOCOUNT ON added to prevent extra result sets from

  -- interfering with SELECT statements.

  SET NOCOUNT ON;

  --Create a temp table to put everyone in

  Create table #Tempad (sAMAccountname varchar(60), lastlogon datetime)

  --get all the ad accounts

  SelectsAMAccountName,

  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

  Fromopenquery(ADSI,'<LDAP://MY Ladp>;(&(objectClass=User)(objectCategory=Person));sAMAccountName,lastLogontimeStamp;Subtree')

  INSERT #Tempad (sAMAccountname, lastlogon)

  select sAMAccountname, lastlogon from #Tempad where datediff(dd, lastlogon, GETDATE()) >= 45

  END

  GO