How to get a list of userID from LDAP distribution list

IN a project, you may require to create user groups based on existing distribution groups in AD (active directory). You may collaborate with AD team to provide you the information. If you have access to LDAP, you may try the follow

Initial Setup:

1. Download Softerra LDAP Administrator (free) and install.
2. Click New to add a new profile
3. For the hostname please ask AD team for the address.
4. Click Next
5. Choose currently logged on user
6. Finish

How to search and export the details

1. You should see the LDAP server on the left, right click and select Directory Search
   
   
2. Enter the DL under Filter. Example of the format is cn=dl.sap_basis and click search
   
3. Highlight the search result and paste it onto a notepad. You will below text
   ldap://ssvldcp01.dell.com:389/CN=DL.SAP_Basis,OU=Groups/DLs,DC=dell,DC=com
4. Now replace <FROM_THE_SEARCH_RESULT> to the above search result
   (&(objectCategory=user)(memberOf=<FROM_THE_SEARCH_RESULT>))
   
5. Final text should look like
   (&(objectCategory=user)(memberOf=CN=DL.SAP_Basis,OU=Groups/DLs,DC=dell,DC=com))
   
6. Highlight and copy the text from step 5.
   
7. Back to Softerra,
   
8. Paste it under Quick Search (top left) and press enter.
   
9. Then paste the same text again to the filter filter. Press enter.
   
   
10. You will have a list of UserID belonging to the DL.
    
11. Now export the data. File > Export Data > CSV > Finish
    
12. Open the *.CSV file with Microsoft Excel and you will get the UserID under column BD called "sAMAccountName"

Extracting Text Between Brackets

1. There are times you will need to extract TEXT between bracket.
2. Eg: (Userid) and extract userid to another cell.
3. Use this formula below
   =MID(C2,FIND("(",C2)+1,FIND(")",C2)-FIND("(",C2)-1)

Excel - remove duplicate and replace it with blanks

If I have data like:

A          B          C
111        asdews     iujfhg
111        oikufjf    jasddaf
112        eifjfjc    olkdkj
112        eiejdj     olokjjfki
112        ioeiurjf   oleodks
113        oeiekdkd   poldkkmd

I would like to delete all duplicates except the first instance but keep the cell blank for all subsequent duplicates. The expected result:

A          B          C
111        asdews     iujfhg
           oikufjf    jasddaf
112        eifjfjc    olkdkj
           eiejdj     olokjjfki
           ioeiurjf   oleodks
113        oeiekdkd   poldkkmd

The only way I've found to delete duplicates is the standard filter that will just remove all duplicates. I can't do this since I have the other cols like B and C that have data. The reason I need this is due to the program I need to import and it will overwrite each line with the next if it has the same ID. Since I need them to append to the same ID, not overwrite, I have to somehow manage this task. I have roughly a million lines across multiple documents to do this with so a quick method is preferable if at all possible.

