Albert Widjaja
asked on
Modifying PowerShell script to List the last 5 Exchange Server Events and put into Excel ?
I had this question after viewing Modifying PowerShell script to list last 5 Event ID not working ?.
Hi All,
When I modify the script to get the Exchange server events, somehow it does not show it in the first column ?
When I run the script Get-ExchangeServer I can get the list of servers already.
Any help to modify it would be greatly appreciated.
Thanks,
Hi All,
When I modify the script to get the Exchange server events, somehow it does not show it in the first column ?
$objExcel = New-Object -comobject Excel.Application
$objExcel.Visible = $True
$objWorkbook = $objExcel.Workbooks.Add()
$objSheet = $objWorkbook.Worksheets.Item(1)
$objSheet.Cells.Item(1,1) = "Server"
$objSheet.Cells.Item(1,2) = "LogName"
$objSheet.Cells.Item(1,3) = "Time"
$objSheet.Cells.Item(1,4) = "Category"
$objSheet.Cells.Item(1,5) = "ID"
$objSheet.Cells.Item(1,6) = "Source"
$objSheet.Cells.Item(1,7) = "Message"
$objSheetFormat = $objSheet.UsedRange
$objSheetFormat.Interior.ColorIndex = 19
$objSheetFormat.Font.ColorIndex = 11
$objSheetFormat.Font.Bold = $True
$Row = 2
$Servers = Get-ExchangeServer
foreach ($Server in $Servers) {
foreach ($Log in @("Application", "Security", "System")) {
$Events = Get-EventLog -LogName $Log -Computer $Server -Newest 5 -ea Silentlycontinue | Where-Object {$_.EntryType -in 'Error', 'Warning'} | Sort-Object Source
foreach ($Event in $Events) {
$objSheet.Cells.Item($Row, 1).Font.Bold = $True
$objSheet.Cells.Item($Row, 1) = $Server
$objSheet.Cells.Item($Row, 2) = $Log
$objSheet.Cells.Item($Row, 3) = $Event.TimeGenerated
$objSheet.Cells.Item($Row, 4) = $Event.Category
$objSheet.Cells.Item($Row, 5) = $Event.EventID
$objSheet.Cells.Item($Row, 6) = $Event.Source
$objSheet.Cells.Item($Row, 7) = $Event.Message
$Row = $Row + 1
}
}
}
$objSheetFormat = $objSheet.UsedRange
$objSheetFormat.EntireColumn.AutoFit()
$objSheetFormat.RowHeight = 15
When I run the script Get-ExchangeServer I can get the list of servers already.
Any help to modify it would be greatly appreciated.
Thanks,
ASKER
Jose,
It is not even showing any result ?
It is not even showing any result ?
Get-EventLog : Cannot validate argument on parameter 'ComputerName'. The argument is null or empty. Provide an argument that is not null or empty, and then try the command again.
$objExcel = New-Object -comobject Excel.Application
$objExcel.Visible = $True
$objWorkbook = $objExcel.Workbooks.Add()
$objSheet = $objWorkbook.Worksheets.Item(1)
$objSheet.Cells.Item(1,1) = "Server"
$objSheet.Cells.Item(1,2) = "LogName"
$objSheet.Cells.Item(1,3) = "Time"
$objSheet.Cells.Item(1,4) = "Category"
$objSheet.Cells.Item(1,5) = "ID"
$objSheet.Cells.Item(1,6) = "Source"
$objSheet.Cells.Item(1,7) = "Message"
$objSheetFormat = $objSheet.UsedRange
$objSheetFormat.Interior.ColorIndex = 19
$objSheetFormat.Font.ColorIndex = 11
$objSheetFormat.Font.Bold = $True
$Row = 2
$Servers = (Get-exchangeserver | select Name).Name
foreach ($Server in $Servers) {
foreach ($Log in @("Application", "Security", "System")) {
$Events = Get-EventLog -LogName $Log -Computer $Server.Name -Newest 5 -ea Silentlycontinue | Where-Object {$_.EntryType -in 'Error', 'Warning'} | Sort-Object Source
foreach ($Event in $Events) {
$objSheet.Cells.Item($Row, 1).Font.Bold = $True
$objSheet.Cells.Item($Row, 1) = $Server
$objSheet.Cells.Item($Row, 2) = $Log
$objSheet.Cells.Item($Row, 3) = $Event.TimeGenerated
$objSheet.Cells.Item($Row, 4) = $Event.Category
$objSheet.Cells.Item($Row, 5) = $Event.EventID
$objSheet.Cells.Item($Row, 6) = $Event.Source
$objSheet.Cells.Item($Row, 7) = $Event.Message
$Row = $Row + 1
}
}
}
$objSheetFormat = $objSheet.UsedRange
$objSheetFormat.EntireColumn.AutoFit()
$objSheetFormat.RowHeight = 15
When I modify the script to get the Exchange server events, somehow it does not show it in the first column ?Can you clarify? Are you getting the rows you want, but only missing the server name in the first column? On all rows, or just certain ones?
»bp
Well, your parameter is bad.
It should be "Computername"
Here's the documentation:
https://docs.microsoft.com/en-us/powershell/module/microsoft.powershell.management/get-eventlog?view=powershell-5.1
The error is because your $Servers variable is null.
do a TRy/catch in the Get-exchangeserver cmdlet
or
It should be "Computername"
Here's the documentation:
https://docs.microsoft.com/en-us/powershell/module/microsoft.powershell.management/get-eventlog?view=powershell-5.1
The error is because your $Servers variable is null.
do a TRy/catch in the Get-exchangeserver cmdlet
or
$Servers = (Get-exchangeserver | select Name).Name
if($servers){
foreach ($Server in $Servers) {
foreach ($Log in @("Application", "Security", "System")) {
$Events = Get-EventLog -LogName $Log -Computer $Server.Name -Newest 5 -ea Silentlycontinue | Where-Object {$_.EntryType -in 'Error', 'Warning'} | Sort-Object Source
foreach ($Event in $Events) {
$objSheet.Cells.Item($Row, 1).Font.Bold = $True
$objSheet.Cells.Item($Row, 1) = $Server
$objSheet.Cells.Item($Row, 2) = $Log
$objSheet.Cells.Item($Row, 3) = $Event.TimeGenerated
$objSheet.Cells.Item($Row, 4) = $Event.Category
$objSheet.Cells.Item($Row, 5) = $Event.EventID
$objSheet.Cells.Item($Row, 6) = $Event.Source
$objSheet.Cells.Item($Row, 7) = $Event.Message
$Row = $Row + 1
}
}
}
}
ASKER
Can you clarify? Are you getting the rows you want, but only missing the server name in the first column? On all rows, or just certain ones?
Yes, Bill, I only missed the server name on the first column.
ASKER
@Jose,
The script that you suggested is still not working, it shows Blank Excel spreadsheet:
The script that you suggested is still not working, it shows Blank Excel spreadsheet:
$objExcel = New-Object -comobject Excel.Application
$objExcel.Visible = $True
$objWorkbook = $objExcel.Workbooks.Add()
$objSheet = $objWorkbook.Worksheets.Item(1)
$objSheet.Cells.Item(1,1) = "Server"
$objSheet.Cells.Item(1,2) = "LogName"
$objSheet.Cells.Item(1,3) = "Time"
$objSheet.Cells.Item(1,4) = "Category"
$objSheet.Cells.Item(1,5) = "ID"
$objSheet.Cells.Item(1,6) = "Source"
$objSheet.Cells.Item(1,7) = "Message"
$objSheetFormat = $objSheet.UsedRange
$objSheetFormat.Interior.ColorIndex = 19
$objSheetFormat.Font.ColorIndex = 11
$objSheetFormat.Font.Bold = $True
$Row = 2
$Servers = (Get-exchangeserver | select Name).Name
if($servers){
foreach ($Server in $Servers) {
foreach ($Log in @("Application", "Security", "System")) {
$Events = Get-EventLog -LogName $Log -Computer $Server.Name -Newest 5 -ea Silentlycontinue | Where-Object {$_.EntryType -in 'Error', 'Warning'} | Sort-Object Source
foreach ($Event in $Events) {
$objSheet.Cells.Item($Row, 1).Font.Bold = $True
$objSheet.Cells.Item($Row, 1) = $Server
$objSheet.Cells.Item($Row, 2) = $Log
$objSheet.Cells.Item($Row, 3) = $Event.TimeGenerated
$objSheet.Cells.Item($Row, 4) = $Event.Category
$objSheet.Cells.Item($Row, 5) = $Event.EventID
$objSheet.Cells.Item($Row, 6) = $Event.Source
$objSheet.Cells.Item($Row, 7) = $Event.Message
$Row = $Row + 1
}
}
}
}
$objSheetFormat = $objSheet.UsedRange
$objSheetFormat.EntireColumn.AutoFit()
$objSheetFormat.RowHeight = 15
You mixed my answers... I gave you 2 options
if you use
....
You already got the name of the Server in the variable $Servers
so you don't have to use the $servers.name
if you use
....
$Servers = (Get-exchangeserver | select Name).Name
if($servers){
foreach ($Server in $Servers) {
foreach ($Log in @("Application", "Security", "System")) {
$Events = Get-EventLog -LogName $Log -Computer $Server -Newest 5 -ea Silentlycontinue | Where-Object {$_.EntryType -in 'Error', 'Warning'} | Sort-Object Source
foreach ($Event in $Events) {
$objSheet.Cells.Item($Row, 1).Font.Bold = $True
$objSheet.Cells.Item($Row, 1) = $Server
$objSheet.Cells.Item($Row, 2) = $Log
$objSheet.Cells.Item($Row, 3) = $Event.TimeGenerated
$objSheet.Cells.Item($Row, 4) = $Event.Category
$objSheet.Cells.Item($Row, 5) = $Event.EventID
$objSheet.Cells.Item($Row, 6) = $Event.Source
$objSheet.Cells.Item($Row, 7) = $Event.Message
$Row = $Row + 1
}
}
}
}
$objSheetFormat = $objSheet.UsedRange
$objSheetFormat.EntireColumn.AutoFit()
$objSheetFormat.RowHeight = 15
You already got the name of the Server in the variable $Servers
so you don't have to use the $servers.name
Security events don't book Error or Warnings, it books 'FailureAudit' or 'SuccessAudit' so that one will always bring something empty.
SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
$Servers = (Get-exchangeserver | select Name).Name
is better written as$Servers = Get-ExchangeServer | select -Expand Name
I for myself would remove the var and put the expression into the foreach, removing the outermost if too:foreach ($server in Get-ExchangeServer | select -Expand Name)
{
...
}
but it is just optimization.@Jose,
is not correct. You can use any abbreviated but unique parameter name. As long as there is no other parameter starting with a "c", even -c should suffice instead of -ComputerName.
Well, your parameter is bad.
It should be "Computername"
ASKER
Jose,
No, This script is run and executed on my Exchange server, the PowerShell script is using the Exchange script.
No, This script is run and executed on my Exchange server, the PowerShell script is using the Exchange script.
Jose is correct - the script requires to have at least the Exchange Shell installed, plus Excel.
Office on Exchange Server is not recommended because of possibly conflicting DLLs (and Exchange being very picky about its environment including OS patches), but has been required in the past for performing PST exports for example.
Office on Exchange Server is not recommended because of possibly conflicting DLLs (and Exchange being very picky about its environment including OS patches), but has been required in the past for performing PST exports for example.
ASKER
Sorry typo, the script is executed on my Laptop, I am doing Powershell remoting.
ASKER
Hi All,
Thank you for the suggestion and the assistance in this matter,
This is the method I am connecting to one of the Exchange server from my laptop:
however the modified code as suggested by QLemo and Jose does not seems to be working, it only grabs the last Exchange Server from total of 6 returned by this command Get-ExchangeServer | select -Expand Name:
this is the script that I have executed:
Thank you for the suggestion and the assistance in this matter,
This is the method I am connecting to one of the Exchange server from my laptop:
$Session = New-PSSession -ConfigurationName Microsoft.Exchange -ConnectionUri http://PRODMBX14.MyDomain.com/PowerShell/ -Authentication Kerberos
Import-PSSession $Session -AllowClobber
however the modified code as suggested by QLemo and Jose does not seems to be working, it only grabs the last Exchange Server from total of 6 returned by this command Get-ExchangeServer | select -Expand Name:
this is the script that I have executed:
$objExcel = New-Object -comobject Excel.Application
$objExcel.Visible = $True
$objWorkbook = $objExcel.Workbooks.Add()
$objSheet = $objWorkbook.Worksheets.Item(1)
$objSheet.Cells.Item(1,1) = "Server"
$objSheet.Cells.Item(1,2) = "LogName"
$objSheet.Cells.Item(1,3) = "Time"
$objSheet.Cells.Item(1,4) = "Category"
$objSheet.Cells.Item(1,5) = "ID"
$objSheet.Cells.Item(1,6) = "Source"
$objSheet.Cells.Item(1,7) = "Message"
$objSheetFormat = $objSheet.UsedRange
$objSheetFormat.Interior.ColorIndex = 19
$objSheetFormat.Font.ColorIndex = 11
$objSheetFormat.Font.Bold = $True
$Row = 2
$Events=@()
foreach ($server in Get-ExchangeServer | select -Expand Name) {
$Events+= Get-EventLog -LogName "Security" -ComputerName $Server -Newest 5 -ea Silentlycontinue | Where-Object {$_.EntryType -eq 'FailureAudit' -or $_.EntryType -eq 'SuccessAudit'} #| Sort-Object Source
foreach ($Log in @("Application", "System")) {
$Events+= Get-EventLog -LogName $Log -ComputerName $Server -Newest 5 -ea Silentlycontinue | Where-Object {$_.EntryType -eq 'Error' -or $_.EntryType -eq 'Warning'} #| Sort-Object Source
}
}
foreach ($Event in $Events) {
$objSheet.Cells.Item($Row, 1).Font.Bold = $True
$objSheet.Cells.Item($Row, 1) = $Server
$objSheet.Cells.Item($Row, 2) = $Log
$objSheet.Cells.Item($Row, 3) = $Event.TimeGenerated
$objSheet.Cells.Item($Row, 4) = $Event.Category
$objSheet.Cells.Item($Row, 5) = $Event.EventID
$objSheet.Cells.Item($Row, 6) = $Event.Source
$objSheet.Cells.Item($Row, 7) = $Event.Message
$Row++
}
$objSheetFormat = $objSheet.UsedRange
$objSheetFormat.EntireColumn.AutoFit()
$objSheetFormat.RowHeight = 15
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
Thanks.
QLemo solution works great :-)
QLemo solution works great :-)
Open in new window
or you can change your
Open in new window