Detecting Storage Issues

In Windows 10 and Windows Server 2016, PowerShell can access storage reliability data so you can find out whether there is something wrong with one of the attached storage drives. This requires Administrator privileges to execute:

 
PS> Get-PhysicalDisk | Get-StorageReliabilityCounter

DeviceId Temperature ReadErrorsUncorrected Wear PowerOnHours
-------- ----------- --------------------- ---- ------------
0                                          0                
1                                          0  
 

To see all available information, use Select-Object:

 
PS> Get-PhysicalDisk | Get-StorageReliabilityCounter | Select-Object -Property *


(...)
DeviceId                : 0
FlushLatencyMax         : 104
LoadUnloadCycleCount    : 
LoadUnloadCycleCountMax : 
ManufactureDate         : 
PowerOnHours            : 
ReadErrorsCorrected     : 
ReadErrorsTotal         : 
ReadErrorsUncorrected   : 
ReadLatencyMax          : 1078
StartStopCycleCount     : 
StartStopCycleCountMax  : 
Temperature             : 1
TemperatureMax          : 1
Wear                    : 0
WriteErrorsCorrected    : 
WriteErrorsTotal        : 
WriteErrorsUncorrected  : 
WriteLatencyMax         : 1128
(...)
FlushLatencyMax         : 
LoadUnloadCycleCount    : 
LoadUnloadCycleCountMax : 
ManufactureDate         : 
PowerOnHours            : 
ReadErrorsCorrected     : 
ReadErrorsTotal         : 
ReadErrorsUncorrected   : 
ReadLatencyMax          : 46
StartStopCycleCount     : 
StartStopCycleCountMax  : 
Temperature             : 0
TemperatureMax          : 0
Wear                    : 0
WriteErrorsCorrected    : 
WriteErrorsTotal        : 
WriteErrorsUncorrected  : 
WriteLatencyMax         : 
PSComputerName          : 
(...) 
 

The detail and amount of returned data depends on your storage manufacturer and your drivers.


Twitter This Tip! ReTweet this Tip!

Resetting Winsock

PowerShell can execute internal PowerShell commands and also regular console commands, so it’s not a bad thing to continue to use console commands for proven tasks.

For example, if you’d like to reset your winsocks, this would be a reliable solution:

#requires -RunAsAdministrator

netsh winsock reset
netsh int ip reset

Note that this code requires Administrator privileges, and may require a system restart to take effect.


Twitter This Tip! ReTweet this Tip!

Using Awesome Export-Excel Cmdlet (Part 5)

This is part 5 of our mini-series about the awesome and free “ImportExcel” PowerShell module by Doug Finke. Make sure you install the module before you play with this tip:

 
PS> Install-Module -Name ImportExcel -Scope CurrentUser -Force
 

In part 4, we looked at misinterpreted data due to arrays found in the input data. As you have seen, you simply need to convert arrays to strings using the -join operator, and Excel will display the arrays correctly, i.e. as a list of comma-separated values.

But what if you’d like to display array elements in individual lines, and use a word wrap?

By default, Excel will show individual lines only in the input field of the selected cell, but not in all cells:

# get some raw data that contains arrays
$rawData = Get-EventLog -LogName System -Newest 10 | 
            Select-Object -Property TimeWritten, ReplacementStrings, InstanceId


# create this Excel file
$Path = "$env:tempreport.xlsx"
# make sure the file is deleted so we have no
# effects from previous data still present in the
# file. This requires that the file is not still
# open and locked in Excel
$exists = Test-Path -Path $Path
if ($exists) { Remove-Item -Path $Path}

$sheetName = 'Testdata'
$rawData |
  ForEach-Object {
    # convert column "ReplacementStrings" from array to string
    $_.ReplacementStrings = $_.ReplacementStrings -join "`r`n"
    # return the changed object
    $_
  } |
  Export-Excel -Path $path -ClearSheet -WorksheetName $sheetName -Show

When you run this code, the array in “ReplacementStrings” would be correctly converted into a multi-line text, but you wouldn’t see this in the sheet. Only when you click an individual cell would you see the multi-lines in the input field.

When you combine the info from our previous parts, you can easily post-process the Excel file, though, and format the cells to “Text” and “WordWrap” like this:

# get some raw data that contains arrays
$rawData = Get-EventLog -LogName System -Newest 10 | 
            Select-Object -Property TimeWritten, ReplacementStrings, InstanceId


# create this Excel file
$Path = "$env:tempreport.xlsx"
# make sure the file is deleted so we have no
# effects from previous data still present in the
# file. This requires that the file is not still
# open and locked in Excel
$exists = Test-Path -Path $Path
if ($exists) { Remove-Item -Path $Path}

