Wednesday, April 19, 2017

Extracting MS Active Directory Users

The following user defined function returns query against MS Active Directory:

USE active_directory
GO

SET ANSI_NULLS ON
GO

SET QUOTED_IDENTIFIER ON
GO

PRINT 'Date: ' + CONVERT(VARCHAR(24),GETDATE(),13) + '   Server Name: ' + @@servername + '   Service Name: ' + @@servicename
GO

IF EXISTS
      (SELECT *
          FROM sys.objects
          WHERE name = 'implementation.ADusers')
        DROP FUNCTION implementation.ADusers
        PRINT 'DROP FUNCTION implementation.ADusers'
GO

PRINT 'CREATE FUNCTION implementation.ADusers'
GO
CREATE FUNCTION implementation.ADusers()

/************************************************************************************************************************************
AD users

Usage: EXEC sp_executesql @strSQL

Change Log
 #    Date       Who  Change
*************************************************************************************************************************************/
 RETURNS nvarchar(max)
 BEGIN
    DECLARE @strSQL1 AS varchar(600)
    ,       @strSQL2 AS varchar(max)
    ,       @strSQL3 AS varchar(100)
    ,       @strSQL  AS nvarchar(max)

    SET @strSQL1 = 'SELECT samaccountname,distinguishedname,mail,mobile,telephonenumber,extensionattribute14,displayname,name,givenName,sn,useraccountcontrol,objectsid,employeeID,employeeNumber,company,title,streetAddress,postalCode,l,st,co,physicalDeliveryOfficeName,manager
    ,CAST(CASE WHEN accountexpires = 0 or accountexpires = 0x7FFFFFFFFFFFFFFF then null
                    ELSE dateadd(day, (accountexpires / (1e7 * 60 * 60 * 24)), cast(''16000101'' as datetime2))
                END AS datetime
            ) FROM (
    '
   
    SELECT @strSQL2 = COALESCE(@strSQL2, ' ') + alpha_union.strSQL
    FROM (
    SELECT char(decimal#) alpha
    ,'SELECT  samaccountname,distinguishedname,mail,mobile,telephonenumber,extensionattribute14,displayname,NAME,givenName,sn,useraccountcontrol,objectsid,employeeID,employeeNumber,company,title,streetAddress,postalCode,l,st,co,physicalDeliveryOfficeName,manager,accountExpires = CAST(accountExpires AS bigint)
        FROM OPENQUERY(active_directory,''SELECT samaccountname,distinguishedname,mail,mobile,telephonenumber,extensionattribute14,displayname,name,givenName,sn,useraccountcontrol,objectsid,employeeID,employeeNumber,company,title,streetAddress,postalCode,l,st,co,physicalDeliveryOfficeName,manager,accountExpires
        FROM ''''LDAP://OU=Organisation,DC=corporate,DC=com,DC=au''''
        WHERE    objectCategory = ''''Person'''' and    objectClass = ''''USER'''' and samaccountname = '
     + '''''' + CHAR(decimal#) + '*' + ''''''''
     + IIF(CHAR(decimal#) <> 'z','
    ) UNION ALL
     ','')    strSQL
    FROM (select row_number() over (order by (select 1)) as decimal#
    FROM sys.all_columns) cte_tally
    WHERE decimal# > 96 and decimal# < 123) alpha_union

    SET @strSQL3 = (SELECT ')) t   
    WHERE t.distinguishedName LIKE ''%OU=Users%''')

    SET @strSQL = @strSQL1 + @strSQL2 + @strSQL3

    RETURN @strSQL
END

No comments:

Post a Comment