1

I am currently exporting the last 7 days of Security microsoft windows security auditing logs via powershell to csv.

It works, but with way to much detail, and also, not properly formatted. For instance, from the csv, the 'message' cell has this in

"An account was successfully logged on.

Subject: Security ID: X Account Name: - Account Domain: - Logon ID: 0x0

Logon Type: 3

Impersonation Level: Impersonation

New Logon: Security ID: X Account Name: XXX Account Domain: XXX Logon ID: XXX Logon GUID: {XXX}

Process Information: Process ID: 0x0 Process Name: -

Network Information: Workstation Name: - Source Network Address: XXX.XXX Source Port: XXX ............

Now to get the above into csv i am using this powershell script

[xml]$CustomView = @"
<QueryList>
  <Query Id="0" Path="Security">
    <Select Path="Security">*[System[(EventID=4624 or EventID=4672 or EventID=4648 or EventID=463) and TimeCreated[timediff(@SystemTime) &lt;= 604800000]]] </Select>
  </Query>
</QueryList>
"@

Get-WinEvent -FilterXML $CustomView | Export-CSV "C:\CustomView_$(Get-Date -format "yyyy-MM-DD").csv"

How is it possible to export a csv with the following columns?

Event Id, Security ID, Account Name, Account Domain, Logon ID, Logon, TimeCreated

  • You would need to parse the message field to get the data that you wanted then export that. – EBGreen Apr 9 '18 at 14:01
3

The details from the event message body are stored in the event XML. You can convert the event to XML and then extract each of the XML fields. The thing to keep in mind is that you should only query multiple event IDs when they share a common schema, otherwise the event properties may not be consistent in the output. There is a good write-up explaining the process and event schema issue here.

There is a cmdlet on GitHub called Get-WinEventData that does all the heavy lifting for you. The output contains properties for all the main event fields like machine name, provider name, and message. It also contains properties for all of the XML event data. To use this, simply pipe your Get-WinEvent command into Get-WinEventData, select the properties you want, then export to CSV. Of course you will need to import the Get-WinEventData function beforehand.

Example usage:

Get-WinEvent -FilterHashtable @{LogName="Security";Id=4624,4672;StartTime=(Get-Date).AddDays(-1)} | Get-WinEventData | Select-Object TimeCreated,Id,EventDataSubjectUserSid,EventDataSubjectUserName,EventDataSubjectDomainName,EventDataSubjectLogonId | Export-CSV "Output.csv" -NoTypeInformation
  • You're very welcome :) – twconnell Apr 12 '18 at 9:51

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.