$sheetName = 'Testdata'

# save the Excel object model by using -PassThru instead of -Show
$excel = $rawData |
  ForEach-Object {
    # convert column "ReplacementStrings" from array to string
    $_.ReplacementStrings = $_.ReplacementStrings -join "`r`n"
    # return the changed object
    $_
  } |
  Export-Excel -Path $path -ClearSheet -WorksheetName $sheetName -AutoSize -PassThru

#region Post-process the column with the misinterpreted formulas  
# remove the region to repro the original Excel error
$sheet1 = $excel.Workbook.Worksheets[$sheetName]
# reformat cell to number type "TEXT" with WordWrap and AutoSize
Set-Format -Address $sheet1.Cells['B:B'] -NumberFormat 'Text' -WrapText -AutoSize
#endregion

Close-ExcelPackage -ExcelPackage $excel -Show

Twitter This Tip! ReTweet this Tip!

Using Awesome Export-Excel Cmdlet (Part 4)

This is part 4 of our mini-series about the awesome and free “ImportExcel” PowerShell module by Doug Finke. Make sure you install the module before you play with this tip:

 
PS> Install-Module -Name ImportExcel -Scope CurrentUser -Force
 

In part 3, we looked at misinterpreted data due to automatic formula conversion, and examined your options to post-process individual cell formats. Let’s examine issues caused by arrays.

Here is some code that reproduces the problem. In our example, it is event log data from the last 10 system events, which happens to contain an array (ReplacementStrings), and displays completely incorrectly:

# get some raw data that contains arrays
$rawData = Get-EventLog -LogName System -Newest 10 | 
            Select-Object -Property TimeWritten, ReplacementStrings, InstanceId


# create this Excel file
$Path = "$env:tempreport.xlsx"
# make sure the file is deleted so we have no
# effects from previous data still present in the
# file. This requires that the file is not still
# open and locked in Excel
$exists = Test-Path -Path $Path
if ($exists) { Remove-Item -Path $Path}

$sheetName = 'Testdata'
$rawData |
  Export-Excel -Path $path -ClearSheet -WorksheetName $sheetName -Show

When Excel opens, you can see that the column “ReplacementStrings” just shows the data type and not the actual data (“System.String[]”). This is the normal Excel behavior when it encounters arrays, so there is nothing Export-Excel can do about this.

Instead, it is your responsibility to convert the array to a string before you pipe it to Export-Excel – which is pretty easy using the -join operator:

# get some raw data that contains arrays
$rawData = Get-EventLog -LogName System -Newest 10 | 
            Select-Object -Property TimeWritten, ReplacementStrings, InstanceId


# create this Excel file
$Path = "$env:tempreport.xlsx"
# make sure the file is deleted so we have no
# effects from previous data still present in the
# file. This requires that the file is not still
# open and locked in Excel:
$exists = Test-Path -Path $Path
if ($exists) { Remove-Item -Path $Path}

$sheetName = 'Testdata'
$rawData |
  ForEach-Object {
    # convert column "ReplacementStrings" from array to string
    $_.ReplacementStrings = $_.ReplacementStrings -join ','
    # return the changed object
    $_
  } |
  Export-Excel -Path $path -ClearSheet -WorksheetName $sheetName -Show

Once you do this, even properties containing arrays will display correctly in Excel. -join works for any object. Simply make sure you specify the delimiter you want to use to separate array elements.


Twitter This Tip! ReTweet this Tip!

Using Awesome Export-Excel Cmdlet (Part 3)

This is part 3 of our mini-series about the awesome and free “ImportExcel” PowerShell module by Doug Finke. Make sure you install the module before you play with this tip:

 
PS> Install-Module -Name ImportExcel -Scope CurrentUser -Force 
 

In part 2, we looked at misinterpreted data due to automatic number conversion. Another issue can occur when raw data “looks like” Excel formulas in which case they are converted to formulas and cause issues when the resulting Excel file is opened later.

Here is an example that reproduces this problem: some records contain text such as “=)”, and the “=” at the beginning makes Excel think this was a formula:

# any object-oriented data will do
# we create some sample records via CSV
# to mimick specific issues
$rawData = @'
Data,Name
Test, Tobias
=), Mary
=:-(), Tom
'@ | ConvertFrom-Csv

# create this Excel file
$Path = "$env:tempreport.xlsx"
# make sure the file is deleted so we have no
# effects from previous data still present in the
# file. This requires that the file is not still
# open and locked in Excel
$exists = Test-Path -Path $Path
if ($exists) { Remove-Item -Path $Path}

