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.

2 comments:

  1. Hello!

    I really liked your short guide in creating table. I have one problem with your code, I do not get several columns, I only get one column, like this:

    AccountName : Cellomics
    Description : test
    Description2 : test2

    AccountName : Cellomics_agent
    Description : test
    Description2 : test2

    AccountName : Cellomics_analy
    Description : test
    Description2 : test2

    AccountName : Cellomics_dbe
    Description : test
    Description2 : test2

    AccountName : Cellomics_integ
    Description : test
    Description2 : test2

    AccountName : Cellomics_poly
    Description : test
    Description2 : test2

    AccountName : Cellomics_rep_c
    Description : test
    Description2 : test2

    AccountName : Cellomics_repcl
    Description : test
    Description2 : test2

    AccountName : Cellomics_repor
    Description : test
    Description2 : test2


    I would really like to have it like this:
    AccountName Description Description2
    ==================== =========================== ======================
    Cellomics test test2
    Cellomics_agent test test2

    and so on

    How do I get it like that?

    BR
    Jorgen Jonsson
    nljorgenj@gmail.com

    ReplyDelete
    Replies
    1. Try forcing the output to use the "format table (ft)" function/method

      So for the example above I would type "$report | ft" (without the quotes) to display it as a table instead of a list

      Delete