DECLARE @PastDays INT
SET @PastDays = 30;
DECLARE @StartPastDays INT
SET @StartPastDays = 1;
DECLARE @date DATETIME
SET @date = '20000101'
DECLARE @days INT
SET @days = DATEDIFF(day, @date, GETDATE())
DECLARE @StartDate DATETIME
SET @StartDate = DATEADD(day, @days-(@PastDays+@StartPastDays-1), @date)
DECLARE @EndDate DATETIME
SET @EndDate = DATEADD(day, @days-@StartPastDays+1+1, @date)
PRINT @StartDate
PRINT @EndDate
IF OBJECT_ID('TempDB..#ADComputer', 'U') IS NOT NULL
DROP TABLE #ADComputer;
Create Table #ADComputer(
name nvarchar(256)
, description nvarchar(256)
, lastLogon bigint
, whenChanged datetime
, distinguishedName nvarchar(256)
, operatingSystem nvarchar(256)
, operatingSystemVersion nvarchar(256)
, operatingSystemServicePack nvarchar(256)
)
INSERT INTO #ADComputer
(name,description,lastLogon,whenChanged,distinguishedName,operatingSystem,operatingSystemVersion,operatingSystemServicePack)
SELECT
name,description,lastLogon,whenChanged,distinguishedName,operatingSystem,operatingSystemVersion,operatingSystemServicePack
FROM OPENQUERY (
ADSI, '<LDAP://{DomainController}>;(&(objectCategory=computer)(objectClass=computer));name,description,lastLogon,whenChanged,distinguishedName,operatingSystem,operatingSystemVersion,operatingSystemServicePack;subtree'
)
SELECT name as 'Computer Name'
, CASE WHEN CHARINDEX('OU=', distinguishedName)> 0
THEN REPLACE(SUBSTRING(distinguishedName,CHARINDEX
('OU=',distinguishedName)+3,CHARINDEX('DC=', distinguishedName)-CHARINDEX('OU=', distinguishedName)-4),',OU=','/')
WHEN CHARINDEX('CN=', distinguishedName, 3) > 0
THEN REPLACE(SUBSTRING(distinguishedName,CHARINDEX('CN=',distinguishedName,
3)+3,CHARINDEX('DC=', distinguishedName)-CHARINDEX('CN=', distinguishedName, 3)-4),',CN=','/')
ELSE 'None'
END AS 'OU'
, REPLACE(SUBSTRING(distinguishedName,CHARINDEX('DC=',distinguishedName)+3,LEN(distinguishedName)-2-
CHARINDEX('DC=',distinguishedName)),',DC=','.') AS 'Domain'
, description AS 'Description'
, CONVERT(bigint, DATEDIFF([day],(DATEADD([second], (nullif(lastLogon,0)-116444736000000000)/10000000, '1/1/1970')),GetUtcDate())) AS 'Last Logon Time (Days)'
, DATEADD([second], (nullif(lastLogon, 0)-116444736000000000)/10000000+DATEDIFF(second, GETUTCDATE(), GETDATE()), '1/1/1970') AS 'Last Logon Time'
, dateadd([second], DATEDIFF(second, GETUTCDATE(), GETDATE()), whenChanged) AS 'When Changed'
, operatingSystem AS [OS]
, operatingSystemVersion AS [OS Version]
, operatingSystemServicePack AS [OS Service Pack]
FROM #ADComputer
-- WHERE whenChanged >= DATEADD([day], -30, GetUtcDate())
WHERE (whenChanged < @StartDate)
ORDER BY whenChanged DESC
DROP TABLE #ADComputer