Create Sp to get 45 day no logons fom Ad

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)

Create Sp to get 45 day no logons fom Ad

Author

Topic

kdeutsch
Yak Posting Veteran

83 Posts

Posted - 2013-09-13 : 12:09:17

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
Master Smack Fu Yak Hacker

3873 Posts

Posted - 2013-09-13 : 12:18:47

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

kdeutsch
Yak Posting Veteran

83 Posts

Posted - 2013-09-13 : 12:51:11

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.

kdeutsch
Yak Posting Veteran

83 Posts

Posted - 2013-09-13 : 12:56:08

HI,
Ok I got it working, dont use CTE expression but i figured it out. thanks for the help.

James K
Master Smack Fu Yak Hacker

3873 Posts

Posted - 2013-09-13 : 12:56:17

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

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources