1

I have a PowerShell script that queries a database and returns two columns. Depending on when this script is run, the query can return no rows, one row, or multiple rows. With these two columns, I write the output to the event log.

Here is the part that writes to event log:

    if($missing) { 
    # foreach easch row in missing, write to the event viewer eventlog

    ForEach ($row in $missing) {

        $serNames = $row.ServerName
        $names =  $row.name 

        $output =  "Following backups missing for longer than threshold:   '$serNames' - '$names'"

        Write-EventLog -ComputerName DB0000 -LogName Application -Source $source -EntryType Error -EventID 9901 -Message $output
        }  
    } 
    else { 
        continue
    }

My question is... Is there a way to have the result table be written as my output to the event log? Currently with my foreach loop, operational DBAs are getting spammed with emails so I would like to have the table written to event viewer as opposed to a foreach writing separate event logs. I tried removing the for each and playing with formatting but I have had no luck.

EDIT: Sorry I should specify further... Currently each row is separately written to the event viewer and SCOM picks these up and sends via email an alert. Would like the full result set of server/db pairs emailed in one single alert. Thank you.

1

You can also use the built-in format-table function. So for example, the message for the event would be:

$MessageBody = $missing | Format-Table -Auto | Out-String

Write-eventlog -Message $MessageBody

You may need to explicitly select the fields you want and apply any formatting (for example, dates).

1

Add a FOR XML (or JSON, if that's your thing) clause to the query. This will output all columns and rows in a single string, which can be written to the event log. Be aware of the size so you don't lose information.

If changing the query isn't possible, Powershell has a full set of XML features. Loop through the results and write one event. You can detect the risk of xml truncation in the event store and write further events to avoid loss.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.