Link to home
Start Free TrialLog in
Avatar of Jorge Ocampo
Jorge OcampoFlag for United States of America

asked on

powershell add exchange property to a report

Hi i have a report that has a samaccountnames of users i want to add information to the report another exchange property but since some users from that report are not mail enable if i were to run it in order i imagine it would not match the information to users

trying to get a powershell that would add the information and if not mail enable to output not mail enable
Avatar of Qlemo
Qlemo
Flag of Germany image

It would help us to help you if you could show some code, working as long as the account is mail enabled. Adding the missing bit is then easy for us.
Avatar of Jorge Ocampo

ASKER

i have attached an example so i want to add information in column C a specific property and if its not mail enable to write back not mail enable the script should execute for each tab
GLOBAL.xlsx
Just to be clear: You want to have an Excel workbook being processed, and Exchange info added to it?
@qlemo correct
ASKER CERTIFIED SOLUTION
Avatar of Mahesh
Mahesh
Flag of India image

Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
Try below code:

$scriptdir = Split-Path $MyInvocation.MyCommand.Definition -Parent

$resultfile = $ScriptDir + "\Resultlogs.csv"

$Allusers = Get-ADUser -Filter {(enabled -eq $true)} -Properties homeMDB, mailNickName,  mail, DisplayName, SamAccountName, Givenname, SurName

$Allusers | Select-Object Givenname, Surname, DisplayName, Mail, MailNickName, SamAccountName, homeMDB | Export-Csv $resultfile -NoTypeInformation

$export = Import-Csv $resultfile -Header "GivenName","SurName","DisplayName","Mail","MailNickName","SamAccountName","HomeMDB"

$export | % {
                if ($_.mailNickName -eq "") {$_.mailnickname = "No Mail Enabled"}
                
                }
$export | Export-Csv $resultfile -NoTypeInformation

Open in new window

Mahesh, please note that the Asker wants to use Excel with several tabs for this task.
SOLUTION
Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
@ Qlemo,

I agreed that requirement is to modify existing Excel file
However its seems difficult to traverse with each row under SamAccountName in excel file with ForEach loop
One option could be convert file to csv, process it and again convert it with Excel, however we need to work on each tab and it will create multiple csv files (one for each tab) and thus complicating the code and thus good amount of work involved

Hence as per my understanding if only AD user specific data along with mail enabled checking is required, you can get it directly from AD and save as csv, you may save csv to xlsx and keep only one file instead of having multiple tabs, Infact in my last script you can add location field which can help to further filter users based on their location if required which is the main reason for having multiple tabs as per my understanding

Again, its up to asker, we don't know if the report contains much more fields than attached here....

Mahesh.
Jorge, any feedback about the different suggestions?
My comment #a41970482 matches the request, as far as we can tell. It does exactly what has been asked for - if that is what the OP really means is a different story ...