Creating a DataTable in PowerShell

Hi Folks ,

I haven’t been blogging as you can see for a long time . So I thought I can create a pretty cool blog about DataTables . I’m not talking about DataSets which are made up of more than one table .

Why are DataTables cool ? Well it provides an easy way to create structured output . Things always look nice when they are structured in tables . The Datatable is created in memory . The cool thing about that is that you can do some sorting before dumping it to a file or on the screen . I won’t go into sorting but I just wanted to show how simple it is to create a datatable.So lets get to it .

We are going to do something cool with this . Lets create a table of the logical drives on the local machine . We are going to want to find out the following :

Drive Letter

Size of Drive

Used (How much of the drive is used )

Free Space (How much of the drive is free)

Percent Free

So right off the bat we have 5 columns . So to start off we know the number of columns . We have to know what kind of Data Types these columns are going hold . Here is what I come up with

Drive Letter — String

Size of Drive–Decimal

Used — Decimal

Free — Decimal

Percent Free —INT

Now if you want to provide int values for the decimal ones you could . In this case I choose not to .

Now that we have our table laid out lets start the script . You can do this interactively also so there is no need to put this in a script . I tend to use scripts allot just to revert back to.

Lets Start :

  1. Get the local Server Name to be used in the tableName

$ServerName = Get-WmiObject -Class Win32_ComputerSystem
$TableName = $ServerName.Caption

Here is a way to get the local Server Name . We will use this name for the DataTable . There are other ways to get the name of the server but I chose this method . We are getting a WMI Class called WIN32_ComputerName and the caption property exposes the Server Name .

Once we have the name now we are able to setup the table :

  1. Setup the Datatable Structure

$table = New-Object system.Data.DataTable “$TableName”

Here we are instantiating a new object . This is ADO stuff creating out DataTable object and giving it our local Machine name .

Now we are going to setup our columns and the Datatypes we talked about .

$col1 = New-Object system.Data.DataColumn Drive,([string])
$col2 = New-Object system.Data.DataColumn Size,([decimal])
$col3 = New-Object system.Data.DataColumn Used,([decimal])
$col4 = New-Object system.Data.DataColumn Free,([decimal])
$col5 = New-Object system.Data.DataColumn PercentFree,([int])

Pretty straight forward stuff . We are creating the DataColumn object ,giving it a name ,declaring what data type it holds and assigning a variable to it .

Now that we created the columns now we have to add it to the table . We do that here :

#Add the Columns
$table.columns.add($col1)
$table.columns.add($col2)
$table.columns.add($col3)
$table.columns.add($col4)
$table.columns.add($col5)

Now Since we want the logical drive info here we create a variable to the WMI Logical Drive Class

  1. create our WMI Object

$drives = Get-WmiObject -Class Win32_LogicalDisk -ComputerName $TableName

Now comes the pipeline :)

$drives | ? {$_.DriveType -eq 3 } | % {$row = $table.NewRow();$row.Drive = $_.DeviceID ; $row.Size = “{0:F3}” -f($_.Size/1gb) ; $row.Used = “{0:F3}” -f (($_.Size/1gb)-($_.FreeSpace/1gb)) ; $row.Free = “{0:F3}” -f ($_.FreeSpace/1gb); $row.PercentFree = “{0:F3}” -f ($_.FreeSpace/1gb) / ($_.Size/1gb) * 100 ;$table.Rows.Add($row) }

Lets go thru this :

$drives – We start out with all of our Drives (Even Floppy ,CD/DVD)

? {$_.DriveType -eq 3 — This say Where the Drive Type is equal to 3 (We only want the Hard Drives . 3 means Hard drives not optical or Floppy )

% {$row = $table.NewRow() — For Each Drive Create a new Row (Cause we want our a Drive per Row )

$row.Drive = $_.DeviceID — Ok Now remeber our First Column is our Drive Letter (String ). Using the DeviceID to get the Name (I.E. C,D,E,F)

$row.Size = “{0:F3}” -f($_.Size/1gb) — Next column is the Size of the Drive . WE are using the Size property . Also we are making everything equal in Gigabytes. We also are formating here that is what {0:F3} does it formats it to the 3rd decimal place . (comes in real Handy these formating parameters)

$row.Used = “{0:F3}” -f (($_.Size/1gb)-($_.FreeSpace/1gb)) — Next Column is the Used Space . Which is the Size minus the Free space . Again Formating to the 3rd decimal place.

$row.Free = “{0:F3}” -f ($_.FreeSpace/1gb) — Next is Freespace Calculation also done in GB’s . Note you can aslo use the 1mb if you like . To keep it uniform I used the 1gb for all calculations .

$row.PercentFree = “{0:F3}” -f ($_.FreeSpace/1gb) / ($_.Size/1gb) * 100 — Here is the last Percent Free space . Which is Free Space divided by the Size Times 100 .

Now that we have all that taken care of lets capture our output to the screen .

$table| format-table -AutoSize

We tell it to take the table and format it . The AutoSize parameter basically pushes everything together and takes the whitespace out between the columns .

So the result is this :

Drive Size Used Free PercentFree
—– —- —- —- ———–
C: 78.125 42.848 35.277 45
D: 302.733 10.916 291.818 96
E: 136.719 87.683 49.036 36
F: 170.898 7.969 162.930 95
G: 243.043 60.144 182.899 75
J: 298.081 111.776 186.305 63

(It looks better on the actual output )

We are almost done . We can also pipe this table out to an XML file and XSD file . With this output you can create nice reports with XML possibly also parse the XML file to display info on a web page or in an application here are the last 2 lines .

#Save Table

$table.WriteXml(“.\DriveInfo.xml”)
$table.WriteXmlSchema(“.\DriveInfo.xsd”)

One is the XML file and the other is the Schema which tells how the XML file was created . With this 2 lines you can reconstuct the table at any time using ReadXML and ReadXMLSchema .

Here is the full Script :

  1. Get the local Server Name to be used in the tableName

$ServerName = Get-WmiObject -Class Win32_ComputerSystem
$TableName = $ServerName.Caption

  1. Setup the Datatable Structure

$table = New-Object system.Data.DataTable “$TableName”
$col1 = New-Object system.Data.DataColumn Drive,([string])
$col2 = New-Object system.Data.DataColumn Size,([decimal])
$col3 = New-Object system.Data.DataColumn Used,([decimal])
$col4 = New-Object system.Data.DataColumn Free,([decimal])
$col5 = New-Object system.Data.DataColumn PercentFree,([int])
#Add the Columns
$table.columns.add($col1)
$table.columns.add($col2)
$table.columns.add($col3)
$table.columns.add($col4)
$table.columns.add($col5)

  1. create our WMI Object

$drives = Get-WmiObject -Class Win32_LogicalDisk -ComputerName $TableName

$drives | ? {$_.DriveType -eq 3 } | % {$row = $table.NewRow();$row.Drive = $_.DeviceID ; $row.Size = “{0:F3}” -f($_.Size/1gb) ; $row.Used = “{0:F3}” -f (($_.Size/1gb)-($_.FreeSpace/1gb)) ; $row.Free = “{0:F3}” -f ($_.FreeSpace/1gb); $row.PercentFree = “{0:F3}” -f ($_.FreeSpace/1gb) / ($_.Size/1gb) * 100 ;$table.Rows.Add($row) }

$table| format-table -AutoSize

#Save Table

$table.WriteXml(“.\DriveInfo.xml”)
$table.WriteXmlSchema(“.\DriveInfo.xsd”)

About these ads