Solved

VBScript or PowerShell for to generate report from Active Directory

Posted on 2015-02-18
6
137 Views
Last Modified: 2015-02-23
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..
0
Comment
Question by:TJOSY
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 4
  • 2
6 Comments
 
LVL 53

Expert Comment

by:Will Szymkowski
ID: 40616696
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.
0
 
LVL 53

Expert Comment

by:Will Szymkowski
ID: 40618996
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.
0
 

Author Comment

by:TJOSY
ID: 40619634
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
0
What is SQL Server and how does it work?

The purpose of this paper is to provide you background on SQL Server. It’s your self-study guide for learning fundamentals. It includes both the history of SQL and its technical basics. Concepts and definitions will form the solid foundation of your future DBA expertise.

 

Author Comment

by:TJOSY
ID: 40619721
Need your help to get the result in the attached format
Export-Template.csv
0
 
LVL 53

Expert Comment

by:Will Szymkowski
ID: 40619794
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.
0
 
LVL 53

Accepted Solution

by:
Will Szymkowski earned 500 total points
ID: 40619843
If you removed the HiddenFromAddressList all you would have to do is the following...

import-module activedirectory
Get-AdUser -Filter * -Properties displayName, samAccountName, Description, title, mail, department, AccountExpires, AccountExpirationDate, Enabled, telephoneNumber, MOBILEPhone, Manager, Office, Company | select displayName, samAccountName, Description, title, mail, department, AccountExpires, AccountExpirationDate, Enabled, telephoneNumber, MOBILEPhone, Manager, Office, Company | export-csv "c:\UsersStats.csv" -notypeinformation

Open in new window


Will.
0

Featured Post

Free Tool: Path Explorer

An intuitive utility to help find the CSS path to UI elements on a webpage. These paths are used frequently in a variety of front-end development and QA automation tasks.

One of a set of tools we're offering as a way of saying thank you for being a part of the community.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

This article demonstrates probably the easiest way to configure domain-wide tier isolation within Active Directory. If you do not know tier isolation read https://technet.microsoft.com/en-us/windows-server-docs/security/securing-privileged-access/s…
Always backup Domain, SYSVOL etc.using processes according to Microsoft Best Practices. This is meant as a disaster recovery process for small environments that did not implement backup processes and did not run a secondary domain controller that ne…
Microsoft Active Directory, the widely used IT infrastructure, is known for its high risk of credential theft. The best way to test your Active Directory’s vulnerabilities to pass-the-ticket, pass-the-hash, privilege escalation, and malware attacks …
Attackers love to prey on accounts that have privileges. Reducing privileged accounts and protecting privileged accounts therefore is paramount. Users, groups, and service accounts need to be protected to help protect the entire Active Directory …

751 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question