Export to CSV

This topic contains 4 replies, has 3 voices, and was last updated by  Max Kozlov 4 months, 1 week ago.

  • Author
    Posts
  • #68122

    Tim R
    Participant

    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?

  • #68125

    Don Jones
    Keymaster

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

    • #68128

      Tim R
      Participant

      "Count","Name","Server"
      "2","Ntfs", "APP1"

  • #68130

    Don Jones
    Keymaster

    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.

  • #68251

    Max Kozlov
    Participant

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

You must be logged in to reply to this topic.