(NOTE: I do have access to Excel so if this is easier or better using Excel please explain that process and I will try it there. I also don't have any problems downloading other open source software to accomplish this, as long as you can explain the method of achieving this using a suggested software.

Sub RemoveRepeatingStrings()

    Dim BaseStr As String, CurrStr As String
    Dim EndRow As Long

    EndRow = Range("A" & Rows.Count).End(xlUp).Row
    BaseStr = Range("A1").Value

    Application.ScreenUpdating = False

    For Iter = 2 To EndRow
        CurrStr = Range("A" & Iter).Value
        If CurrStr = BaseStr Then
            Range("A" & Iter).Value = vbNullString
        Else
            BaseStr = Range("A" & Iter).Value
        End If
    Next Iter

    Application.ScreenUpdating = True

End Sub

Source http://stackoverflow.com/questions/20793967/how-to-delete-duplicates-but-keep-the-first-instance-and-a-blank-cell-for-the-d

Excel - Deleting text from cell a space character

Try this:

=LEFT(A1,FIND(" ",A1)-1)

Useful SAP Security Tcodes

Security - General tcodes

SM20

Analysis of Security Audit Log

SM19

Security Audit Configuration

STRUSTSSO2

Trust Manager for Logon Ticket

STRUST

Trust Manager

SECSTORE

Administration of Secure Storage

SM20N

Analysis of Security Audit Log

SM18

Reorganize Security Audit Log

CERTREQ

Certificate enrollment

SLAW

License Administration Workbench

WSSPROFILE

Edit Web Services Security Profile

LICENSE_ADMIN

License Administration Workbench

SM20_OLD

Security Audit Log Evaluation (Old)

CERTMAP

Certificate Assignment

Security - Audit Information System tcodes

RSPFPAR

Display profile parameter

RSUSR200

List of Users per Login Date

RSSCD100

Display Change Documents

RSUSR003

Check standard user passwords

RSCSAUTH

Maintain/Restore Authorization Group

RSUSR000

Currently Active Users

SECR

Audit Information System

RSEIDOC2

IDoc List

RSRFCTRC

RFC Trace

RSTBHIST

Table history

RSAU_SELECT_EVENTS

Display Audit Events (Batch Proc.)

RSABAPSC

Statistical Prog. Anal. for Search

RSRFCCHK

RFC destinations with logon data

RSGWLST

Accessible Gateways

RSSTAT20

Performance Analysis: Single Stats

RSSTAT10

Performance Analysis: Workload Anal.

RSWBO040

Search for Objects in Requests/Tasks

RSUSR200_PWDCHG180

Unchanged for 180 Days

RSWBOSSR

RSWBOSSR

RSPFPAR_LOGIN

Logon Rules

RSRSDEST

System Overview Output

RSINFO00_BCE_AUD_MOD

Customer Exits

RSSCD150

Display Change Documents

RSUSR007

List Users

SM30V_DDAT

Call of SM30 for View V_DDAT

RSAUDITC_BCE

Display Locked Transactions

0REP

"Start of program

RSWBO004

Set System Change Option

RBDAUD01

Statistical Evaluations for AL

SM30V_BRG

Call of SM30 for View V_BRG

RSABTPGP

Authorization Groups

RSABAUTH

Transfer of Authorization Groups

RSUSR002_AUDIT_OSCL

Users who can call OS commands

RSAUDITM_BCE_TPLGA

Transport Monitor ALOG

RSUSR002_AUDIT_UCC

Update Company Codes

RSTMSCON_VERBOSE

Verbose

RSPO0055

Installation Check: Spool

RSPFPAR_GATEWAY

SAP Gateway

RSUSR200_INITPASS

Users with Initial Password

RSPFPAR_SAPSTAR

Hardcoded SAP*

RSPFPAR_SYSLOG

Syslog Parameters

RSUSR002_AUDIT_RFC

Users who can execute RFC functions

RSAUDITM_BCE_TPLGS

Transport Monitor SLOG

RSTMSDIC

TMS: Display Configuration

RSPFPAR_SNC

SNC

RSWBO040_AUDIT_PA

Requests with PA tables

RSUSR002_AUDIT_ABAP

Users with ABAP Authorization

RSPFPAR_TABLEREC

Table Recording

RSAUDITM_BCE_IMPO

Import Overview

RSUSR002_AUDIT_UAP

Update Accounting Periods

RSSNCSRV

SNC Status of Application Server

RSPFPAR_AUTH

Authorization All

RSSWOUSR

List of Internet users

RSPFPAR_PROFGEN

Profile Generator

RSUSR200_UNUSED30

Not Logged On for 30 Days

RSPFPAR_SPOOL

Spool Parameters

RSWBO040_AUDIT_USR

Requests with USR tables

RSUSR002_AUDIT_CTS

Users who can use CTS

RSPFPAR_TABLESTAT

Table Access Statistics

RSAUDITM_BCE_SYSO

System Overview

RSUSR002_AUDIT_UCA

Update Chart of Accounts

RSTMSAMO

TMS: Alert Viewer

RSPFPAR_CALLSYSTEM

Call System

RSRFCSTX

RFC statistics

RSPFPAR_RFC

Remote Function Call

RSPFPAR_STATISTICS

Workload Statistics

RSWBO050

Analyze Objects in Orders/Tasks

RDDTDDAT_BCE

Check Table Logging

Security - Directory tcodes

LDAP

LDAP Customizing and Test

RSLDAPSYNC_USER

LDAP Synchronization of Users

LDAPMAP

Maintain LDAP Attribute Assignment

LDAPLOG

Analyze LDAP Log

Security - Secure Network Communications tcodes

SNC0

SNC Access Control List: Systems

SNC1

Generate SNC name for user

SNC4

Check canonical SNC names

Security - Secure Store and Forward tcodes

SSFA

SSF: Set Application Parameters

PSEMAINT

PSE Management

O07C

Obsolete transaction

Security - User and Authorization Management tcodes

SU01

User Maintenance

PFCG

Role Maintenance

SU53

Evaluate Authorization Check

SUIM

User Information System

SU24

Auth. Obj. Check Under Transactions

SU3

Maintain Users Own Data

SU10

User Mass Maintenance

SLICENSE

Administer SAP Licenses

SU25

Upgrade Tool for Profile Generator

SU21

Maintain Authorization Objects

PFUD

User Master Data Reconciliation

SU22

Auth. Object Usage in Transactions

SU01D

User Display

USMM

Customer measurement

SU56

Analyze User Buffer

SCUL

Central User Administration Log

SCUM

Central User Administration

SMEN

Session Manager Menu Tree Display

SU03

Maintain Authorizations

SE97

Maint. transaction call authorizatn

SUPC

Role Profiles

SU02

Maintain Authorization Profiles

SU20

Maintain Authorization Fields

SUGR

Maintain User Groups

SCUA

Central User Administration

SCUG

Transfer Users

SU2

Maintain Own User Parameters

SU52

Maintain Own User Parameters

SUCOMP

User company address maintenance

SU0

Maintain Own Fixed User Values

RSSCD100_PFCG

Change Documents for Role Admin.

SU1

Maintain Own User Address

S_BCE_68001425

Roles by Complex Criteria

S_BCE_68001398

Users According to Complex Criteria

WP3R

Follow-Up Processes for Portal Roles

S_BCE_68001400

Users According to Complex Criteria

SU12

Mass Changes to User Master Records

SU51

Maintain Own User Address

SU26

Upgrade Tool for Profile Generator

S_BCE_68001439

For user

SU50

Own data

S_BCE_68002041

Executable for Role

S_BCE_68001430

Compare Users

S_BCE_68001420

Roles by Transaction Assignment

MENU_MIGRATION

Menu Migration into New Hierarchy

S_BCE_68001402

With Unsuccessful Logons

SU99

Call report RSUSR008

SDMO

Dynamic Menu (old)

S_BCE_68001401

Critical Combinations of Auth.

S_BCE_68001423

Roles by Authorization Values

SU01_NAV

User maint. to include in navigation

S_BCE_68001429

Transactions for User

ROLE_CMP

Compare Roles

AUTH_SWITCH_OBJECTS

Switch on/off authorizations

SUUM

Global User Manager

S_BCE_68001426

Transactions for User

SUUMD

Display User Administration

SUPO

Maintain org. levels

S_BCE_68001422

Roles by Authorization Object

S_BCE_68001393

Users by address data

S_BCE_68001777

Compare Roles

RSSCD100_PFCG_USER

For Role Assignment

SU_REFUSERVARIABLE

Maintain reference user variables

LICENSE_ATTRIBUTES

Maintain License Attributes of Roles

S_BCE_68001399

Users According to Complex Criteria

SM30_SSM_VAR

Maintain Table SSM_VAR

S_BCE_68001397

Users According to Complex Criteria

SU55

Call the Session Manager menus

SU98

Call Report RSUSR008

S_BCE_68001441

For authorizations

S_BCE_68001409

Profiles According to Complex Crit.

S_BCE_68001440

For profiles

S_BCE_68001418

Roles by Role Name

SUGRD

Display user groups

SM30_SSM_RFC

Maintain Table SSM_RFC

SU24_CHECK

Switch Off Authorizations: Test

PERSREG

Personalization object

S_BCE_68001394

Users According to Complex Criteria

SM30_SSM_CUST

Maintain Table SSM_CUST

SCUC

CUA: Synchronize company addresses

S_BCE_68001405

Profiles by Authorization Name

S_BCE_68001419

Roles by User Assignment

AUTH_DISPLAY_OBJECTS

Display Active Authorization Objects

S_BCE_68001413

Auth. Objects According to Complex

S_BCE_68001427

Transactions for User

SUIM_OLD

Call AUTH Reporting Tree (Info Sys.)

S_BCE_68001395

Users According to Complex Criteria

S_BCE_68001396

Users According to Complex Criteria

S_BCE_68001431

Compare Profiles

SROLE

Export User Roles to XML doc.

S_BCE_68001412

Auth. Objects According to Complex

SUPO_PREPARE

Maintain Organizational Levels

S_BCE_68001406

Profiles by Values

S_BCE_68001410

Auth. Objects According to Complex

S_BCE_68001414

Auth. According to Complex Criteria

S_BCE_68001407

Profiles by Changes

S_BCE_68001408

Profiles by Roles

S_BCE_68001432

Compare Authorizations

S_BCE_68001416

Authorizations by Changes

S_BCE_68001436

Where-used lists

S_BCE_68001421

Roles by Profile Assignment

S_BCE_68001433

Comparisons

S_BCE_68001437

Where-used lists

S_BCE_68001434

Where-used lists

S_BCE_68001403

With Critical Authorizations

S_BCE_68001438

Where-used lists

S_BCE_68001767

By Profile Name or Text

S_BCE_68001411

Auth. Objects According to Complex

S_BCE_68001415

Authorizations by Values

S_BCE_68001435

Where-used lists

S_BCE_68001404

Profiles by Contained Profiles

S_BCE_68001424

Roles by Change Data

S_BCE_68001428

Transactions for User

S_BIE_59000199

Report cross-system information

SM30_PRGN_CUST

Maintain Table SSM_CUST

SM30_VAL_AKH

Maintain Table VAL_AKH

S_BCE_68002111

RSUSR008_009_NEW

SALE_CUA

Display ALE Customizing for CUA

SPERS_MAINT

Personalization object processing

SUGR_NAV

Maintain User Groups

S_BIE_59000198

Report cross-system information

SU83

Archive authorization docs.

SPERS_TEST

Test personalization objects

SU87

Read Authorization Change Documents

How to check Active Directory account is locked or not

1. From Window > Start > Run  > CMD
2. Enter below command:
   
   NET USER myuserid /DOMAIN | FIND /I "Account Active"