asked on

VBScript or PowerShell for to generate report from Active Directory

Hi, require assistance to generate report (csv or xls) from Active Directory for users with below information. The script should be able to customize to specific AD OU's.

The report should have following information in the excel\csv:-
Account (enable or disabled)
Account with Expire (yes or no)
Expiry date (if expiry enabled)
HIDDEN FROM ADDRESS LIST (yes or no)
displayName
mail
samAccountName
Description
title
department
telephoneNumber
MOBILE
Manager
physicalDeliveryOfficeName,
Company

Thank you..

Will Szymkowski

You are trying to get information from multiple cmdlets and export them to a CSV. For this you will need to create a hash table and create a new object and out-put this information. See command below...

Also do you have a list of users or you just want to query all users in your domain?

This will get all info from all AD Accounts in your environment

Import-Module activedirectory
$AllUsers = Get-ADUser -Filter * -Properties samaccountname
Foreach ($User in $AllUsers) {
$ADStats = Get-ADUser -Identity $User -Properties *
$EXCHStats = Get-Mailbox -Identity $User
$Data =@{'Account Enabled / Disabled'=$ADStats.Enabled;
         'Account Expires'=$ADStats.AccountExpires;
         'Account Expires Date'=$ADStats.AccountExpirationDate;
         'Hidden From Address Book'=$EXCHStats.HiddenFromAddressListsEnabled;
         'DisplayName'=$ADStats.DisplayName;
         'Email'=$ADStats.mail;
         'sAMAccountName'=$ADStats.SamAccountName;
         'Description'=$ADStats.Description;
         'Title'=$ADStats.Title;
         'Department'=$ADStats.Department;
         'Phone #'=$ADStats.telephoneNumber;
         'Mobile #'=$ADStats.MobilePhone;
         'Manager'=$ADStats.Manager;
         'Office'=$ADStats.Office;
         'Company'=$ADStats.Company
           }
        $Obj = New-Object -TypeName psobject -Property $Data
        Write-Output $Obj | out-file <filename.csv> -append
 }

Open in new window

If you want to do it from a txt file use the below command...

Import-Module activedirectory
$AllUsers = Get-Content <filename.txt>
Foreach ($User in $AllUsers) {
$ADStats = Get-ADUser -Identity $User -Properties *
$EXCHStats = Get-Mailbox -Identity $User
$Data =@{'Account Enabled / Disabled'=$ADStats.Enabled;
         'Account Expires'=$ADStats.AccountExpires;
         'Account Expires Date'=$ADStats.AccountExpirationDate;
         'Hidden From Address Book'=$EXCHStats.HiddenFromAddressListsEnabled;
         'DisplayName'=$ADStats.DisplayName;
         'Email'=$ADStats.mail;
         'sAMAccountName'=$ADStats.SamAccountName;
         'Description'=$ADStats.Description;
         'Title'=$ADStats.Title;
         'Department'=$ADStats.Department;
         'Phone #'=$ADStats.telephoneNumber;
         'Mobile #'=$ADStats.MobilePhone;
         'Manager'=$ADStats.Manager;
         'Office'=$ADStats.Office;
         'Company'=$ADStats.Company
           }
        $Obj = New-Object -TypeName psobject -Property $Data
        Write-Output $Obj | out-file <filename.csv> -append
 }

Open in new window

Running this command for a single user

Import-Module activedirectory
$User = Read-Host "Enter your samaccountname here"
$ADStats = Get-ADUser -Identity $User -Properties *
$EXCHStats = Get-Mailbox -Identity $User
$Data =@{'Account Enabled / Disabled'=$ADStats.Enabled;
         'Account Expires'=$ADStats.AccountExpires;
         'Account Expires Date'=$ADStats.AccountExpirationDate;
         'Hidden From Address Book'=$EXCHStats.HiddenFromAddressListsEnabled;
         'DisplayName'=$ADStats.DisplayName;
         'Email'=$ADStats.mail;
         'sAMAccountName'=$ADStats.SamAccountName;
         'Description'=$ADStats.Description;
         'Title'=$ADStats.Title;
         'Department'=$ADStats.Department;
         'Phone #'=$ADStats.telephoneNumber;
         'Mobile #'=$ADStats.MobilePhone;
         'Manager'=$ADStats.Manager;
         'Office'=$ADStats.Office;
         'Company'=$ADStats.Company
           }
        $Obj = New-Object -TypeName psobject -Property $Data
        Write-Output $Obj

Open in new window

Will.

Will Szymkowski

Just another note, what version of Exchange are you running? You might need to add the following snapin to each of the scripts for the version of Exchange you are running.

Exchange 2007 - add-pssnapin microsoft.exchange.management.powershell.admin
Exchange 2010 - add-pssnapin microsoft.exchange.management.powershell.E2010
Exchange 2013 - add-pssnapin microsoft.exchange.management.powershell.E2013

Will.

TJOSY

ASKER

Thank you Will. infact im getting error while running the first script.
im using exchange 2013. can you guide me where to place "add-pssnapin microsoft.exchange.management.powershell.E2013" command to the script

TJOSY

ASKER

Need your help to get the result in the attached format
Export-Template.csv

Will Szymkowski

Unfortunately, because we are using hash tables to grab the data from multiple cmdlets this is the only way to present the data. However, you removed the attribute HiddenFromAddressList this command would be much easier, because you can just use a single cmdlet.

Will.

ASKER CERTIFIED SOLUTION

Will Szymkowski

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial