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)
 Create Sp to get 45 day no logons fom Ad
 New Topic  Reply to Topic
 Printer Friendly
Author Previous Topic Topic Next Topic  

kdeutsch
Yak Posting Veteran

USA
83 Posts

Posted - 09/13/2013 :  12:09:17  Show Profile  Reply with Quote
I am trying to get all the people whom have not logged into AD after 45 days, so if the lastlogontimestamp is greater than 45 days thye should appear, but my SP does not appear to work as it return everyone in AD not matter how I switch it.


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
Select sAMAccountName,
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://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

James K
Flowing Fount of Yak Knowledge

3588 Posts

Posted - 09/13/2013 :  12:18:47  Show Profile  Reply with Quote
I think you need to rearrange the LDAP query and insert something like shown below. The way you have it now, you are simply inserting data from #Tempad back into itself.
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
;WITH cte AS (
Select sAMAccountName, 
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://MY Ladp>;(&(objectClass=User)(objectCategory=Person));sAMAccountName,lastLogontimeStamp;Subtree')
)
INSERT #Tempad (sAMAccountname, lastlogon)

select sAMAccountname, lastlogon from cte where datediff(dd, lastlogon, GETDATE()) >= 45;

select * from #TempAd; -- if selecting it here is the only purpose, you don't even need to insert it into a temp table.
END
GO
Go to Top of Page

kdeutsch
Yak Posting Veteran

USA
83 Posts

Posted - 09/13/2013 :  12:51:11  Show Profile  Reply with Quote
Hi,

Ok tried it out, but I get the redline error under the field names for selecting from the cte select statement.

select sAMAccountname, lastlogon from cte where datediff(dd, lastlogon, GETDATE()) >= 45;


When I cut this out and try to select through the #TempAd select I get no results.
Go to Top of Page

kdeutsch
Yak Posting Veteran

USA
83 Posts

Posted - 09/13/2013 :  12:56:08  Show Profile  Reply with Quote
HI,
Ok I got it working, dont use CTE expression but i figured it out. thanks for the help.
Go to Top of Page

James K
Flowing Fount of Yak Knowledge

3588 Posts

Posted - 09/13/2013 :  12:56:17  Show Profile  Reply with Quote
Make the change shown below in red:
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
;WITH cte AS (
Select sAMAccountName, 
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 lastLogon
From openquery(ADSI,'<LDAP://MY Ladp>;(&(objectClass=User)(objectCategory=Person));sAMAccountName,lastLogontimeStamp;Subtree')
)
INSERT #Tempad (sAMAccountname, lastlogon)

select sAMAccountname, lastlogon from cte where datediff(dd, lastlogon, GETDATE()) >= 45;

select * from #TempAd; -- if selecting it here is the only purpose, you don't even need to insert it into a temp table.
END
GO
Go to Top of Page
  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.06 seconds. Powered By: Snitz Forums 2000