Exporting
PowerShell's ability to manipulate objects is pretty formidable. We've seen how PowerShell permits you to control the output format of an expression or cmdlet. However, PowerShell even has the ability to change or export the object into something else.
Export-CSV
A comma separated value (CSV) file is a mainstay of administrative scripting. It's a text-based database that can be parsed into an array or opened in a spreadsheet program like Microsoft Excel. The cmdlet requires an input object that is typically the result of a piped cmdlet:
Get-process | export-csv processes.csv
When you run this command on your system it creates a text file called processes.csv. When the file is opened in a spreadsheet program, you'll be amazed by the amount of information that is available. In fact, it's probably overkill for most situations.
Here's another version of basically the same expression except this time we're using Select-Object to specify the properties we want returned:
PS C:\> get-process |select-object name,id,workingset,cpu| ` >> export-csv processes.csv >> PS C:\> get-content processes.csv #TYPE System.Management.Automation.PSCustomObject Name,Id,WorkingSet,CPU acrotray,3996,6574080,0.7110224 ApntEx,1456,4718592,6.5894752 Apoint,1592,7147520,6.0787408 avgamsvr,436,7389184,4.155976 avgcc,1684,12288000,10.4550336 avgemc,860,22593536,9.5036656 avgupsvc,828,3182592,1.6824192 BCMWLTRY,2948,6508544,16.2333424 Client,1084,1019904,140.6121904 cmd,1288,1093632,0.5207488 csrss,868,3440640,82.7790304 cvpnd,4000,8015872,9.6538816 EXCEL,2452,6545408,6.6996336 explorer,484,35528704,801.6427056 firefox,3028,71385088,881.2872288 Groove,2032,11628544,25.3264176 Idle,0,16384, inetinfo,3012,5357568,1.4420736 Microsoft.Crm.Application.Hoster,1796,28168192,14.7812544 MSASCui,1732,10907648,7.310512 MsMpEng,1832,14114816,107.8951456 MsPMSPSv,2932,1425408,1.0114544 nvsvc32,1788,3522560,2.5236288 powershell,1560,53522432,7.9314048 procexp,588,10452992,86.1438688 rapimgr,2380,6225920,3.4850112 PS C:\>
This produces a raw data report that we can further process any way we want. For example, if the Out-File already exists it will be overwritten unless you use -NoClobber. If you don't want the #TYPE header, which we find distracting, specify -NoTypeInformation as part of the Export-CSV cmdlet.
On a related note, PowerShell also has an Import-CSV cmdlet that reads the contents of the csv file and displays the data as a table. Here's an example with abbreviated output:
PS C:\> import-csv processes.csv Name Id WorkingSet CPU ---- -- ---------- --- acrotray 3996 6574080 0.7110224 ApntEx 1456 4718592 6.5894752 Apoint 1592 7147520 6.0787408 avgamsvr 436 7389184 4.155976 avgcc 1684 12288000 10.4550336 avgemc 860 22593536 9.5036656 avgupsvc 828 3182592 1.6824192 cmd 1288 1093632 0.5207488 csrss 868 3440640 82.7790304 cvpnd 4000 8015872 9.6538816 EXCEL 2452 6545408 6.6996336 ...
Export-CliXML
If you prefer to store results as an XML file, perhaps for processing by other tools, you can use PowerShell's Export-CliXML cmdlet. It works much the same way as Export-CSV:
PS C:\>get-wmiobject -class win32_processor |export-clixml wmiproc.xml
This creates an XML file called wmiproc.xml that can be imported back into PowerShell using Import-CliXML:
PS C:\> import-clixml wmiproc.xml AddressWidth : 32 Architecture : 0 Availability : 3 Caption : x86 Family 6 Model 9 Stepping 5 ConfigManagerErrorCode : ConfigManagerUserConfig : CpuStatus : 1 CreationClassName : Win32_Processor CurrentClockSpeed : 1598 CurrentVoltage : 33 DataWidth : 32 Description : x86 Family 6 Model 9 Stepping 5 DeviceID : CPU0 ErrorCleared : ErrorDescription : ExtClock : 133 Family : 2 InstallDate : L2CacheSize : 1024 L2CacheSpeed : LastErrorCode : Level : 6 LoadPercentage : Manufacturer : GenuineIntel MaxClockSpeed : 1598 Name : Intel(R) Pentium(R) M processor 1600MHz OtherFamilyDescription : PNPDeviceID : PowerManagementCapabilities : PowerManagementSupported : False ProcessorId : A7E9F9BF00000695 ProcessorType : 3 Revision : 2309 Role : CPU SocketDesignation : Microprocessor Status : OK StatusInfo : 3 Stepping : 5 SystemCreationClassName : Win32_ComputerSystem SystemName : GODOT UniqueId : UpgradeMethod : 6 Version : Model 9, Stepping 5 VoltageCaps : 2 __GENUS : 2 __CLASS : Win32_Processor ... PS C:\>
As with the other exporting cmdlets, you can use -NoClobber to avoid overwriting an existing file.
ConvertTo-HTML
Finally, PowerShell includes a cmdlet to convert text output to an HTML table with the ConvertTo-HTML cmdlet. At its simplest, you can run an expression like this:
PS C:\> Get-Service | ConvertTo-HTML
If you execute this expression you'll see HTML code fly across the console, which doesn't do you much good. This can be changed by piping the HTML output to a file using Out-File, specifying a file name:
PS C:\> Get-Service | ConvertTo-HTML |out-file services.html
Now when you open services.html in a Web browser, you'll see a pretty complete table of running services and their properties. By default, the cmdlet lists all properties. However, you can specify the properties by name and in whatever order you prefer:
PS C:\> Get-Service | ConvertTo-HTML Name,DisplayName,Status |` >> out-file services.html >> PS C:\>
Now when you open services.html it's a little easier to work with. If you want to dress-up the page a bit, ConvertTo-HTML has some additional parameters as shown in the following table:
 |
