Tuesday, 5 February 2013

Creating Custom Tables in Powershell

When working with scripts, it is often necessary to collect, store and present information in a table format. An example of this could be to export a number of properties from a users mailbox in Exchange into a table format.

For my example, I will run the following command to export all the properties of a mailbox (mine) to a variable called $UserList

$UserList = get-mailbox -ResultSize Unlimited | Where-Object {$_.DisplayName -eq "Morrissey, Peter"}

The next thing to do is to declare/create the table. It can be called anything you like, but I tend to use $report. The following line of code creates the table;

$report = @()

Next, you need to populate each row of your table with whatever fields you require. This would usually be done with a ForEach statement where you cycle through a list of objects, with numerous properties for each object.

In my example, the object list is stored in $UserList. I can run the command $UserList | fl to display the list of all the properties available for each object in my variable.

This ForEach command will cycle through each entry in $UserList (in my example it is only a single entry for my mailbox) and add whatever properties of that object I declare into the table (I have used DisplayName and Database) but you can use as many as you like - they just need to be separated by a semi colon (;)

ForEach ($User in $UserList)
             $report += New-Object psobject -Property @{DisplayName=$($User.DisplayName);Database=$($User.Database)}

This is a simple example and will create a table with two values for each row; DisplayName and Database.

Once you have populated all the rows you require in your table, you can then export it to a CSV file using the following command;

$report | select DisplayName, Database | Export-CSV -path "C:\ReportOutput.csv"

Once again, you will need to replace $report with whatever variable you used to declare your table. You also need to use the Select component to select all the table fields you wish to export into the CSV file - in my case it was the DisplayName and Database fields.

Finally, once the CSV file is exported, you can then attach it to an email and send it to yourself (or someone else) as outlined in my previous blog post.

No comments:

Post a Comment