$rawData |
  Export-Excel -Path $path -ClearSheet -WorksheetName Processes -Show

When you run this code, Excel opens but immediately complains about invalid formulas. The original content is lost.

This issue cannot be easily resolved by a switch parameter. Instead, you need to manually reformat cells which gives you great flexibility. Here is the general strategy:

  • Use Export-Excel to create the .xlsx file, but instead of specifying -Show (and opening the file in Excel), use -PassThru. This gets you the Excel object model instead.
  • Make any changes to the cells using the object model
  • Save the changes to a file using Close-ExcelPackage. You can specify -Show now, and open the results in Excel if you want
# any object-oriented data will do
# we create some sample records via CSV
# to mimick specific issues
$rawData = @'
Data,Name
Test, Tobias
=), Mary
=:-(), Tom
'@ | ConvertFrom-Csv

# create this Excel file
$Path = "$env:tempreport.xlsx"
# make sure the file is deleted so we have no
# effects from previous data still present in the
# file. This requires that the file is not still
# open and locked in Excel
$exists = Test-Path -Path $Path
if ($exists) { Remove-Item -Path $Path}

$sheetName = 'Testdata'
$excel = $rawData |
  Export-Excel -Path $path -ClearSheet -WorksheetName $sheetName -PassThru
  


#region Post-process the column with the misinterpreted formulas  
# remove the region to repro the original Excel error
$sheet1 = $excel.Workbook.Worksheets[$sheetName]

# take all cells from row "A"...
$sheet1.Cells['A:A'] |
# ...that are currently interpreted as a formula...
Where-Object Formula |
ForEach-Object {
  # ...construct the original content which is the formula
  # plus a prepended "="
  $newtext = ('={0}' -f $_.Formula)
  # reformat cell to number type "TEXT"
  Set-Format -Address $_ -NumberFormat 'Text' -Value 'dummy'
  # assign the original content to the cell (this cannot be done using Set-Format)
  $_.Value = $newtext
}
#endregion

Close-ExcelPackage -ExcelPackage $excel -Show 

When you run this, the Excel sheet opens without issues, and the first column correctly displays the content. This was achieved by explicitly formatting the first column as “Text”. Then, once the format was changed to “Text”, the formula content was inserted as cell value.

You neither receive the “formula” error messages, nor do you have to “mask” the content by adding quotes around it.

The example illustrates how you can post-process the Excel sheet and add, change, reformat individual cells at your disposal before saving the result to file and opening it in Excel.


Twitter This Tip! ReTweet this Tip!

Using Awesome Export-Excel Cmdlet (Part 2)

This is part 2 of our mini-series about the awesome and free “ImportExcel” PowerShell module by Doug Finke. Make sure you install the module before you play with this tip:

 
PS> Install-Module -Name ImportExcel -Scope CurrentUser -Force 
 

When you export data to Excel files, you may sometimes encounter data that is misinterpreted by Excel. For example, often phone numbers are misinterpreted as numeric values. Here is an example that reproduces this problem:

# any object-oriented data will do
# we create some sample records via CSV
# to mimick specific issues
$rawData = @'
Phone,Name
+4915125262524, Tobias
0766256725672, Mary
00496253168722567, Tom
'@ | ConvertFrom-Csv

# create this Excel file
$Path = "$env:tempreport.xlsx"
# make sure the file is deleted so we have no
# effects from previous data still present in the
# file. This requires that the file is not still
# open and locked in Excel
$exists = Test-Path -Path $Path
if ($exists) { Remove-Item -Path $Path}

$rawData |
  Export-Excel -Path $path -ClearSheet -WorksheetName Processes -Show

As you’ll see, when Excel opens, the phone numbers are auto-converted to integers.

To prevent this auto-conversion, use -NoNumberConversion, and specify the column(s) that should be excluded from conversion:

# any object-oriented data will do
# we create some sample records via CSV
# to mimick specific issues
$rawData = @'
Phone,Name
+4915125262524, Tobias
0766256725672, Mary
00496253168722567, Tom
'@ | ConvertFrom-Csv

# create this Excel file
$Path = "$env:tempreport.xlsx"
# make sure the file is deleted so we have no
# effects from previous data still present in the
# file. This requires that the file is not still
# open and locked in Excel
$exists = Test-Path -Path $Path
if ($exists) { Remove-Item -Path $Path}

$rawData |
  Export-Excel -Path $path -ClearSheet -WorksheetName Processes -Show -NoNumberConversion Phone

Now, the “Phone” column will no longer be treated as numbers, and the phone numbers show correctly.


Twitter This Tip! ReTweet this Tip!