| Parameter | Description |
|---|---|
| Head | Inserts text into the <head> tag of the html page. You might want to include metadata or style sheet references. |
| Title | Inserts text into the <title> tag of the html page. This let's you have a more meaningful title to the page other then the default HTMLTABLE. |
| Body | Inserts text within the <body></body> tag. This lets you specify body specify formatting like fonts and colors as well as any text you want to appear before the table. |
Open table as spreadsheet |
Here's a script where we put it all together.
Service2HTML.ps1
|
#Service2HTML.ps1 # a style sheet, style.css, should be in the same directory # as the saved html file. $server=hostname $body="Services Report for "+$server.ToUpper()+"<HR>" $file="c:\"+$server+"-services.html" write-host "Generating Services Report for "$server.ToUpper() get-service |sort -property status -descending | ConvertTo-HTML ` Name,DisplayName,Status -Title "Service Report" ` -Head "<link rel=stylesheet type=text/css href=style.css>" ` -Body $body | out-file $file write-host "Report Generation Complete! Open" $file "for results."
|
This script takes the Get-Service cmdlet and generates a formatted HTML page. The script starts by defining some variables. First we want the computer name to use in the report and other variables. Then we define a variable for the -Body parameter. If just text is being used, we don't have to bother with this. However, the ConvertTo-HTML cmdlet is a little finicky and doesn't handle the results of embedded cmdlets very well. By defining a variable we can ensure its value is a string. We also specify the location and name of the saved file. We're using the server name as part of the filename.
After a message is sent to the user informing him the report is being generated, the heart of the script is reached. We take the Get-Service cmdlet and first pipe it to the Sort-Object cmdlet, sorting on service status and returning the results in descending order. This puts Running services at the top of the page and Stopped services at the bottom. Next we pipe that to ConvertTo-HTML specifying the properties we want in the table. We include a -head parameter so we can reference a style sheet and then the -body parameter using the $body variable we defined at the beginning of the script. All of this is piped to Out-File, which saves the result to an HTML file. The results can be seen in Figure 9-1.
Figure 9-1: Service2HTML.ps1 saved HTML page
