This discussion has been locked.
You can no longer post new replies to this discussion. If you have a question you can start a new discussion

Searching and exporting account information from Active Directory

I would like to do the following with AD accounts:

Export the following fields from Active Directory to an Excel sheet with the appropriate headers:

Under AD Account's General Tab: First Name, Last name, Display Name, Description, Office, Telephone Number, E-mail

Under AD Account's Address Tab: Street, City, State/province, Zip/Postal Code, Country/region

Under Organization Tab: Job Title, Department, Company, Manager

In addition, I want to control the extraction of data based on a combination of things such as accounts missing the Manager Name, Accounts without a Street, Accounts withou Phone Numbers, etc. At first, I want to be able to pull all of this information out of a root domain and all of its sub-domains.

Can someone provide examples of how to do the above in addition to any links to websites and books that may help me manipulate this data and other AD data (i.e. extracting the members of a security or distribution list to an Excel document, mailbox and it's aliases, etc).

Thank you.

  • Quick and dirty is to just extract the user data as described in my post on your other question and then use excell to manipulate the data - unless this is a reguar report and then the easiest way or to do a separate task for each report.

    Here is an example report looking for accounts that do not have an employeeID set.  I don't have enough time to walk through the logic here today - I tend to comment my scripts anyway so hopefully it makse sense to you.

    #===========================================================================
    # Script Name..: Email-missing-EmpID-report
    $scriptversion = "2.0"
    $taskDN = "CN=Email missing employeeID report,CN=HR Tasks,CN=Scheduled Tasks,CN=Server Configuration,CN=Configuration"
    #===========================================================================
    #
    # Version .....: 1.1 - now emails a 2nd report of Clan8OtherDomain users
    # Version .....: 1.2 - allows the script to run as either a shell script or scheduled task automatically
    # Version .....: 1.3 - removed redundant section for loading the ActiveRoles cmdlets.
    #
    ############################################################################
    ################# HELPER FUNCTIONS ########################################
    ###########################################################################
    #
    # Global variables
    $errorinRun = $false
    $errormsg = ""
    #
    #
    function GetTaskParameters
    {
    try {
      $Task.DirObj.GetInfo()
      $Task.DirObj.GetInfoEx(@("edsaParameters"),0)
      $strParameters = $Task.DirObj.Get("edsaParameters")
    }
    catch {
      try {
       $Task = Get-QADObject -Identity $TaskDN  -Proxy -IncludedProperties edsaParameters
       $strParameters = $Task.edsaParameters
      }
      catch {
       Out-File -filepath $logfile -inputobject "FATAL ERROR: Failed to get script parameters"   
       throw "FATAL ERROR: Failed to get script parameters"
      }
    }
    $strParameters = '<parameters>' + $strParameters + '</parameters>'
    if( $Task ){ if ( $Task -is [IDisposable] ){ try { $Task.Dispose() } catch{} } }; Remove-Variable -Name Task -ErrorAction SilentlyContinue
    return $strParameters
    }
    #
    #
    function emailsupport
    {
    param(
      $fromemail = "ActiveRolesServer@MyDomain.com" ,
      $toemail =   "Lee.Andrews@MyDomain.com",
      $bodyText = "********** User objects without EmployeeIDs set REPORT ATTACHED **********",
      $fileattachment = "C:\Scripts\Identity Management\Logs\MissingEmpID\PSEmpIDMissing.csv",
      $smtpserver = "smtp.drama.clan8.com"
    )

    Out-File -filepath $logfile -inputobject "`nIn Email Function" -Append   
    Out-File -filepath $logfile -inputobject "Email to        : $toemail" -Append   
    Out-File -filepath $logfile -inputobject "Email From      : $fromemail" -Append   
    Out-File -filepath $logfile -inputobject "Email Body Text : $bodytext" -Append   

    # create mail objects
    # Create Message  
        $message = New-Object  System.Net.Mail.MailMessage
        $message.Subject = "NEW User objects (Last 7 days) created without an EmployeeID set or Title set with an underscore." 
        $message.Body = $bodyText
        $message.from = $fromemail
        $message.to.add($toemail)
    # create attachment
    if ($fileattachment.length -gt 0)
    {
      if (Test-Path $fileattachment)
      {
       $attachment = New-Object System.Net.Mail.Attachment -ArgumentList $fileattachment
       $message.Attachments.Add($attachment)
      }
    }
    # Set SMTP Server and create SMTP Client 
    $server = $smtpserver
    $client = New-Object system.net.mail.smtpclient $server    
    # Send the message 
    try { $client.Send($message) } 
    catch {
      Out-File -filepath $logfile -inputobject "`n***** ERROR - SCRIPT failed to EMAIL SUPPORT *****" -Append      
      $errorinRun = $true
      $errormsg = $errormsg + " : Failed to send email : "
    }
    }

    ##############################################################################
    #################### DIRECT CALL FROM SCHEDULED TASK #########################
    ##############################################################################
    ##
    ## Retrieve Scheduled Task Parameters
    ##
    ##
    $logfile      = "C:\Scripts\Identity Management\Logs\MissingEmpID\PS-MissingEmpID.log"
    $starttime    = Get-Date -DisplayHint time
    Out-File -filepath $logfile -inputobject "Starting Script at     : $starttime "   
    $strParameters = GetTaskParameters
    $xmlParameters = [xml]$strParameters
    $emailTo     = [string]$xmlParameters.parameters.emailTo    
    $smtpserver    = [string]$xmlParameters.parameters.smtpServer

    #   
    # set defaults if the task parameters are missing
    if ( ($emailTo -eq $null) -or ($emailTo.Length -le  1) ) {$emailTo= "Lee.Andrews@clan8.com" }
    if ( ($smtpserver -eq $null) -or ($smtpserver.Length -le  1) ) {$smtpserver= "smtp.drama.clan8.com" }
    $outfile = "C:\Scripts\Identity Management\Logs\MissingEmpID\Clan8PSEmpIDMissing.csv"
    $targetdate = $(Get-Date).adddays(-8)
    $noeid = get-qaduser -searchroot "Configuration/Managed Units/User Management/Admin Action Required/Users without EmployeeID" -proxy -IncludedProperties sAMAccountName,givenname,sn,displayaname,mail,telephonenumber,lastlogon,creationdate -SizeLimit 0
    $userswithoutEmpID = 0
    $noeid | ForEach-Object { if ( $_.creationdate -gt $targetdate ) { $userswithoutEmpID++ } }
    if ($userswithoutEmpID -gt 0 )
    {
    $bodyText = "********** Clan8 User objects without EmployeeIDs set REPORT ATTACHED **********`n`nSee https://qst.Clan8.ad.clan8.com/MIAdmin/CustomCommands.aspx?TaskID=UnitContent&TargetClass=edsManagedUnit&DN=CN%3dUsers+without+EmployeeID%2cCN%3dAdmin+Action+Required%2cCN%3dUser+Management%2cCN%3dManaged+Units%2cCN%3dConfiguration`n`n`nIf the account is a service or test account update the Job Title with any Description that includes an underscore, e.g. Test_Account, Telephone_Account, Facilities_Account or Service_Account.`n`n`nDo not reply to this email.`n`n If you are unable to access the link above it could be because you do not have permissions, email Lee.Andrews@clan8.com if you need to be given access to the Active Roles Server Web Portal" 
    $noeid | select NTAccountName,sAMAccountName,givenname,sn,displayaname,mail,telephonenumber,lastlogon,creationdate |  where { $_.creationdate -gt $targetdate } | Export-Csv $outfile -NoTypeInformation
    $errormsg = "Email sent to support to deal with new Clan8 users without an EmployeeID set"
    emailsupport -toemail $emailTo -smtpserver $smtpserver -bodytext $bodytext -fileattachment "C:\Scripts\Identity Management\Logs\MissingEmpID\Clan8PSEmpIDMissing.csv"
    }
    Out-File -filepath $logfile -inputobject $("Clan8 user accounts found without employeeeIDs    : "+ $noeid.count ) -Append
    Out-File -filepath $logfile -inputobject $noeid -Append
    # repeat for Clan8OtherDomain user objects
    $noeid = get-qaduser -searchroot "Configuration/Managed Units/User Management/Admin Action Required/Clan8OtherDomain Users without EmployeeID" -proxy -IncludedProperties sAMAccountName,givenname,sn,displayaname,mail,telephonenumber,lastlogon,creationdate  -SizeLimit 0
    $userswithoutEmpID = 0
    $noeid | ForEach-Object { if ( $_.creationdate -gt $targetdate ) { $userswithoutEmpID++ } }
    $otherdomainusers = Get-QADUser -Service "otherdomain.otherdomain.com" -LdapFilter "(!(employeeID=*))"  -IncludedProperties sAMAccountName,givenname,sn,displayaname,mail,telephonenumber,lastlogon,creationdate -SizeLimit 0
    if ( $otherdomainusers.count -gt 0  ) { $noeid += $otherdomainusers }
    $outfile = "C:\Scripts\Identity Management\Logs\MissingEmpID\Clan8OtherDomainPSEmpIDMissing.csv"
    if ($userswithoutEmpID -gt 0 )
    {
          $bodyText = "********** Clan8OtherDomain User objects without EmployeeIDs set REPORT ATTACHED **********`n`nSee https://qst.Clan8.ad.clan8.com/MIAdmin/CustomCommands.aspx?TaskID=UnitContent&TargetClass=edsManagedUnit&DN=CN%3dClan8OtherDomain+Users+without+EmployeeID%2cCN%3dAdmin+Action+Required%2cCN%3dUser+Management%2cCN%3dManaged+Units%2cCN%3dConfiguration`n`n`nIf the account is a service or test account update the Job Title with any Description that includes an underscore, e.g. Test_Account, Telephone_Account, Facilities_Account or Service_Account.`n`n`nDo not reply to this email.`n`n If you are unable to access the link above it could be because you do not have permissions, email Lee.Andrews@clan8.com if you need to be given access to the Active Roles Server Web Portal" 
    $noeid | select NTAccountName,sAMAccountName,givenname,sn,displayaname,mail,telephonenumber,lastlogon,creationdate |  where { $_.creationdate -gt $targetdate } | Export-Csv $outfile -NoTypeInformation
    if ( $errormsg -eq "Email sent to support to deal with new Clan8 users without an EmployeeID set" )
    {
          $errormsg = "Email sent to support to deal with new Clan8OtherDomain and Clan8 users without an EmployeeID set"
          }
    else   
    {
                $errormsg = "Email sent to support to deal with new Clan8OtherDomain users without an EmployeeID set"
          }
    emailsupport -toemail $emailTo -smtpserver $smtpserver -bodytext $bodytext -fileattachment "C:\Scripts\Identity Management\Logs\MissingEmpID\Clan8OtherDomainPSEmpIDMissing.csv"
    }
    Out-File -filepath $logfile -inputobject $("User accounts found without employeeeIDs    : "+ $noeid.count ) -Append
    Out-File -filepath $logfile -inputobject $noeid -Append
    $endtime = Get-Date -DisplayHint time
    Out-File -filepath $logfile -inputobject "Script run finished at : $endtime" -Append
    if ( $errorinRun -eq $false )
    {
    $errormsg = "No NEW users found without EmployeeID set"
    }
    throw $errormsg

  • If you really want to do this by way of scripting (and have the time and inclination to build and maintain the scripts, that's cool).  I love scripting very much myself.

    BUT, if your boss might prefer you spend time doing other things, perhaps have a look at Enterprise Reporter:

    software.dell.com/.../

    It can get you all the information you described in a scheduled fashion exported as either formatted or unformatted (delimited text) output.