Export to CSV

PowerShell Help
1

Afternoon all. I’m trying to sort out a script that gives me a daily CSV of the System Errors on a number of servers.

I found a good script that I’ve been modifying for my needs, but have hit a bit of a snag at the last step.

First of all, here is the script:

Set-Variable -Name EventAgeDays -Value 1     #events from the last 1 day
Set-Variable -Name CompName -Value @("APP1", "APP2", "APP3", "APP4")  #Server names 
Set-Variable -Name LogNames -Value @("System")  # Checking system logs
Set-Variable -Name EventTypes -Value @("Error")  # Loading only Errors
Set-Variable -Name ExportFolder -Value "C:\TEST\" #Folder to export CSV file to

$el_c = @()   #consolidated error log
$now=get-date
$startdate=$now.adddays(-$EventAgeDays)
$ExportFile=$ExportFolder + "Error Log" + $now.ToString("yyyy-MM-dd---hh-mm-ss") + ".csv"  

foreach($comp in $CompName)
{
  foreach($log in $LogNames)
  {
    Write-Host Processing $comp\$log
    $el = get-eventlog -ComputerName $comp -log $log -After $startdate -EntryType $EventTypes
    $el_c += $el  #consolidating
  }
}

$el_sorted = $el_c | Sort-Object MachineName, TimeGenerated    #sort by time
Write-Host Exporting to $ExportFile

$el_sorted  | Group-object -Property "source", "MachineName" | Select-Object -Property Count, Name  | export-csv $ExportFile -NoTypeInformation 

Write-Host Done!

Now, the issue is that when I run this it exports the CSV as follows:

"Count","Name" "2","Ntfs, APP1" "1","TermDD, APP1" "117","Schannel, APP2" "2","Ntfs, APP2" "1","TermDD, APP2" "12","TermServDevices, APP2" "12","TermServDevices, APP3" "1","DCOM, APP3"

The Problem is that because of the grouping it is exporting only 2 columns and the error type and server name are in the same one, but I want those two in separate columns so it is easier to work with.
I’ve been trying every combination I can think of to make this work, but failing miserably! Can anyone help?

2

So, what would you WANT the CSV to look like?

3

“Count”,“Name”,“Server”
“2”,“Ntfs”, “APP1”

4

Ah. That’s going to be tricky the way you’ve gone about this. Once you group things, you “lose” easy access to the grouped things, and are instead left with group objects.

Write-Host makes me deeply sad, BTW. Consider Write-Verbose.

So, what you’re going to have to do is this.

  1. Group by machine name only.

  2. Enumerate those group objects in a ForEach. For each group, extract the contents, and then group those contents by source. Do the same Select-Object you’re doing for Count and Name, but add a custom property to add the server name. Since you’re inside a ForEach, you’ll have a variable with that from the original group (step 1).

You’re essentially trying to do a double-group, which isn’t a one-liner thing.

Alternately - and this would be far more fun - install SQL Express locally. Dump the data into a SQL table and write a SQL query to do this. SQL is really good at this, and can do grouping and summarizing and stuff all in one gorgeous query. And frankly, if you use SQL Server Reporting Services (which comes with the “advanced” SQL Express), you could let IT produce your CSV or any other fancy reports (even HTML!) you want.

5

fast and durty way (PSv3+):

$el_sorted |
 Group-object -Property "source", "MachineName" |
 Select-Object Count, @{n='Service'; e={ ($_.Name -split ',')[0] }}, @{n='ComputerName'; e={ ($_.Name -split ',')[1] }}