i'm new to ps scripting , i want to export certain data from event logs. I tried following

$events = Get-WinEvent -FilterHashtable @{logname="Microsoft-Windows-Storage-Storport/Health"; id=511}

# get the first event raw XML
$event = [xml]$events[0].ToXml()
$event.Event.EventData.Data | Where-Object {$_.name -eq "BootDevice"}

and i was getting output as:

but when i tried to export its value to excel it gives me error.

Here is my complete code:

$Myexcel = New-Object -ComObject excel.application
$Myexcel.visible = $true
$Myworkbook = $Myexcel.workbooks.add()
$Sheet1 = $Myworkbook.worksheets.item(1)
$Sheet1.name = "summary"
$Sheet1.cells.item(1,1) = 'BootDevice'
$events = Get-WinEvent -FilterHashtable @{logname="Microsoft-Windows-Storage-Storport/Health"; id=511}

# get the first event raw XML
$event = [xml]$events[0].ToXml()

# display its content
#$event.Event.EventData.Data
$BootDevice=$event.Event.EventData.Data | Where-Object {$_.name -eq "BootDevice"}
write-output $BootDevice
$Sheet1.cells.item(2,1) = $BootDevice
$Sheet1.Columns.AutoFit()
$Myfile = 'E:\tmp\test.csv'
$Myworkbook.Saveas($Myfile)
$Myexcel.displayalerts = $true

But its giving error

Exception from HRESULT: 0x800A03EC
At line:16 char:1
 $Sheet1.cells.item(2,1) = $BootDevice

And a blank excel is generated.

Any help will be thankfull.

CodePudding user response：

When manually creating an Excel file like this, you need to set the values and headers cell by cell.

Try

$Myexcel = New-Object -ComObject excel.application
$Myexcel.Visible = $true
$Myexcel.DisplayAlerts = $true
$Myworkbook = $Myexcel.workbooks.add()
$Sheet1 = $Myworkbook.worksheets.item(1)
$Sheet1.Name = "summary"
$Sheet1.Cells.Item(1,1) = 'BootDevice'
$Sheet1.Cells.Item(1,2) = 'Value'

$events = Get-WinEvent -FilterHashtable @{logname="Microsoft-Windows-Storage-Storport/Health"; id=511} | Select-Object -First 1
# get the first event raw XML
$event = [xml]$events.ToXml()

# display its content
$BootDevice = $event.Event.EventData.Data | Where-Object {$_.Name -eq "BootDevice"}
write-output $BootDevice
$Sheet1.Cells.Item(2,1) = $BootDevice.Name
$Sheet1.Cells.Item(2,2) = $BootDevice.'#text'
$Sheet1.Columns.AutoFit()

# if you want to save in some other format than .XLSX, see what value you need for the second parameter of .SaveAs()
# XlFileFormat enumeration: https://docs.microsoft.com/en-us/office/vba/api/excel.xlfileformat
$Myfile = 'E:\tmp\Test\test.xlsx'
$Myworkbook.SaveAs($Myfile)

# quit Excel and clear the used COM objects from memory
$Myexcel.Quit()

$null = [System.Runtime.Interopservices.Marshal]::ReleaseComObject($Sheet1)
$null = [System.Runtime.Interopservices.Marshal]::ReleaseComObject($Myworkbook)
$null = [System.Runtime.Interopservices.Marshal]::ReleaseComObject($Myexcel)
[System.GC]::Collect()
[System.GC]::WaitForPendingFinalizers()

By using Select-Object -First 1 you effectively stop the gathering of events when you have found one, saving a lot of unnecessary time to first read all matching events and then only use the first one as in your code.

The above should give you an Excel file looking like this:

CodePudding user response：

Below code fetches the log events that you are looking for and exported to CSV file(test.csv)

 $events = Get-WinEvent -FilterHashtable @{logname="Microsoft-Windows-Storage-Storport/Health"; id=511}
    
    $events | Export-Csv -NoTypeInformation test.csv