Link to home
Start Free TrialLog in
Avatar of Albert Widjaja
Albert WidjajaFlag for Australia

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 ?

$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

Open in new window


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,
Avatar of J0rtIT
J0rtIT
Flag of Venezuela, Bolivarian Republic of image

In your foreach loop, you're not using the name of the server, you're using the whole $Servers Object as name of the computer. that's not right.

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
      }
    }
}

Open in new window


or you can change your

$Servers = (Get-exchangeserver | select Name).Name

Open in new window

Avatar of Albert Widjaja

ASKER

Jose,

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

Open in new window

Avatar of Bill Prew
Bill Prew

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

$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
      }
    }
}


}

Open in new window

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.
@Jose,

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

Open in new window

You mixed my answers... I gave you 2 options
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

Open in new window


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
Avatar of J0rtIT
J0rtIT
Flag of Venezuela, Bolivarian Republic of image

Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
$Servers = (Get-exchangeserver | select Name).Name

Open in new window

is better written as
$Servers = Get-ExchangeServer | select -Expand Name

Open in new window

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)
{
   ...
}

Open in new window

but it is just optimization.

@Jose,

Well, your parameter is bad.
It  should be "Computername"
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.
Jose,

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.
Sorry typo, the script is executed on my Laptop, I am doing Powershell remoting.
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:

$Session = New-PSSession -ConfigurationName Microsoft.Exchange -ConnectionUri http://PRODMBX14.MyDomain.com/PowerShell/ -Authentication Kerberos
Import-PSSession $Session -AllowClobber

Open in new window


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

Open in new window

ASKER CERTIFIED SOLUTION
Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
Thanks.
QLemo solution works great :-)